Solved

Case statement problem to show values without null in a row

Posted on 2011-02-16
10
270 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
  • 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 142

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

815 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

13 Experts available now in Live!

Get 1:1 Help Now