select case with multiple

Question

select case with multiple

kmundt

SSC Journeyman

Points: 77
More actions
March 28, 2013 at 7:58 am

#272278

Here is my scenario, The user puts in a customerID, possible start and end date and selects a status from a radiobutton list. The radiobutton list is 'closed, open or both', so when the user selects both I have to pull the status that is 'C' - closed and Open which is this case is NULL(converted unibasic data). Even when I use THEN NULL for Open is returns nothing. I thought I could do a CASE with an IN with 'C, NULL' but that just returns nothing.
Declare @CustID VARCHAR(10)
Declare @StartDate DATETIME
Declare @EndDate DATETIME
Declare @status VARCHAR(1)
SET @CustID = 2395
SET @StartDate = NULL
SET @EndDate = NULL
SET @status = 'O'
SELECT SOHNbr,CustNbr, ISNULL(Order_Del_Ext_Price, 0.00) AS Order_Del_Ext_Price, ISNULL(Order_Del_Ext_Price, 0.00) AS Currency_Order_Total, 'Order_Status' =
CASE
WHEN Order_Status = 'C' THEN 'Closed'
ELSE 'Open'
END
FROM SOH
WHERE CustNbr = @CustID AND (@StartDate IS NULL OR So_Date >= @StartDate)
AND (@EndDate IS NULL OR So_Date <= @EndDate)
AND Order_Status IN (CASE WHEN @status = 'C' THEN 'C'
WHEN @status = 'O' THEN NULL
WHEN @status = 'B' THEN 'C, NULL'
WHEN @status = NULL THEN NULL
END)
ORDER BY So_Date, SoHNbr asc

Viewing 6 posts - 1 through 5 (of 5 total)

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

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

kmundt (3/28/2013)
Here is my scenario, The user puts in a customerID, possible start and end date and selects a status from a radiobutton list. The radiobutton list is 'closed, open or both', so when the user selects both I have to pull the status that is 'C' - closed and Open which is this case is NULL(converted unibasic data). Even when I use THEN NULL for Open is returns nothing. I thought I could do a CASE with an IN with 'C, NULL' but that just returns nothing.
Declare @CustID VARCHAR(10)
Declare @StartDate DATETIME
Declare @EndDate DATETIME
Declare @status VARCHAR(1)
SET @CustID = 2395
SET @StartDate = NULL
SET @EndDate = NULL
SET @status = 'O'
SELECT SOHNbr,CustNbr, ISNULL(Order_Del_Ext_Price, 0.00) AS Order_Del_Ext_Price, ISNULL(Order_Del_Ext_Price, 0.00) AS Currency_Order_Total, 'Order_Status' =
CASE
WHEN Order_Status = 'C' THEN 'Closed'
ELSE 'Open'
END
FROM SOH
WHERE CustNbr = @CustID AND (@StartDate IS NULL OR So_Date >= @StartDate)
AND (@EndDate IS NULL OR So_Date <= @EndDate)
AND Order_Status IN (CASE WHEN @status = 'C' THEN 'C'
WHEN @status = 'O' THEN NULL
WHEN @status = 'B' THEN 'C, NULL'
WHEN @status = NULL THEN NULL
END)
ORDER BY So_Date, SoHNbr asc

When @status = 'B' you're trying to pull back records that have a string 'C' or a STRING 'NULL'. If you want to pull all records back use something like

WHEN @status = 'B' then Order_Status

You also realize that when @status isn't set you're pulling only the Open records? Just checking.

Erin

kmundt SSC Journeyman Points: 77 More actions · Answer 2

The status is always being set, because I require them in .NET to select something, but even when they select both and it's 'B' it just selects the Closed fields ('C') and 'O' for some reason doesn't select anything. It should be selecting null values statuses.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

kmundt (3/28/2013)
The status is always being set, because I require them in .NET to select something, but even when they select both and it's 'B' it just selects the Closed fields ('C') and 'O' for some reason doesn't select anything. It should be selecting null values statuses.

There are several errors in the CASE, including matching to NULL using =.

Try longhand instead:

SELECT

SOHNbr,

CustNbr,

ISNULL(Order_Del_Ext_Price, 0.00) AS Order_Del_Ext_Price,

ISNULL(Order_Del_Ext_Price, 0.00) AS Currency_Order_Total,

'Order_Status' =

CASE

WHEN Order_Status = 'C' THEN 'Closed'

ELSE 'Open'

END

FROM SOH

WHERE CustNbr = @CustID

AND (@StartDate IS NULL OR So_Date >= @StartDate)

AND (@EndDate IS NULL OR So_Date <= @EndDate)

AND (

(@Status = 'C' AND Order_Status = 'C')

OR

(@Status = 'O' AND Order_Status IS NULL)

OR

(@Status = 'B' AND (Order_Status = 'C' OR Order_Status IS NULL))

OR

(@Status IS NULL AND Order_Status IS NULL)

)

ORDER BY So_Date, SoHNbr asc

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Your issue is here...

WHEN @status = NULL THEN NULL

That will never evaluate to true. You should instead use

WHEN @status IS NULL THEN NULL

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kmundt SSC Journeyman Points: 77 More actions · Answer 5

That works perfect. I was over thinking it. Thank you.

ChrisM@Work (3/28/2013)
kmundt (3/28/2013)
The status is always being set, because I require them in .NET to select something, but even when they select both and it's 'B' it just selects the Closed fields ('C') and 'O' for some reason doesn't select anything. It should be selecting null values statuses.
There are several errors in the CASE, including matching to NULL using =.
Try longhand instead:
SELECT
SOHNbr,
CustNbr,
ISNULL(Order_Del_Ext_Price, 0.00) AS Order_Del_Ext_Price,
ISNULL(Order_Del_Ext_Price, 0.00) AS Currency_Order_Total,
'Order_Status' =
CASE
WHEN Order_Status = 'C' THEN 'Closed'
ELSE 'Open'
END
FROM SOH
WHERE CustNbr = @CustID
AND (@StartDate IS NULL OR So_Date >= @StartDate)
AND (@EndDate IS NULL OR So_Date <= @EndDate)
AND (
(@Status = 'C' AND Order_Status = 'C')
OR
(@Status = 'O' AND Order_Status IS NULL)
OR
(@Status = 'B' AND (Order_Status = 'C' OR Order_Status IS NULL))
OR
(@Status IS NULL AND Order_Status IS NULL)
)
ORDER BY So_Date, SoHNbr asc