?
Solved

Case statement problem to show values without null in a row

Posted on 2011-02-16
10
Medium Priority
?
276 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 668 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 3

Assisted Solution

by:spandexandy
spandexandy earned 332 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 668 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 668 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 332 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 668 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

777 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