• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Case statement problem to show values without null in a row

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
PeteEngineer
Asked:
PeteEngineer
  • 2
  • 2
  • 2
  • +3
6 Solutions
 
Paul_Harris_FusionCommented:
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
 
PeteEngineerAuthor Commented:
rtt.us_code will never be nulll it will be either 's' or 'r'

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
spandexandyCommented:
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
 
Paul_Harris_FusionCommented:
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
 
John ClaesCommented:
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
 
PeteEngineerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
awking00Commented:
Can you post some sample data and what your desired results should be?
0
 
John ClaesCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now