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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

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?



0
jandhb
Asked:
jandhb
  • 5
  • 3
1 Solution
 
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
Independent Software Vendors: 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!

 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now