April 16, 2009 at 7:23 am
Hi,
This question mainly concerns to two columns. Column names are StatusDetail and StatusID.
What I'm trying to figure out is when I have a StatusDetail of Blank or (StatusDetail='' or StatusDetail=NULL) then I'd want my StatusID to be 9. If there is something in StatusDetail (StatusDetail='ABCDEFGH') then I'd want my StatusID to be 11.
Please see my query below if it helps but it could confuse you. Above is what I'm trying to do. Thanks.
DECLARE @filekey int, @statusid int
SET @filekey = 117664 --Filekey
SET @statusid = 9 --11 will show you the batch number if available
SELECT c.name As Name,
a.supercompanyid As 'Super Company ID',
a.filekey As 'File Key',
b.fileid As 'File ID',
--SUBSTRING(a.statusdetail, 7, 10) As 'Batch Number',
b.TotalRecords As 'Total Records',
b.ErrorRecords As 'Error Reords',
b.NumberofRecords As 'Processed Records',
CASE (a..statusdetail)
WHEN NULL THEN a.Statusid=9
ELSE a.statusid=11
END
FROM etldev.auto.dbo.filestatus_history a
INNER JOIN etldev.mortgage.dbo.filekeys b ON a.filekey = b.filestatusid
INNER JOIN etldev.mortgage.dbo.insurancecompanymaster c ON a.supercompanyid=c.supercompanyid
WHERE a.filekey IN (@filekey) and a.Statusid=@statusid
Thanks,
O
--
:hehe:
April 16, 2009 at 7:28 am
Are you just trying to select this info? If so then just take the alias of the columns.
Try this.
CASE (a.statusdetail)
WHEN NULL THEN 9
ELSE 11
END as StatusID
_______________________________________________________________
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/
April 16, 2009 at 7:39 am
Case statement is not a flow control statement. It is a function that returns a value. In your code you are using it as a control statement that sets value to a.statusdetail. You need to lose the a.statusdetial=X in the case statement (after the then part of the case statement)
…when null then 9
Else 11
Also notice that you wrote that if status = '' then it should also return 9, but in your code you didn’t specify that. Null and empry string are not the same. If you need the case statement to return 9 if you have an empty string, you’ll need to modify your code.
This part was added a day after my original post. As Christian Buettner
wrote, my code will never work properly. the criteria:
when null then 9
will never be evaluated as true even if a.statusdetail is null. The correct way of checking for null will be:
...case when a.statusdetail is null then 9
else 11
end...
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 16, 2009 at 7:48 am
Slange,
Doesn't work. I'm not trying to do a simple select, I think i'd be able to figure that out myself :-P.
I'm trying to select based on a criteria.
What happens is when I set the @filekeys and the @statusid to 9 it will bring the results. Because, I've manually set the statusid to 9. If I changed it to 11... it wouldnt bring back results.
I want my query to be dynamic instead of static.
I want it to check both status id 9 and 11 and I want it to display the correct statusdetail (Blank or with content) based on the status id.
StatusID 9 does not show StatusDetail, StatusID 11 does show StatusDetail. However, if there is something in StatusDetail and StatusID is set to 9, it will not show anything. Which is wrong and makes my statement that my query is currently static.
Does this make sense?
--
:hehe:
April 16, 2009 at 7:51 am
Can you post the create scripts, some sample data and the desired output? That way we are on the same page. It sounds like the case is exactly what you want but I am unclear on all the details.
_______________________________________________________________
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/
April 16, 2009 at 7:51 am
Adi,
I'm thinking if I shuold be using an IF statement instead of a CASE WHEN? I just want my query to be more dynamic than currently and be able to know that if statusdetail has content use statusid 11 and if it doesnt or is null then use statusid 9.
I'm confused as what to use...
Thanks,
O
--
:hehe:
April 16, 2009 at 7:52 am
Sure, Slange. I'll post here in a minute.
--
:hehe:
April 16, 2009 at 7:52 am
Actually, when i was looking again at your code the case statement has only 1 valid path. You are filtering out every value for StatusID except what is in @status
_______________________________________________________________
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/
April 16, 2009 at 7:56 am
Not sure what you mean. If you mean my logic is wrong.. I'm thinking the same. How do I correct it?
--
:hehe:
April 16, 2009 at 7:58 am
I tweaked your code a little bit more. I removed the in because it was checking for a single value and removed the statusid check at the end. I think this is getting closer to what you want. :hehe:
SELECT c.name As Name,
a.supercompanyid As 'Super Company ID',
a.filekey As 'File Key',
b.fileid As 'File ID',
b.TotalRecords As 'Total Records',
b.ErrorRecords As 'Error Reords',
b.NumberofRecords As 'Processed Records',
CASE WHEN isnull(a.statusdetail, '') = '' THEN 9
ELSE 11
END as statusid
FROM etldev.auto.dbo.filestatus_history a
INNER JOIN etldev.mortgage.dbo.filekeys b ON a.filekey = b.filestatusid
INNER JOIN etldev.mortgage.dbo.insurancecompanymaster c ON a.supercompanyid=c.supercompanyid
WHERE a.filekey = @filekey --) and a.Statusid=@statusid
_______________________________________________________________
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/
April 16, 2009 at 8:03 am
😎 Definitely closer. I just ran it and it returns all the result options such as:
StatusID = 9 And StatusDetail Empty (Twice for some reason)
StatusID = 9 and StatusDetail NULL
StatusID = 11 and StatusDetail WITH CONTENT
Thanks man....so I think it's the right direction..
--
:hehe:
April 16, 2009 at 8:05 am
Glad that worked for you. Let me know if you need some more help on it and I will see what I can do. 😀
_______________________________________________________________
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/
April 16, 2009 at 11:50 am
Just for additional clarification:
CASE Fieldxy WHEN NULL THEN ...
This case will always evaluate to false
the correct code in this case would be:
CASE WHEN Fieldxy IS NULL THEN ...
Here you can find more information
http://en.wikipedia.org/wiki/Null_(SQL)#CASE_expressions
Best Regards,
Chris Büttner
April 16, 2009 at 2:07 pm
[font="Verdana"]Based on some earlier comments, I suspect you are trying to do something like this:
select [...]
where a.filekey in (@filekey) and
a.Statusid = case when a.StatusDetail is null then 9 else 11 end
[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply