I have my code:
SELECT Y.shipdate[Sent date],
Rate_ = 'price' + Z.Which,
Bill_ = 'bill' + Z.Which,
Rate = CASE Z.Which
WHEN '1' THEN price1
WHEN '2' THEN price2
WHEN '3' THEN price3
WHEN '4' THEN price4
WHEN '5' THEN price5
WHEN '6' THEN price6 END,
Bill = CASE Z.Which
WHEN '1' THEN bill1
WHEN '2' THEN bill2
WHEN '3' THEN bill3
WHEN '4' THEN bill4
WHEN '5' THEN bill5
WHEN '6' THEN billr6 END,
Y.duedate[Due Date], Y.recvdate[Received]
FROM tra Y
CROSS JOIN (SELECT '1' union ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6') Z (Which)
WHERE name = 'test'
with output:
Sent Date Rate_ Bill_ Rate Bill Due Date Received
2015-12-22 00:00:00.000 price1 bill1 0.55 300.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
2015-12-22 00:00:00.000 price2 bill2 0.04 2.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
2015-12-22 00:00:00.000 price3 bill3 0.07 43.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
2015-12-22 00:00:00.000 price4 bill4 0.00 0.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
2015-12-22 00:00:00.000 price5 bill5 0.00 0.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
2015-12-22 00:00:00.000 price6 bill6 0.00 0.00 2015-12-16 00:00:00.000 2015-12-01 00:00:00.000
I want an output that will only display rows with value in price and bill. Based on my sample, there should only be 3 rows in the output. thanks.
try this
where name = 'test'
and CASE Z.Which
WHEN '1' THEN price1
WHEN '2' THEN price2
WHEN '3' THEN price3
WHEN '4' THEN price4
WHEN '5' THEN price5
WHEN '6' THEN price6
END <> 0.00
Cross join produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. but in you case you are using filters to eliminate rows. In such a case better use a Natural Join