query returns no results (I don't mean NULL values

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

SQL Server 2000 Forums

SQL Server Development (2000)

query returns no results (I don't mean NULL values

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-02-03 : 10:49:46

I have a query that looks something like this:

select DMonth, DYear from sometable
where DYear=2002

The problem is no results are returned because 2002 does not exist in the column, DYear. I don't mean it returns null values. In query analyzer, it shows the column headers and nothingelse underneath, not NULL, not any type of value.

I tried doing something like this:

select IsNull(DMonth,12), IsNull(DYear,1999) froms ometable
where DYear=2002

(Where I'm using 12/1999 as a default value.) But the query still returns no results. I don't understand why I can't steer the query to whatever values I choose when it returns no results. I'd appreciate any help.

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-03 : 11:50:56

Sounds like you have no rows where DYear=2002

What is the datatype of DYear? What type of values does it contain? If you do
SELECT top 4 DYear from ometable
what does it return?

Be One with the Optimizer
TG

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-03 : 11:54:35

The problem is that null is different from an empty rowset when no matches are found so isnull wouldn't work here. I think in this case I'd do a select into #temp table and then test @@rowcount.

Create table Test(myval int)

--Will return empty set
select isnull(myval,999) from TEST

--Will return 1111 if no values were returned from query
select t.* into #temp from Test T
if @@rowcount=0
begin
select 1111
END
Else
begin
select * from #temp
END

Mike
"oh, that monkey is going to pay"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 12:19:39

select IsNull(DMonth, 12), IsNull(DYear, 1999) from onetable
where DYear=2002 OR DYear IS NULL

E 12°55'05.63"
N 56°04'39.26"

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-02-03 : 15:26:13

Right. There is no year 2002 in the table I'm querying. The query returns an empty rowset, that's how I should've stated it in the first place. I'll try out that #temp table routine and see what happens, as I want to select default values if there rowset is 0.

Thank you all for your warm help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-03 : 16:46:54

Maybe this is less complicated?


if not exists (select * from sometable where DYear=2002)
begin
  select 12 as DMonth, 1999 as DYear 
end
else
begin
  select DMonth, DYear from sometable where DYear=2002
end

Excuse me if I'm wrong.
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources