Microsoft, SQL, 2005, Union and Case Statement Help Needed

I have 6 DoctorFacility Types (Doctor = 1, Facility = 2, Referring Doctor = 3, Company = 5, Resource = 6
and Other Provider = 7)

With my Code Snippet below, I am getting the following results back:

Text      ItemData
NULL      1
NULL      2
NULL      3
NULL      5
NULL      6
NULL      7
(All)      0
Doctor      1
Other Provider      7
Referring Doctor      3

What I want only:

(All)      0
Doctor      1
Other Provider      7
Referring Doctor      3

I dont want to see the others in my list ... just exactly what I have above this sentence.
SELECT
        '(All)' AS Text,
        0 AS ItemData
UNION 
SELECT
        CASE WHEN TYPE = 1 THEN 'Doctor' END AS TEXT,
        type AS Itemdata
FROM
        DoctorFacility
UNION 
SELECT 
        CASE WHEN TYPE = 3 THEN 'Referring Doctor' END AS TEXT,
        type AS Itemdata
FROM
        DoctorFacility
UNION 
SELECT 
        CASE WHEN TYPE = 7 THEN 'Other Provider' END AS TEXT,
		type AS Itemdata    
FROM
        DoctorFacility

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
HuyBDConnect With a Mentor Commented:
try to add more condition to move unselected item out
SELECT
        '(All)' AS Text,
        0 AS ItemData
UNION
SELECT
CASE  
WHEN type=1 THEN 'Doctor'
WHEN TYPE = 3 THEN 'Referring Doctor'
WHEN TYPE = 7 THEN 'Other Provider'
END AS TEXT
,type AS Itemdata
FROM
        DoctorFacility
WHERE TYPE IN(1,3,7)
GROUP BY type 

Open in new window

0
 
HuyBDCommented:
try this
SELECT 
CASE  
WHEN type=1 THEN 'Doctor'
.....
END AS TEXT
,type AS Itemdata
FROM
        DoctorFacility
GROUP BY type 

Open in new window

0
 
Jeff SAuthor Commented:
HuyBD

I modified your coding slightly to this:

SELECT
        '(All)' AS Text,
        0 AS ItemData
UNION
SELECT
CASE  
WHEN type=1 THEN 'Doctor'
WHEN TYPE = 3 THEN 'Referring Doctor'
WHEN TYPE = 7 THEN 'Other Provider'
END AS TEXT
,type AS Itemdata
FROM
        DoctorFacility
GROUP BY type

But now get this:
Text                     ItemData
NULL                        2
NULL                        5
NULL                        6
(All)                        0
Doctor                        1
Other Provider        7
Referring Doctor        3

I do not want ItemData's 2, 5 or 6.
0
 
jfmadorCommented:
There is null value because you only specify one value in your case statement

Take a look to your query, what happen for the doctor facility 2 to 7, they will return null for the text.
SELECT
        CASE WHEN TYPE = 1 THEN 'Doctor' END AS TEXT,
        type AS Itemdata
FROM
        DoctorFacility


Try this

SELECT
        '(All)' AS Text,
        0 AS ItemData
UNION ALL
SELECT
        CASE WHEN TYPE = 1 THEN 'Doctor'
                  WHEN TYPE = 2 THEN 'Facility'
                  WHEN TYPE = 3 THEN 'Referring Doctor'
                  WHEN TYPE = 4 THEN ''
                  WHEN TYPE = 5 THEN 'Company'
                  WHEN TYPE = 6 THEN 'Resource'
                  WHEN TYPE = 7 THEN 'Other Provider' END AS TEXT,
        type AS Itemdata
FROM
        DoctorFacility

You can add where type in (1,3,7) if you want only these choice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.