About 0 rows affected - 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

SQL Server 2005 Forums

Transact-SQL (2005)

About 0 rows affected

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-09 : 21:24:51

Hello,

I create a table in this way.

select id,value from table1 where id>99

The question is I may be get null, but I still want to display as

id  value
1    null

instead of without any result.

id value

Please don't use if count(*)=0.

Thanks.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-09 : 23:41:12

this may help, give it a try

SELECT COALESCE(T1.Id, T2.Id) AS Id, T2.Value
FROM (SELECT 1 AS Id) T1
LEFT JOIN table1 T2 ON (T1.Id = T2.Id
						AND T2.Id = 0)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 02:25:09

quote:

Originally posted by PeterNeo

this may help, give it a try

SELECT COALESCE(T1.Id, T2.Id) AS Id, T2.Value
FROM (SELECT 1 AS Id) T1
FULL OUTER LEFT JOIN table1 T2 ON (T1.Id = T2.Id
						AND T2.Id = 0)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

shouldnt it be FULL OUTER JOIN?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 02:43:38

[code]select id, case when id > 99 then value else null end from table1[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 03:04:40

quote:
Originally posted by Peso

select id, case when id > 99 then value else null end from table1

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

will this return default values when table has no data at all?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 03:12:38

Why should it? See original post.

The WHERE clause is "> 99" and yet a record with ID 1 shows up.
Maybe he wants all records to be displayed? And only those records satisfying his original WHERE clause should display real value, other records should display NULL?

This is the way I interpreted the original question.
I recognize the question was not particular well defined.

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

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-10 : 09:37:22

This is the modified version.I want to make it simple but perhaps not good.
The original question is there is a temp table #TmpRptData.

But if I

select * #TmpRptData

It may be empty(some values are zeros). In that case I want to display it.

Original I used count(*) then insert one row if it is 0.

insert #TmpRptData 
select @RegID as RegID,1 AS RptGrp,'' as GrpName,'Cash' as ColName,'' as subColAmt,'' as subColCount,0 as ColAmt,0 as ColCount,@EmpID as EmpID

Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:04:40

ok...so may be i got confused by first response

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-12-10 : 10:13:20

I am sorry about it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources