Query using joins [message #348581] |
Wed, 17 September 2008 05:39  |
basmraj
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi,
my tables structure looks:
Column names:
rhead
bu
sub_bu
report_order
sub_bu_order
q1py
My query is:
select Rhead,bu,Sub_bu,report_order,sub_bu_order,q1py,case
when sub_bu='ISS' then (select q1py from tsg a where a.sub_bu='ESS' and a.rhead=b.rhead)
when sub_bu='TS' then (select q1py from tsg a where a.sub_bu='Services' and a.rhead=b.rhead)
when sub_bu='SW' then (select q1py from tsg a where a.sub_bu='SW' and a.rhead=b.rhead)
else NULL end as q1py_bu,
case
when sub_bu='ISS' then (select q1py from tsg a where a.sub_bu='TSG' and a.rhead=b.rhead)
when sub_bu like 'R&D' and rhead in ('Region owned opex','% of revenue','Region opex','Region opex%') then
(select q1py from tsg a where a.sub_bu like 'TSG' and a.rhead=b.rhead)
else NULL end as q1py_tot from tsg b where report_order=1
Result:
rhead bu sub_bu report_order sub_bu_order q1py q1py_bu q1py_tot
orders ESS ISS 1 1 1162 1778.4 2953.9
here i am using the sub query to produce the result. I need to give the same result by using JOINS.
So any one help me, by using JOINS instead of SUB QUERY and produce the same result
Thanx in advance
|
|
|
|
Re: Query using joins [message #348586 is a reply to message #348584] |
Wed, 17 September 2008 05:49  |
basmraj
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi,
my tables structure looks:
Column names:
rhead
bu
sub_bu
report_order
sub_bu_order
q1py
My query is:
SELECT rHead,
bu,
Sub_bu,
Report_Order,
Sub_bu_Order,
q1py,
CASE
WHEN Sub_bu = 'ISS' THEN (SELECT q1py
FROM tsg a
WHERE a.Sub_bu = 'ESS'
AND a.rHead = b.rHead)
WHEN Sub_bu = 'TS' THEN (SELECT q1py
FROM tsg a
WHERE a.Sub_bu = 'Services'
AND a.rHead = b.rHead)
WHEN Sub_bu = 'SW' THEN (SELECT q1py
FROM tsg a
WHERE a.Sub_bu = 'SW'
AND a.rHead = b.rHead)
ELSE NULL
END AS q1py_bu,
CASE
WHEN Sub_bu = 'ISS' THEN (SELECT q1py
FROM tsg a
WHERE a.Sub_bu = 'TSG'
AND a.rHead = b.rHead)
WHEN Sub_bu LIKE 'R&D'
AND rHead IN ('Region owned opex',
'% of revenue',
'Region opex',
'Region opex%') THEN (SELECT q1py
FROM tsg a
WHERE a.Sub_bu LIKE 'TSG'
AND a.rHead = b.rHead)
ELSE NULL
END AS q1py_Tot
FROM tsg b
WHERE Report_Order = 1
Result:
rhead bu sub_bu report_order sub_bu_order q1py q1py_bu q1py_tot
orders ESS ISS 1 1 1162 1778.4 2953.9
here i am using the sub query to produce the result. I need to give the same result by using JOINS.
So any one help me, by using JOINS instead of SUB QUERY and produce the same result
Thanx in advance
[Edit MC: add code tags]
[Updated on: Wed, 17 September 2008 06:20] by Moderator Report message to a moderator
|
|
|