question

EriKDavila-1048 avatar image
0 Votes"
EriKDavila-1048 asked MelissaMa-msft answered

IF help on statement

Hello Experts, I will like to ask if there's a way to add in a statement if 0 shows will be "success" and if a 1 shows would be "Cancelled" o.IsSkipcartCancelled as 'IsAdminCanceled', this is the line o.IsSkipcartCancelled as 'IsAdminCanceled', o.IsRetailerCancelled as 'IsRetailerCancelled', o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel', (SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode', TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName, Thanks

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

nasreen-akter avatar image
0 Votes"
nasreen-akter answered EriKDavila-1048 commented

Hi @EriKDavila-1048,

Would you please try the following:

 SELECT CASE WHEN o.IsSkipcartCancelled = 0 THEN 'Success' ELSE 'Cancelled' END AS IsAdminCanceled, 
 o.IsRetailerCancelled as 'IsRetailerCancelled', 
 o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel', 
 (SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode',
 TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName
  ...

Hope this helps! Thanks!

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank and I have an error on Select, I don't know much how to fix it

Thanks


78912-image.png


0 Votes 0 ·
image.png (25.7 KiB)

@EriKDavila-1048, please remove the select and try again. In your case you only need to add CASE WHEN o.IsSkipcartCancelled = 0 THEN 'Success' ELSE 'Cancelled' END AS IsAdminCanceled

0 Votes 0 ·

Thank you it work great!!

Have a great day

1 Vote 1 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @EriKDavila-1048,

Welcome to Microsoft Q&A!

Glad that you already received your answer.

You could also consider to use IIF function instead of case when.

syntaxsql

IIF( boolean_expression, true_value, false_value )

Please also refer below:

 IIF(o.IsSkipcartCancelled = 0 ,'Success' ,'Cancelled') AS IsAdminCanceled, 
  o.IsRetailerCancelled as 'IsRetailerCancelled', 
  o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel', 
  (SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode',
  TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName

If you have any other issue or concern, welcome to post another question in Microsoft Q&A!

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.