How can i rewrite my query to not return the null values caused by the case statement

How can I rewrite my query to not return the null values caused by the case statement in my select statement.  I believe if I add the case statement as a left join I can get the results that I need but I am not sure how to write the query logic or if that is the best solution.

Please provide examples. I am using MS SQL 2008 R2.


Current Query  syntax:

Select
Order,
id,
name  =   CASE WHEN a.code = 'C' THEN
          CASE WHEN a.class = 'S' THEN RTRIM(sa.signname)
              WHEN a.class = 'C' THEN RTRIM(c.custname)
              WHEN a.class = 'E' THEN RTRIM(en.user_desc) END
   END
From tbl_ABC a
LEFT JOIN dbo.o_a a
ON a.order = oma.order
LEFT JOIN customer c
ON a.id = c.customer_id
AND a.class = 'C'
LEFT JOIN sign sa
ON a.id = sa.sign_id
AND a.class = 'S'       
LEFT JOIN euser en
ON a.id = en.euser_id
AND a.class = 'E'            

This is the current output from the query:
  order           id      Name
300007      24      NULL
300007      24      CYBERTEXT
300007      25      NULL
300007      25      CYBERTEXT
300007      40      NULL
300007      40      CYBERTEXT
300007      42      NULL
300007      42      CYBERTEXT
300007      44      NULL
300007      44      CYBERTEXT

How can I get the output to return a single row without the null values which are a result of the case statement:
  order            id      Name
300007      24      CYBERTEXT
300007      25      CYBERTEXT
300007      40      CYBERTEXT
300007      42      CYBERTEXT
300007      44      CYBERTEXT


Thanks,
MTD
MoreThanDoubledAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Doubled,

You've got a nested CASE structure and only handle one value in the outer CASE construct.  Anything that doesn't match the outer case results in NULL.

Current:

name  =   CASE WHEN a.code = 'C' THEN
          CASE WHEN a.class = 'S' THEN RTRIM(sa.signname)
              WHEN a.class = 'C' THEN RTRIM(c.custname)
              WHEN a.class = 'E' THEN RTRIM(en.user_desc) END
   END

should be:

name  =   CASE WHEN a.code = 'C' THEN
          CASE WHEN a.class = 'S' THEN RTRIM(sa.signname)
              WHEN a.class = 'C' THEN RTRIM(c.custname)
              WHEN a.class = 'E' THEN RTRIM(en.user_desc)
              ELSE value when a.class is not S, C, or E END
           ELSE value when a.code is not C END


Add the two values shown in the second example and the query will quit setting name to NULL.


Good Luck,
Kent
0
sdstuberCommented:
SELECT *
  FROM (SELECT order,
               id,
               CASE
                   WHEN a.code = 'C'
                   THEN
                       CASE
                           WHEN a.class = 'S' THEN RTRIM(sa.signname)
                           WHEN a.class = 'C' THEN RTRIM(c.custname)
                           WHEN a.class = 'E' THEN RTRIM(en.user_desc)
                       END
               END
                   AS name
          FROM tbl_abc a
               LEFT JOIN dbo.o_a a ON a.order = oma.order
               LEFT JOIN customer c ON a.id = c.customer_id AND a.class = 'C'
               LEFT JOIN SIGN sa ON a.id = sa.sign_id AND a.class = 'S'
               LEFT JOIN euser en ON a.id = en.euser_id AND a.class = 'E')
 WHERE name IS NOT NULL
0
MoreThanDoubledAuthor Commented:
kdo, when using your logic I still don't get a single row return
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

sdstuberCommented:
does mine do what you're looking for?
0
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  A couple of things.

You're showing that you get two rows of output for every row in tbl_ABC.  The way the query is structured suggests that you've got 2 rows in oma that match keys in tbl_ABC.  The easiest thing to do is to filter as sdstuber suggests.  Better would be to join the correct rows, if possible.

Also, I assumed a typo here:

From tbl_ABC a
LEFT JOIN dbo.o_a a
ON a.order = oma.order

That's not valid.
0
Russell FoxDatabase DeveloperCommented:
Since you're including the code/class/class parameters in the join condition, I don't believe you need the case statement at all:
SELECT 
	[Order], 
	[id], 
		COALESCE(RTRIM(sa.signname), RTRIM(c.custname), RTRIM(en.user_desc))
	AS [Name]  
FROM tbl_ABC a
	INNER JOIN dbo.o_a a 
		ON a.order = oma.order 
		AND a.code = 'C'
	LEFT JOIN customer c 
		ON a.id = c.customer_id 
		AND a.class = 'C'
	LEFT JOIN sign sa 
		ON a.id = sa.sign_id 
		AND a.class = 'S'       
	LEFT JOIN euser en
		ON a.id = en.euser_id 
		AND a.class = 'E'
WHERE COALESCE(RTRIM(sa.signname), RTRIM(c.custname), RTRIM(en.user_desc)) IS NOT NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MoreThanDoubledAuthor Commented:
sdstuber,

ideally this would work but sometimes I get a second and third row of data in another field.
order_id      id      Name      Name 2      Name 3
3007      24      NULL      NULL      AMERICAN COMPANY
3007      24      NULL      EAGLE COMPANY      NULL
3007      24      OMINSOURCE      NULL      NULL
3007      25      NULL      NULL      AMERICAN COMPANY
3007      25      NULL      EAGLE COMPANY      NULL
3007      25      OMINSOURCE      NULL      NULL
3007      40      NULL      NULL      AMERICAN COMPANY
3007      40      NULL      EAGLE COMPANY      NULL
3007      40      OMINSOURCE      NULL      NULL
3007      42      NULL      NULL      AMERICAN COMPANY
3007      42      NULL      EAGLE COMPANY      NULL
3007      42      OMINSOURCE      NULL      NULL
3007      44      NULL      NULL      AMERICAN COMPANY
3007      44      NULL      EAGLE COMPANY      NULL
3007      44      OMINSOURCE      NULL      NULL


Is there another way to get my data in a single row in the from clause?
0
sdstuberCommented:
coalesce(name,name2,name3)  

will let you combine the values into a single column


if that doesn't do it,then I'm probably not understanding what you are looking for.

Given that sample input, what would be the expected output?
0
Kent OlsenData Warehouse Architect / DBACommented:
Each join of customer, sign, and euser has the potential to generate a row in the result set.  If all 3 tables successfully join to tbl_ABC, you'll get 3 rows in the result.

You can simplify the CASE logic if you'll concatenate the code and class columns.

Select DISTINCT
Order,
id,
name  =   coalesce (
    CASE WHEN a.code || a.class = 'CS' THEN RTRIM(sa.signname)
    WHEN a.code || a.class = 'CC' THEN RTRIM(c.custname)
    WHEN a.code || a.class = 'CE' THEN RTRIM(en.user_desc) END)

From tbl_ABC a
LEFT JOIN dbo.o_a a
ON a.order = oma.order
LEFT JOIN customer c
ON a.id = c.customer_id
AND a.class = 'C'
LEFT JOIN sign sa
ON a.id = sa.sign_id
AND a.class = 'S'       
LEFT JOIN euser en
ON a.id = en.euser_id
AND a.class = 'E' 

Open in new window


That should be close.
0
PortletPaulfreelancerCommented:
NB: If you have "simplified" your query for this question please do NOT do this. Just provide the full unedited query.

1. Please run this

SELECT
      a.[Order]
    , a.id
    , c.custname
    , sa.signname
    , en.user_desc
FROM tbl_ABC a
      LEFT JOIN customer c
                  ON a.id = c.customer_id AND a.class = 'C'
      LEFT JOIN sign sa
                  ON a.id = sa.sign_id AND a.class = 'S'
      LEFT JOIN euser en
                  ON a.id = en.euser_id AND a.class = 'E'
WHERE a.id = 24

Open in new window

IF the output looks like this
| ORDER | ID |         CUSTNAME |      SIGNNAME |  USER_DESC |
|-------|----|------------------|---------------|------------|
|     x | 24 | AMERICAN COMPANY |        (null) |     (null) |
|     x | 24 |           (null) | EAGLE COMPANY |     (null) |
|     x | 24 |           (null) |        (null) | OMINSOURCE |

Open in new window

2. Run this

SELECT
      a.id
    , COUNT(DISTINCT a.class) a_class_distinct
    , COUNT(*)                num_rows
FROM tbl_ABC a
GROUP BY
      a.id

Open in new window

3. If the output of query (1) is one row per id, run this

SELECT
      a.[Order]
    , a.id
    , c.custname
    , sa.signname
    , en.user_desc
FROM tbl_ABC a
      LEFT JOIN dbo.o_a oma
                  ON a.[order] = oma.[order]
      LEFT JOIN customer c
                  ON a.id = c.customer_id
                        AND a.class = 'C'
      LEFT JOIN sign sa
                  ON a.id = sa.sign_id
                        AND a.class = 'S'
      LEFT JOIN euser en
                  ON a.id = en.euser_id
                        AND a.class = 'E'
;

Open in new window

4. Then Run this

SELECT
      a.id
    , COUNT(DISTINCT a.class) a_class_distinct
    , COUNT(*)                num_rows
FROM tbl_ABC a
      LEFT JOIN dbo.o_a oma
                  ON a.[order] = oma.[order]
GROUP BY
      a.id

Open in new window

0
Scott PletcherSenior DBACommented:
This code:
CASE WHEN a.code = 'C' THEN
           CASE WHEN ... END
END
will return a NULL value for that column anytime a.code is not = 'C'.  Since you don't want to see NULLs, easiest is to add a WHERE clause to check for a.code = 'C' and then you don't need that CASE condition.


Select
 [Order],
 id,
 name  =  get_first_matching_name.name
 From tbl_ABC a
 LEFT JOIN dbo.o_a a
 ON a.[order] = oma.[order]
 OUTER APPLY (
     SELECT TOP (1) name
     FROM (
         SELECT 1 AS sequence, sa.signname AS name
         FROM sign sa
         WHERE
             a.id = sa.sign_id AND
             a.class = 'S'
         UNION ALL
         SELECT 2 AS sequence, c.custname AS name
         FROM customer c
         WHERE
             a.customer_id = a.id AND
             a.class = 'C'
         UNION ALL
         SELECT 3 AS sequence, en.user_desc AS name
         FROM euser en
         WHERE
             a.id = en.euser_id AND
             a.class = 'E'
     ) AS get_names
     ORDER BY sequence
) AS get_first_matching_name
WHERE a.code = 'C'
0
MoreThanDoubledAuthor Commented:
ScottPletcher,

Thanks for pointing out the issue on the null values.  I almost have the statement working with the exception of my where clause.  As you can see I specify that if the coalesce statement is not null then return those records.  The problem hear is that their will be times where the coalesce statement IS NULL (meaning all values sa1, c1 and en1 will all be null), BUT I will still need to return that record set if all the values are null.  

So is there way to put a case statement in my where clause or another solution?  for example:
Where order = 1234
AND case when  COALESCE(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc))  is not null then COALESCE(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc))  is not null else COALESCE(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc))  IS NULL END


currently I have ..
Where order = 1234  
AND COALESCE(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc))  is not null
AND  COALESCE(RTRIM(sa2.cons_name), RTRIM(c2.cust_name), RTRIM(en2.enduser_desc)) is not null
AND  COALESCE(RTRIM(sa3.cons_name), RTRIM(c3.cust_name), RTRIM(en3.enduser_desc)) is not null
0
Scott PletcherSenior DBACommented:
Perhaps this?!:

Where order = 1234  
  -- there must be at least one non-NULL value, but the other two can be NULL
 AND (
COALESCE(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc))  is not null
 OR  COALESCE(RTRIM(sa2.cons_name), RTRIM(c2.cust_name), RTRIM(en2.enduser_desc)) is not null
 OR  COALESCE(RTRIM(sa3.cons_name), RTRIM(c3.cust_name), RTRIM(en3.enduser_desc)) is not null )


If all three could be NULL and you still want the row, I would think you could just remove that part of the WHERE completely?!
0
sdstuberCommented:
you don't even need multiple coalesce to check for not null, just chain them all in one call

and coalesce(RTRIM(sa1.cons_name), RTRIM(c1.cust_name), RTRIM(en1.enduser_desc), RTRIM(sa2.cons_name),RTRIM(c2.cust_name), RTRIM(en2.enduser_desc), RTRIM(sa3.cons_name), RTRIM(c3.cust_name), RTRIM(en3.enduser_desc)) is not null
0
MoreThanDoubledAuthor Commented:
Thanks for all the help guys.  I finally got it to work the way it should.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.