Link to home
Start Free TrialLog in
Avatar of PeteEngineer
PeteEngineerFlag for India

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Paul_Harris_Fusion
Paul_Harris_Fusion
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeteEngineer

ASKER

rtt.us_code will never be nulll it will be either 's' or 'r'

Avatar of Guy Hengel [angelIII / a3]
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you post some sample data and what your desired results should be?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial