Solved

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

Posted on 2014-09-12
15
133 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
  • 4
  • 4
  • 3
  • +3
15 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 50 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 73

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
 
LVL 73

Expert Comment

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

Expert Comment

by:Kdo
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 250 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 73

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:Kdo
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 48

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:ScottPletcher
ScottPletcher earned 200 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:ScottPletcher
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 73

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now