select NULL if there are no rows

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 2008 Forums

Transact-SQL (2008)

select NULL if there are no rows

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-09 : 03:57:58

I have select queery where it should return columns based on WHERE condition..

If suppose there are no rows , I want NULL to be populated in that columns.

How can i achieve this??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 04:05:46

[code]
SELECT columns....
FROM
(
SELECT columns,Count(column) OVER() AS Cnt
FROM table
UNION ALL
SELECT NULL,NULL,...
)t
WHERE Cnt =0
OR Column IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 04:06:00

can you show us the query ? It will be easier for us to understand your requirement and provide a solution accordingly

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-09 : 04:17:04

SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT
FROM
(
SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT,Count(Column) OVER() AS Cnt
FROM dbo.dbo_TH609F
WHERE VALIDFLAG=1 AND
ITEMITEM=@ProductCd
UNION ALL
SELECT NULL,NULL,NULL,NULL
)t
WHERE Cnt =0
OR Column IS NOT NULL

Hi Visakh
i am getting the error as mentione below when i execute the above qry..

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 04:29:24

quote:
Originally posted by Sql_forum

SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT
FROM
(
SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT,Count(~~Column~~ITEMITEM) OVER() AS Cnt
FROM dbo.dbo_TH609F
WHERE VALIDFLAG=1 AND
ITEMITEM=@ProductCd
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL
)t
WHERE Cnt =0
OR ~~Column~~ITEMITEM IS NOT NULL

Hi Visakh
i am getting the error as mentione below when i execute the above qry..

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

you need to replace Column with actual columnname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-09 : 04:38:13

Hi Visakh

After executing the query , i m not getting NULL values in columns

I want output as follows
---------------------------------------
ITEM VALIDFLAG INDIC PRCNT
NULL NULL NULL NULL
---------------------------------------
currently it is not returning anyrws

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 04:42:22

[code]
SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT
FROM
(
SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT,Count(1) OVER() AS Cnt
FROM
(
SELECT ITEMITEM, VALIDFLAG,INDIC,PRCNT
FROM dbo.dbo_TH609F
WHERE VALIDFLAG=1 AND
ITEMITEM=@ProductCd
UNION ALL
SELECT NULL,NULL,NULL,NULL
)t
)r
WHERE Cnt =1
OR ITEMITEM IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-09 : 04:45:38

grt......
Thnx alot Visakh.. it is working as i need

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources