Home » SQL & PL/SQL » SQL & PL/SQL » Query using joins
Query using joins [message #348581] Wed, 17 September 2008 05:39 Go to next message
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 #348584 is a reply to message #348581] Wed, 17 September 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Query using joins [message #348586 is a reply to message #348584] Wed, 17 September 2008 05:49 Go to previous message
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

Previous Topic: Max(average sale) for any 3 parallel months
Next Topic: Converting text to ascii and vice versa at application level
Goto Forum:
  


Current Time: Wed Dec 07 02:59:49 CST 2016

Total time taken to generate the page: 0.08353 seconds