Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Nested Case SQL

Posted on 2011-10-05
4
Medium Priority
?
317 Views
Last Modified: 2012-06-27
I am getting an error on this portion of my sql statement.

Trying do the following:

If Feet > 0 then I want to evaluate ItemID.
   If ItemID starts with 'LUM-TUBE' then Units will be 'FT' else they wil be Units
 If ItemID starts with 'LUM-CHANNEL' then Units will be 'LBS' else they wil be Units

Thanks!
CASE WHEN Feet > 0 
                THEN CASE  WHEN  ItemID LIKE 'LUMB-TUBE%' THEN 'FT' ELSE Units END 
         ELSE 
                    CASE WHEN  ItemID LIKE 'LUMB-CHANNEL%' THEN 'LBS' ELSE Units END 
          ELSE Units END AS Units2,

Open in new window

0
Comment
Question by:vwalla
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36920113
CASE WHEN Feet > 0 THEN
    CASE WHEN ItemID LIKE 'LUMB-TUBE%' THEN 'FT'
        WHEN ItemID LIKE 'LUMB-CHANNEL%' THEN 'LBS'
        ELSE Units END
    ELSE Units END AS Units2
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36920193
with one else

case
when feet > 0 and ItemID like 'LUM-TUBE%' then 'FT'
when feet > 0 and ItemID like 'LUM-CHANNEL%' then 'LBS'
else Units
end as Units2
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36920195
I mean with one case :)
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36920205
It looks like Patrick answered you already, but FYI your original CASE was close. You just have TWO ELSEs.

CASE
   WHEN Feet > 0
        THEN CASE  WHEN  ItemID LIKE 'LUMB-TUBE%' THEN 'FT' ELSE Units END
   ELSE CASE WHEN  ItemID LIKE 'LUMB-CHANNEL%' THEN 'LBS' ELSE Units END
   ELSE Units
END AS Units2

That should be just one:

CASE
   WHEN Feet > 0
      THEN CASE WHEN ItemID LIKE 'LUMB-TUBE%' THEN 'FT' ELSE Units END
   ELSE CASE WHEN  ItemID LIKE 'LUMB-CHANNEL%' THEN 'LBS' ELSE Units END
END AS Units2

However, I think you can probably do this like this anyway:

CASE
   WHEN Feet > 0 AND ItemID LIKE 'LUMB-TUBE%' THEN 'FT'
   WHEN Feet <= 0 AND ItemID LIKE 'LUMB-CHANNEL%' THEN 'LBS'
   ELSE Units
END AS Units2

That is basically what your first statement was doing, so if Feet <= 0 is not what you wanted to evaluate along side ItemID LIKE 'LUMB-CHANNEL%' -- see Patrick's correction which corrected the case to include both conditions nested under Feet > 0.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question