UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Case Statement, SQL Server 2012    
 
   
BentBrain
post Feb 18 2020, 04:00 AM
Post#1



Posts: 563
Joined: 10-February 03
From: Thailand


HI All,
Keep having issue with this Case Statement.
if i wrap it in STR or not I get this error
Conversion failed when converting the varchar value '**' to data type int.

Any Ideas.

CASE

WHEN ([tbl_Wait_Status].[WaitStatus] = 'Repair')

THEN (STR([tbl_Sales_OrderData].[JobOrderNO]) + STR('**'))

ELSE STR([tbl_Sales_OrderData].[JobOrderNO])

END


Thank you in advance.
BentBrain

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 18 2020, 04:09 AM
Post#2


UtterAccess Moderator
Posts: 12,809
Joined: 6-December 03
From: Telegraph Hill


Hi,

What datatype is tbl_Sales_OrderData.JobOrderNO ?

The STR() function converts a number to a string representation. So when you pass '**' it will choke.

What are you trying to do here?

Perhaps you want:
CODE
CASE
  WHEN tbl_Wait_Status.WaitStatus = 'Repair' THEN CONCAT(tbl_Sales_OrderData.JobOrderNO, '**')
  ELSE tbl_Sales_OrderData.JobOrderNO
END

If tbl_Sales_OrderData.JobOrderNO is numeric datatype then the CONCAT() function will handle the implicit conversion to string for you.


th,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 18 2020, 04:53 AM
Post#3



Posts: 563
Joined: 10-February 03
From: Thailand


Thank you cheekybudda,
But now i get a different error

Attached File(s)
Attached File  Capture.JPG ( 31.77K )Number of downloads: 1
 

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 18 2020, 05:11 AM
Post#4


UtterAccess Moderator
Posts: 12,809
Joined: 6-December 03
From: Telegraph Hill


>> What datatype is tbl_Sales_OrderData.JobOrderNO ? <<

You didn't answer this question.

I'm guessing it's an INT, so you can try:
CODE
CASE
  WHEN tbl_Wait_Status.WaitStatus = 'Repair' THEN CONCAT(tbl_Sales_OrderData.JobOrderNO, '**')
  ELSE CAST(tbl_Sales_OrderData.JobOrderNO AS VARCHAR(20))
END

--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 18 2020, 05:22 AM
Post#5



Posts: 563
Joined: 10-February 03
From: Thailand


Thank you so much David
Sorry I did not see your question about the field type.


THAT WORKED LIKE A CHARM

Thank you again.

Regard
BB

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 18 2020, 06:00 AM
Post#6


UtterAccess Moderator
Posts: 12,809
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 09:31 AM