sql query (if statement)

I have this query similar to this...

(SELECT     SUBSTRING(ProductCode, 1, 3)
FROM          tblProd
WHERE      productcode = tblKt.ProductCode)) + ' ' +
(SELECT     Color
FROM          tblCol
WHERE      ColorCode =
(SELECT     SUBSTRING(ProductCode, 21, 3)
FROM          tblProd
WHERE      productcode = tblKt.ProductCode)) + ' ' +

I'm using SQL Server 2000 and would like to add an if statement to check if the ColorCode is a certain code. If it is then I want the string not display the colorcode, but just a blank space. How would this be done?



LVL 1
jandhbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thrill_houseCommented:
You need to use a "Case" statement.  You can read all about it in Books Online, but it's quite simple to use.

Select case when Color = 'Color you want' then '' else Color end as Color

This will do exactly what you want.
0
thrill_houseCommented:
Here it is in context:

Select case when Color = 'Color you want' then '' else Color end as Color
From tblCol
Where ....
0
jandhbAuthor Commented:
thrill house, this is my entire query at this point. Can you show me what I would need to do so that when ColorCode = '099' it returns a blank space, else it would return color.

SELECT     '1' AS Header, Quantity * 1 AS Quantity,
                          (SELECT     Process
                            FROM          tblProcess
                            WHERE      Processcode =
                                                       (SELECT     SUBSTRING(ProductCode, 1, 3)
                                                         FROM          tblProduct
                                                         WHERE      productcode = tblKit.ProductCode)) + ' ' +
                          (SELECT     Color
                            FROM          tblColor
                            WHERE      ColorCode =
                                                       (SELECT     SUBSTRING(ProductCode, 21, 3)
                                                         FROM          tblProduct
                                                         WHERE      productcode = tblKit.ProductCode)) + ' ' +
                          (SELECT     Product
                            FROM          tblProduct
                            WHERE      productCode = tblKit.ProductCode) AS SkinType,
                          (SELECT     tblSize.sizeFinal
                            FROM          tblSize
                            WHERE      tblSize.sizecode = substring(tblKit.productcode, 9, 3)) AS XprFinalSize, '' AS XprLiveSize,
                          (SELECT     tblSize.sizePrint
                            FROM          tblSize
                            WHERE      tblSize.sizecode = substring(tblKit.productcode, 9, 3)) AS XprPrintSize
FROM         tblKit
WHERE     (ProductCodeKitID = '203-330-998-110-301-007')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

thrill_houseCommented:
This should do it, if I understand the query correctly.

SELECT     '1' AS Header, Quantity * 1 AS Quantity,
                          (SELECT     Process
                            FROM          tblProcess
                            WHERE      Processcode =
                                                       (SELECT     SUBSTRING(ProductCode, 1, 3)
                                                         FROM          tblProduct
                                                         WHERE      productcode = tblKit.ProductCode)) + ' ' +
                          (SELECT     case when ColorCode = '099' then '' else Color end as Color
                            FROM          tblColor
                            WHERE      ColorCode =
                                                       (SELECT     SUBSTRING(ProductCode, 21, 3)
                                                         FROM          tblProduct
                                                         WHERE      productcode = tblKit.ProductCode)) + ' ' +
                          (SELECT     Product
                            FROM          tblProduct
                            WHERE      productCode = tblKit.ProductCode) AS SkinType,
                          (SELECT     tblSize.sizeFinal
                            FROM          tblSize
                            WHERE      tblSize.sizecode = substring(tblKit.productcode, 9, 3)) AS XprFinalSize, '' AS XprLiveSize,
                          (SELECT     tblSize.sizePrint
                            FROM          tblSize
                            WHERE      tblSize.sizecode = substring(tblKit.productcode, 9, 3)) AS XprPrintSize
FROM         tblKit
WHERE     (ProductCodeKitID = '203-330-998-110-301-007')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jandhbAuthor Commented:
thrill house, thank you! it works.

Just out of curiosity here...how is this different then from what you see when people use actual if statements?
0
thrill_houseCommented:
The 'if' statement in t-sql is used when based on a condition you execute a t-sql statement.  

The case statement is a condition that is used to return an alternative value.
0
jandhbAuthor Commented:
ok. So then I could use an 'if' statement if need be?

I'm just asking because initially I tried to use an 'if' statement, but received an error. Maybe it was just the syntax.

Just curious if there was a certain environment it had to be in, i.e. stored procedure.
0
thrill_houseCommented:
For the most part yet, it needs to be in a stored procedure.  It can be done adhoc, but you must be doing a series of statements to really use it.

Think of it this way: If you need an if statement, use case when you are selecting, updating, inserting, or deleting.  But you probably will use If for everything else.

Insert into table (x) values (1)

Declare @y as int
@y = @@Identity

if @y = 1
BEGIN
    ... do something
END
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.