Solved

Case statement problem to show values without null in a row

Posted on 2011-02-16
10
274 Views
Last Modified: 2012-05-11
select  CASE WHEN ( rtt.us_code = 's' )
                  THEN rtt.name
                  ELSE '' END AS input_tax_name,
             CASE WHEN ( rtt2.us_code = 'r')
                  THEN rtt2.name
                  ELSE '' END AS output_tax_name,
             CASE WHEN ( rtt.us_code = 's' )
                  THEN rtt.acc_id
                  ELSE 0 END AS input_tax_rate,
             CASE WHEN ( rtt2.us_code = 'r')
                  THEN rtt2.acc_id
                  ELSE 0 END AS output_tax_rate
from supplier_item si
JOIN ret_tx_type  rtt
ON si.ret_tx_type_id = rtt.ret_tx_type_id
JOIN ret_tx_type  rtt2
ON si.ret_tx_type_id = rtt2.ret_tx_type_id
 where si.ret_tx_type_id is not null

I am trying to display input and output tax in the same row for a report depends on us_code is 'r' and 's'.
But i am getting the results like

if input tax is there ...output tax column will be null
if output tax is there ...input tax column will be null

i am want to get the both in same row and no need to make null in any rows

please help
0
Comment
Question by:PeteEngineer
[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
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 167 total points
ID: 34906022
I think this is because comparisons like:

 rtt.us_code = 's'

wil return NULL  if  rtt.us_code is null.

Try replacing them with
COALESCE ( rtt.us_code, 'NOT PRESENT') = 's'

0
 
LVL 8

Author Comment

by:PeteEngineer
ID: 34906065
rtt.us_code will never be nulll it will be either 's' or 'r'

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34906072
you want to do a group by:
select  max(CASE WHEN ( rtt.us_code = 's' )
                  THEN rtt.name
                  ELSE '' END ) AS input_tax_name,
         max(CASE WHEN ( rtt2.us_code = 'r')
                  THEN rtt2.name
                  ELSE '' END ) AS output_tax_name,
         max( CASE WHEN ( rtt.us_code = 's' )
                  THEN rtt.acc_id
                  ELSE 0 END ) AS input_tax_rate,
         max(  CASE WHEN ( rtt2.us_code = 'r')
                  THEN rtt2.acc_id
                  ELSE 0 END ) AS output_tax_rate
from supplier_item si
JOIN ret_tx_type  rtt
ON si.ret_tx_type_id = rtt.ret_tx_type_id
JOIN ret_tx_type  rtt2
ON si.ret_tx_type_id = rtt2.ret_tx_type_id
 where si.ret_tx_type_id is not null

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 3

Assisted Solution

by:spandexandy
spandexandy earned 83 total points
ID: 34906083
How about not using a CASE statement. Something like this:

SELECT
supplier_item.ret_tx_type_id,
ISNULL(supplier_outputtax.name,'') as output_tax_name,
ISNULL(supplier_outputtax.acc_id,0) as output_tax_rate,
ISNULL(supplier_inputtax.name,'') as input_tax_name,
ISNULL(supplier_inputtax.acc_id,0) as input_tax_rate
FROM
supplier_item
LEFT JOIN
(
SELECT
acc_id,
name
FROM
ret_tx_type
WHERE
us_code='r'
) as supplier_outputtax ON supplier_item.ret_tx_type_id=supplier_outputtax.ret_tx_type_id
LEFT JOIN
(
SELECT
acc_id,
name
FROM
ret_tx_type
WHERE
us_code='s'
) as supplier_inputtax ON supplier_item.ret_tx_type_id=supplier_outputtax.ret_tx_type_id
WHERE
supplier_item.ret_tx_type_id IS NOT NULL


Cheers, Andrew.
0
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 167 total points
ID: 34906172
Could you post the results of:

Select *
from supplier_item si
JOIN ret_tx_type  rtt
ON si.ret_tx_type_id = rtt.ret_tx_type_id
JOIN ret_tx_type  rtt2
ON si.ret_tx_type_id = rtt2.ret_tx_type_id
 where si.ret_tx_type_id is not null
0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 167 total points
ID: 34906462
I thinck you don't need the case clause but a clear view upon the issue

You want to show the intax name, intax rate , outtax name and outtax rate


select  intax.name AS input_tax_name,
            outtax.name AS output_tax_name,
            intax.acc_id AS input_tax_rate,
            outtax..acc_id AS output_tax_rate
from supplier_item si
inner JOIN ret_tx_type  intax ON si.ret_tx_type_id = intax.ret_tx_type_id and intax.us_code = 's'
inner JOIN ret_tx_type  outtax ON si.ret_tx_type_id = outtax.ret_tx_type_id and intax.us_code = 'r'
where si.ret_tx_type_id is not null

0
 
LVL 8

Author Comment

by:PeteEngineer
ID: 34906668
i think  this query working :



SELECT
  MAX(CASE rtt.us_code WHEN 's' THEN rtt.name END) AS input_tax_name,
  MAX(CASE rtt.us_code WHEN 'r' THEN rtt.name END) AS output_tax_name,
  MAX(CASE rtt.us_code WHEN 's' THEN rtt.acc_id END) AS input_tax_rate,
  MAX(CASE rtt.us_code WHEN 'r' THEN rtt.acc_id END) AS output_tax_rate
FROM supplier_item si
  INNER JOIN ret_tx_type rtt
    ON si.ret_tx_type_id = rtt.ret_tx_type_id
GROUP BY ??? /* here you should supply a column,
                presumably in 'si', that is common
                to both of the related 'r'- and
                's'-tax records */

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 83 total points
ID: 34907274
if you don't need an additional column returned, you don't need a GROUP BY ... but you can, of course:
SELECT si.item_name,
  MAX(CASE rtt.us_code WHEN 's' THEN rtt.name END) AS input_tax_name,
  MAX(CASE rtt.us_code WHEN 'r' THEN rtt.name END) AS output_tax_name,
  MAX(CASE rtt.us_code WHEN 's' THEN rtt.acc_id END) AS input_tax_rate,
  MAX(CASE rtt.us_code WHEN 'r' THEN rtt.acc_id END) AS output_tax_rate
FROM supplier_item si
  INNER JOIN ret_tx_type rtt
    ON si.ret_tx_type_id = rtt.ret_tx_type_id
GROUP BY si.item_name

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 34909732
Can you post some sample data and what your desired results should be?
0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 167 total points
ID: 34914479
select  intax.name AS input_tax_name,
            outtax.name AS output_tax_name,
            intax.acc_id AS input_tax_rate,
            outtax..acc_id AS output_tax_rate
from supplier_item si
inner JOIN ret_tx_type  intax ON si.ret_tx_type_id = intax.ret_tx_type_id and intax.us_code = 's'
inner JOIN ret_tx_type  outtax ON si.ret_tx_type_id = outtax.ret_tx_type_id and intax.us_code = 'r'
where si.ret_tx_type_id is not null

Example Tables :  
supplier_item 

ret_tx_type_id 
1
2
3
4
5

 ret_tx_type  

ret_tx_type_id   us_code   Name     Acc_Id
1		s	IN1	1
1		r	OUT1	1
2		s	IN2	2
2		r	OUT2	2
3		s	IN3	3
3		r	OUT3	3
4		s	IN4	4
4		r	OUT4	4
5		s	IN5	5
5		r	OUT5	5
6		s	IN6	6
6		r	OUT6	6

Open in new window

Example Code :  
CREATE TABLE #ret_tx_type  
(
	ret_tx_type_id int,
	us_code char(1), 
	[name]  varchar(25), 
	acc_id int
) 
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (1,'s','IN1','1')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (1,'r','OUT1','1')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (2,'s','IN2','2')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (2,'r','OUT2','2')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (3,'s','IN3','3')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (3,'r','OUT3','3')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (4,'s','IN4','4')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (4,'r','OUT4','4')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (5,'s','IN5','5')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (5,'r','OUT5','5')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (6,'s','IN6','6')
insert into #ret_tx_type (ret_tx_type_id, us_code,[name],acc_id) values (6,'r','OUT6','6')


CREATE TABLE #supplier_item  
(
	ret_tx_type_id int
) 
insert into #supplier_item (ret_tx_type_id) values (1)
insert into #supplier_item (ret_tx_type_id) values (2)
insert into #supplier_item (ret_tx_type_id) values (3)
insert into #supplier_item (ret_tx_type_id) values (4)
insert into #supplier_item (ret_tx_type_id) values (5)



select      intax.name AS input_tax_name,
            outtax.name AS output_tax_name,
            intax.acc_id AS input_tax_rate,
            outtax.acc_id AS output_tax_rate
from #supplier_item si
JOIN #ret_tx_type  intax ON si.ret_tx_type_id = intax.ret_tx_type_id and intax.us_code = 's' 
JOIN #ret_tx_type  outtax ON si.ret_tx_type_id = outtax.ret_tx_type_id and outtax.us_code = 'r' 
where si.ret_tx_type_id is not null 


drop table #supplier_item
drop table #ret_tx_type

Open in new window


Example Result :  
IN1	OUT1	1	1
IN2	OUT2	2	2
IN3	OUT3	3	3
IN4	OUT4	4	4
IN5	OUT5	5	5

Open in new window

0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

729 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