Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Case Statement

Posted by: BentBrain Feb 18 2020, 04:00 AM

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

Posted by: cheekybuddha Feb 18 2020, 04:09 AM

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

Posted by: BentBrain Feb 18 2020, 04:53 AM

Thank you cheekybudda,
But now i get a different error


 

Posted by: cheekybuddha Feb 18 2020, 05:11 AM

>> 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

Posted by: BentBrain Feb 18 2020, 05:22 AM

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

Posted by: cheekybuddha Feb 18 2020, 06:00 AM

yw.gif