May 30, 2011 at 4:53 pm
I have:
SELECT *
FROM tablename
WHERE AccountID = '000001'
This returns a blank value as that AccountID does not exist in the table. But, instead of returning nothing, I need it to return a 1.
I am trying:
SELECT CASE WHEN AccountID = '' THEN '1' ELSE AccountID End AS AccountID
FROM tablename
WHERE AccountID = '000001'
and this still returns a blank, not the value of 1 I am expecting. I have tried numerous different things to get this to work, I've Googled alot of different things and still no luck.
Maybe a CASE statement is not the way to go??? I am not sure...
Any and All help will be greatly appreciated.
May 30, 2011 at 6:51 pm
If the record does not exist it won't return an empty string '', but rather null. You therefore need to test for whether a null is returned. The easiest way using your query below would be to use the isnull function, eg.
SELECT isnull(AccountID, '1') as 'AccountID'
FROM tablename
WHERE AccountID = '000001'
May 30, 2011 at 7:19 pm
Whoops. Reminder to self: test all code, especially before posting.
May 30, 2011 at 7:29 pm
Thanks gentleman for the replies, but using what you guys suggested is not what I am looking for. I am still getting an empty set back.
My goal in this is to return an AccountID if there is one and if not return something other than an empty set (a 1 or a No AccountID), whatever.
If you guys have other suggestions, please share.
Thanks Again...
May 30, 2011 at 11:24 pm
GBeezy (5/30/2011)
Thanks gentleman for the replies, but using what you guys suggested is not what I am looking for. I am still getting an empty set back.My goal in this is to return an AccountID if there is one and if not return something other than an empty set (a 1 or a No AccountID), whatever.
If you guys have other suggestions, please share.
Thanks Again...
What do you mean exactly by "empty set"?
May 30, 2011 at 11:52 pm
GBeezy (5/30/2011)
I have:SELECT *
FROM tablename
WHERE AccountID = '000001'
This returns a blank value as that AccountID does not exist in the table. But, instead of returning nothing, I need it to return a 1.
I am trying:
SELECT CASE WHEN AccountID = '' THEN '1' ELSE AccountID End AS AccountID
FROM tablename
WHERE AccountID = '000001'
and this still returns a blank, not the value of 1 I am expecting. I have tried numerous different things to get this to work, I've Googled alot of different things and still no luck.
Maybe a CASE statement is not the way to go??? I am not sure...
Any and All help will be greatly appreciated.
I don't think so you will be able to Achieve this by case statement .
Think flow of Query .
first in query it evaluates Where condition now here if we get result set then case Statement will be executed ,but on empty result set(i.e. WHERE AccountID = '000001' does not return any row)case statement will not be executed ,so no matter what you will not be able to show 1 by using such condition,you can use following code.may it will help you.
IF EXISTS ( SELECT 1
FROM tablename
WHERE AccountID = '000001' )
PRINT 'We found row'
ELSE
PRINT 'No Matching criteria'
May 30, 2011 at 11:55 pm
As per my intepretation of your question iv written this snippet..
hope it helps..
CREATE TABLE #ACCT(ACCOUNTID NVARCHAR(50),VALUE INT IDENTITY(1,1))
INSERT INTO #ACCT(ACCOUNTID)
SELECT '' UNION ALL
SELECT '' UNION ALL
SELECT '' UNION ALL
SELECT '' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'
SELECT * FROM #ACCT
SELECT CASE WHEN ACCOUNTID='' THEN '1' ELSE ACCOUNTID END FROM #ACCT
WHERE VALUE=4
If ur sure that the value would be a null then go for isnull().
May 31, 2011 at 5:52 am
Gaurang, Thank you very much this works perfectly. Thanks to everyone else for the replies.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply