IF help on statement

EriK Davila 26 Reputation points
2021-03-17T17:55:36.737+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,791 Reputation points
    2021-03-17T18:07:35.25+00:00

    Hi @EriK Davila ,

    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!


1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,191 Reputation points
    2021-03-18T02:56:36.957+00:00

    Hi @EriK Davila ,

    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.

    0 comments No comments