ISNull Function Enquiry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ISNull Function Enquiry

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-02-05 : 02:40:57

Hi Experts,

I am trying to get the value of 0 if there are no GLAccountID found matching the records, or the GLAccountID if there is any records returned.

However, my statement seems to return nothing (not 0) if there are no records. Did I do anything wrong or can I rewrite the query so that it will return 0 if there is no GLaccountID for a record? Any help is very much appreciate.

Select isnull(GLaccountID, 0) as GLAccountID from Finance.FinanceGLAccount F
where GLAccountNo = '3100-0000-0003'
and F.CompanyID = 'FAC1AF11-41E5-4CD4-8DE7-935F9F9D261F'

Table Finance.FinanceGLAccount
---------------------------------
GLAccountID (PK) autonumber
GLaccountNo (FK to another table)
CompanyID (FK to company table)
GLAccountName varchar(50)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 02:55:22

nothing to do with isnull(). When there isn't any match, there will not be any record return at all

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2010-02-05 : 02:57:06

Is null will return 0 only if there is a null against the record.If there are no matching records it wont return anything.

declare @tbl as table(id int,name varchar(5))
insert into @tbl
select 1,'a' union all
select null,'b' union all
select 3,'c'

select isnull(id,0) from @tbl where name='b'
select isnull(id,0) from @tbl where name='d'

PBUH

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-02-05 : 03:04:33

ahhh, that's why I got nothing even if there is no record. So I should first use a count of the number of records return (which will return 0 or 1), then based on the count, I will either return 0 or return the ID? Thanks for the explaination - I was getting confused just now.

Declare @count int
Select @count = count(*) from Finance.FinanceGLAccount F
where GLAccountNo = '3100-0000-0000'
and F.CompanyID = 'FAC1AF11-41E5-4CD4-8DE7-935F9F9D261F'

If @count > 0
BEGIN
Select @count = GLAccountID from Finance.FinanceGLAccount F
where GLAccountNo = '3100-0000-0000'
and F.CompanyID = 'FAC1AF11-41E5-4CD4-8DE7-935F9F9D261F'
End

select @count

works perfectly. This is what I am trying to accomplish. Thanks for the explaination

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 03:07:01

[code]
Declare @count int
Select @count = count(*) from Finance.FinanceGLAccount F
where GLAccountNo = '3100-0000-0000'
and F.CompanyID = 'FAC1AF11-41E5-4CD4-8DE7-935F9F9D261F'

If @count > 0
BEGIN
select @count = 0
Select @count = GLAccountID from Finance.FinanceGLAccount F
where GLAccountNo = '3100-0000-0000'
and F.CompanyID = 'FAC1AF11-41E5-4CD4-8DE7-935F9F9D261F'
~~End~~

select @count
[/code]

KH
[spoiler]Time is always against us[/spoiler]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-02-05 : 03:16:29

thanks KH, your solution is a lot more effective than mine. What happens is that the @count is set to 0

if there is nothing, the @count will not be reset and will always return the 0 value.

If there is a GLaccountId, it will be reset tp GLaccountID value right?

Nice. Learnt some new tricks today. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 03:28:36

Yup. Correct. I just initialize the @count to 0. It will be assign with GLAccountID if there is any records return from the query.

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 03:55:06

Another approach (depends on what you are doing, nothing wrong with the approach above!) is that you can do:


SELECT ...
FROM ...
WHERE ...
IF @@ROWCOUNT = 0
BEGIN
    ...
END

Note that you must test (or save it in another variable) @@ROWCOUNT as the very next statement after your SELECT because @@ROWCOUNT is re-used by SQL for every SELECT / UPDATE / DELETE etc ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources