Post reply

If statement in T-SQL

  • June 22, 2017 at 8:24 am

    #322201

    Hi guys,

    I am trying to display item_name with a new category, which says the following term ( i have pasted my whole query) :

    declare @item varchar(50) 
    Select @item = item_name from order_items
    If @item = 'charger' or @item = 'cable'   print 'electricity'
    else
    if @item = 'pen' or @item = 'pencil'  print 'accessories'
    else
    print 'other'

    Now, I want to display a new category culomn, along side with the item name. But i know i can't mix regular Select rows with the variables i used in the declare clause
    like this : 

    item_name        category
    pen                    accessories
    pencil                 accessories
    cable                 electricity
    ...                     ...       
    So how do i do that?

    Thanks

  • June 22, 2017 at 8:28 am

    #1947775

    Instead of an IF statement, why not a CASE expression?

    SELECT oi.item_name
           CASE oi.item_name WHEN 'Pen' THEN 'Accessories'
                             WHEN 'Pencil' THEN 'Accessories'
                             WHEN 'Cable' THEN 'Electricity' END AS Category
    FROM order_items oi;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 22, 2017 at 8:30 am

    #1947776

    SELECT
      item_name
    ,  category = (CASE
           WHEN item_name IN ('charger', 'cable') THEN
             'electricity'
           WHEN item_name IN ('pen', 'pencil') THEN
             'accessories'
           ELSE
             'other'
          END
          )
    FROM order_items;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 22, 2017 at 8:39 am

    #1947777

    Phil Parkin - Thursday, June 22, 2017 8:30 AM

    Thanks guys, as usually. 
    It works.

    BTW I am curious, is there actually a way doing it with declare and IF statement?

  • June 22, 2017 at 8:46 am

    #1947778

    If you want to set the values of multiple variables in a single statement, you can use the SELECT statement:
    SELECT @ItemName = item_name,
           @Category = category
    FROM MyTable;

    I'm not quick sure what you want to do after that Something like...
    IF @ItemName = 'Pen' BEGIN
        --Do something
    END ELSE IF @ItemName = 'Pencil' BEGIN
        SELECT @ItemName, @Category; --This seems silly to do after you've done the above statement.
        --Do something else
    END ELSE BEGIN
        --Do something else
    END

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 22, 2017 at 8:56 am

    #1947783

    JohnDoe564 - Thursday, June 22, 2017 8:39 AM

    Phil Parkin - Thursday, June 22, 2017 8:30 AM

    Thanks guys, as usually. 
    It works.

    BTW I am curious, is there actually a way doing it with declare and IF statement?

    Not really. The way you suggested would work only where a single row is being returned from the order_items table. Here is an example:
    DECLARE @item VARCHAR(50);

    --Select @item = item_name from order_items
    SET @item = 'other';

    IF @item = 'charger'
     OR @item = 'cable'
      SELECT
       Item = @item
      , 'electricity';
    ELSE IF @item = 'pen'
       OR @item = 'pencil'
      SELECT
       Item = @item
      , 'accessories';
    ELSE
      SELECT
       Item = @item
      , 'other';

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 22, 2017 at 9:02 am

    #1947785

    IF..ELSE are conditional batch execution directives, not a conditional evaluation like CASE, hence the former is not valid within a select statement which in it self is a batch.
    😎

  • June 22, 2017 at 9:07 am

    #1947787

    Phil Parkin - Thursday, June 22, 2017 8:56 AM

    JohnDoe564 - Thursday, June 22, 2017 8:39 AM

    Phil Parkin - Thursday, June 22, 2017 8:30 AM

    Not really. The way you suggested would work only where a single row is being returned from the order_items table. Here is an example:

    Ok i got it now.

    Thanks again Phil

  • June 26, 2017 at 1:32 pm

    #1948243

    JohnDoe564 - Thursday, June 22, 2017 8:39 AM

    Phil Parkin - Thursday, June 22, 2017 8:30 AM

    Thanks guys, as usually. 
    It works.

    BTW I am curious, is there actually a way doing it with declare and IF statement?

    This is not a good idea. The if – then – else statements are traditionally used for flow control in procedural programming languages. In declarative languages (like SQL). We use a case expression; expressions return a single value of a known data type. It is completely different from flow control which doesn't exist in declarative languages. This tells where your mindset is.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • June 27, 2017 at 7:48 pm

    #1948484

    jcelko212 32090 - Monday, June 26, 2017 1:32 PM

    It is completely different from flow control which doesn't exist in declarative languages. This tells where your mindset is.

    Where on Earth did you learn THAT from?  Flow control is alive, well, and very useful in declarative languages like PL-SQL and T-SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply