include null values in dataset

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)

include null values in dataset

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 20:53:22

Hi everyone, i have a question. how do i include null values in a dataset called 'filter_Type'.

the codes i have is

SELECT DISTINCT TERMINAL_ID AS value,CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL'
FROM OPS_DLY_AGING_DTL
ORDER BY TERMINAL_ID ASC

the value is going to have null values and the other is for the prompt where i replace null values to other location.

please help thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 21:00:29

is this what you want ?


SELECT DISTINCT TERMINAL_ID AS value,CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL',
       NULL as filter_Type
FROM   OPS_DLY_AGING_DTL
ORDER BY TERMINAL_ID ASC

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 21:10:56

i need a code that will help me display null values. like in my preview tab,
i will be able to do a filter which will have my terminals and null values.

Terminal : Null
T1
T2

something like that

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 21:16:28

i have these codes: my main dataset
SELECT CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL', FROM OPS_DLY_AGING_DTL
WHERE ((TERMINAL_ID = @Terminal))

my filter dataset
SELECT DISTINCT TERMINAL_ID AS value,CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL'
FROM OPS_DLY_AGING_DTL
ORDER BY TERMINAL_ID ASC

and my default dataset
SELECT Terminal_ID
FROM OPS_DLY_AGING_DTL
WHERE (TERMINAL_ID IS NULL)
ORDER BY TERMINAL_ID ASC

-----------------------

when i go to my preview tab, i select the null, no null data is displayed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 21:38:43

this ?


SELECT CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL', FROM OPS_DLY_AGING_DTL
WHERE ((TERMINAL_ID = @Terminal))
OR    (@Terminal IS NULL AND TERMINAL_ID IS NULL)

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 21:47:43

Thanks it works. I have another question.

i have this code:

SELECT DISTINCT TERMINAL_ID AS value,CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL'
FROM OPS_DLY_AGING_DTL
UNION
SELECT 'All', 'All'
ORDER BY TERMINAL_ID ASC

is it possible to display other location and all only.

as in all other terminal is not seen. and all displays all the terminal including other location

filter:
other location
All

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 21:51:07

[code]
SELECT DISTINCT TERMINAL_ID AS value,CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL'
FROM OPS_DLY_AGING_DTL
WHERE TERMINAL_ID IS NULL
UNION
SELECT 'All', 'All'
ORDER BY TERMINAL_ID ASC
[/code]

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 21:57:35

if i choose all in the preview tab, no data is displayed. when i choose all, all the terminals and other locations should be displayed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 22:00:23

i am sorry, i don't know what do you mean by "preview tab". If it is something on your front end development tools then i will not be able to help you on this. I have no knowledge in that area.

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-08 : 22:05:42

i am running the ssrs in visual studio. so there is a preview in there... if you dont know then its ok.Thanks for all the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:26:23

quote:
Originally posted by somenoob

i am running the ssrs in visual studio. so there is a preview in there... if you dont know then its ok.Thanks for all the help

for that you need to change original code as


SELECT CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL', FROM OPS_DLY_AGING_DTL
WHERE ((TERMINAL_ID = @Terminal))
OR    (@Terminal IS NULL AND TERMINAL_ID IS NULL)
OR (@Terminal='All')

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:27:34

quote:
Originally posted by khtan

i am sorry, i don't know what do you mean by "preview tab". If it is something on your front end development tools then i will not be able to help you on this. I have no knowledge in that area.

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

Its a tab available in sql reporting services where you can test the reports locally by passing parameter values

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources