Home » SQL & PL/SQL » SQL & PL/SQL » Other way of doing query
Other way of doing query [message #342151] Thu, 21 August 2008 09:40 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
we have been using this type of query for our reporting tool, just want to ask if there's other workaround because this sql flow is taking time because of i think repetitive execution of the main queries, here's a sample layout of the query:


select columns..
       'SOURCE1' as SRC
from source1
union all
select columns
       'SOURCE2' as SRC
from source2
union all
select source1.columns
       (source1.columns - source2.columns),
       'Variance' as SRC
from (select columns..       
      from source1) s1
     ,(select columns
        from source2) s2
where s1.columns = s2.columns




We do this kind of query so that the reporting tool would display it in pivot type like:


[col1(price)]         [SRC]
[col2(product)]       
                      SOURCE1          SOURCE2     Variance
PRODUCT1              2.45             2.30        15



Hope I've explained it clearly. Thanks again.

Re: Other way of doing query [message #342169 is a reply to message #342151] Thu, 21 August 2008 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you post the actual query and the actual you're trying to solve we can help more.

Regards
Michel
Re: Other way of doing query [message #342185 is a reply to message #342151] Thu, 21 August 2008 11:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Hope I've explained it clearly. Thanks again.
Except that the table columns are totally undistinguishable. I wonder, why you did not simply join the tables, something as
SELECT source1.columns.. SOURCE1, source2.columns SOURCE2, (source1.columns - source2.columns) Variance
FROM source1, source2
WHERE s1.columns = s2.columns;
(I tried to formulate it at least as clear as your post).
Re: Other way of doing query [message #342254 is a reply to message #342169] Thu, 21 August 2008 16:03 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Thanks for the replies, here's a sample data and a screenshot of the result of it in the tool:



create table t1
(
    commodity varchar2(10),
    p_name    varchar2(100),
    cc_code   varchar2(10),
    cob_dt    date,
    price     number
) 


insert into t1 values('COAL', 'ICE RDAM COAL', 'ATW', to_date('13-aug-08', 'dd-mon-yy'), 5.60);

insert into t1 values('COAL', 'ICE RICH COAL', 'AFR', to_date('13-aug-08', 'dd-mon-yy'), 5);

insert into t1 values('COAL', 'ICE APPL COAL', 'QL', to_date('13-aug-08', 'dd-mon-yy'), 4.45);

insert into t1 values('COAL', 'NYM CSX COAL', 'QP', to_date('13-aug-08', 'dd-mon-yy'), 2.89);

insert into t1
select commodity, p_name, cc_code, cob_dt + 1, price + rownum from t1

insert into t1 values('COAL', 'NOT CSX COAL', 'QX', to_date('14-aug-08', 'dd-mon-yy'), 1.9);


create procedure p(cur_out out sys_refcursor) as
begin
    open cur_out for 
        select commodity, 
       p_name, 
       cc_code,
       cob_dt,
       price,
       to_char(cob_dt, 'MM/DD/YYYY') src
from t1 src1
where cob_dt = to_date('13-aug-08', 'dd-mon-yy')
union all
select commodity, 
       p_name, 
       cc_code,
       cob_dt,
       price,
       to_char(cob_dt, 'MM/DD/YYYY') src
from t1 src1
where cob_dt = to_date('14-aug-08', 'dd-mon-yy')
union all
select sr1.commodity,
       sr1.p_name,
       sr1.cc_code,
       sr1.cob_dt,
       sr2.price - sr1.price price,
       'Variance' src
from (select commodity, 
       p_name, 
       cc_code,
       cob_dt,
       price
from t1 src1
where cob_dt = to_date('13-aug-08', 'dd-mon-yy')) sr1,
     (select commodity, 
       p_name, 
       cc_code,
       cob_dt,
       price
from t1 src1
where cob_dt = to_date('14-aug-08', 'dd-mon-yy')) sr2
where sr1.commodity = sr2.commodity
  and sr1.p_name = sr2.p_name
  and sr1.cc_code = sr2.cc_code;
end;



hope this adds explanation, thanks.

flyboy, thanks for the info, but i think i tried that approach before the SRC columns wont work the way it is displayed in the jpeg i provided, also i wont be able to show all records and also if the user added the COB_DT on the left of commodity.
  • Attachment: img1.JPG
    (Size: 28.25KB, Downloaded 80 times)

[Updated on: Thu, 21 August 2008 16:07]

Report message to a moderator

Re: Other way of doing query [message #342346 is a reply to message #342151] Fri, 22 August 2008 01:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
firstly, thank you for testcase.
I do not know your reporting tool and the need of pivoting data there (as the selected dates in WHERE conditions are constant), so I do not see any reason not to make pivoting in SQL query. As the variance is simple subtraction of two columns, you will count it as
SELECT commodity, p_name, cc_code,
  <pivot expression for 13-aug-08> "08/13/2008",
  <pivot expression for 14-aug-08> "08/14/2008",
  <pivot expression for 14-aug-08> - <pivot expression for 13-aug-08> "Variance"
...
(note you cannot use column aliases in the subtraction expression).

If you do not know, how to pivot in SQL, here is a link which shows it: http://www.orafaq.com/wiki/PIVOT (note the 11g concept).

> but i think i tried that approach before the SRC columns wont work the way it is displayed in the jpeg i provided

It depends on the uniqueness of (COMMODITY, P_NAME, CC_CODE, COB_DT); as you self-joined t1 on these columns; I supposed they are unique; otherwise you should aggregate. However the classical PIVOT query seems better to me (only one pass through table).

> also i wont be able to show all records and also if the user added the COB_DT on the left of commodity.

Using (full) outer join instead of inner join shall pick these rows.
Re: Other way of doing query [message #342466 is a reply to message #342346] Fri, 22 August 2008 07:39 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
thanks! I'll try this one. Not sure on the pivoting, as the two sources may come from two different queries/source tables, as I saw on the link you gave it uses one base table. But I'll see what would be the effect, thanks again!
Previous Topic: Default Foreign key setup
Next Topic: Email PL/SQL exception
Goto Forum:
  


Current Time: Sun Dec 04 18:44:15 CST 2016

Total time taken to generate the page: 0.07401 seconds