?
Solved

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

Posted on 2014-09-12
15
Medium Priority
?
139 Views
Last Modified: 2014-09-17
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
0
Comment
Question by:MoreThanDoubled
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +3
15 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 200 total points
ID: 40319871
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40319880
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
 

Author Comment

by:MoreThanDoubled
ID: 40319955
kdo, when using your logic I still don't get a single row return
0
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40319960
does mine do what you're looking for?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 40319972
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
 
LVL 13

Accepted Solution

by:
Russell Fox earned 1000 total points
ID: 40320012
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
 

Author Comment

by:MoreThanDoubled
ID: 40320017
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40320024
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 40320036
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40320490
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 40323454
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
 

Author Comment

by:MoreThanDoubled
ID: 40326453
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40326472
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40326560
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
 

Author Comment

by:MoreThanDoubled
ID: 40328668
Thanks for all the help guys.  I finally got it to work the way it should.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question