Solved

Case statement problem to show values without null in a row

Posted on 2011-02-16
10
271 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

809 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