Home » SQL & PL/SQL » SQL & PL/SQL » SQL Question (Oracle 9.2.0.8.0)
SQL Question [message #388190] Mon, 23 February 2009 14:24 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I am trying to figure out why I don't get same results from my 2 SQLs below. The sales_detail table is a big table and does not have a primary key. Please don't ask why. Thanks in advance for any insight.

******************************SQL1************************************************************************************************

SELECT
sum(shipped_qty) total_qty
from gdw_views.sales_detail i,
gdw_views.od_calendar j,
where i.account_id <> 0
and i.shipped_qty <> 0
and i.sales_type_cd in (1,2)
and i.business_unit_cd in ('01','06','10')
and i.vendor_id in ( 500,5820,2925,2014 )
and i.business_day_dt = j.fiscal_date_id
and cast(j.dt as format 'YYYYMMDD ' ) = '20090128'


36254

******************************************************************************************SQL 2***********************************

select
sum(a.total_qty)
from (SELECT
item_id,
order_id,
sum(shipped_qty) total_qty,
i.business_day_dt
from gdw_views.sales_detail i,
gdw_views.od_calendar j

where i.account_id <> 0
and i.shipped_qty <> 0
and i.sales_type_cd in (1,2)
and i.business_unit_cd in ('01','06','10')
and i.vendor_id in ( 500,5820,2925,2014 )
and i.business_day_dt = j.fiscal_date_id
and cast(j.dt as format 'YYYYMMDD ' ) = '20090128'
group by order_id, item_id, i.business_day_dt
) a,
gdw_views.sales_detail b ,
gdw_views.od_calendar j,
where a.order_id = b.order_id
and a.item_id = b.item_id
and a.business_day_dt = b.business_day_dt
and i.vendor_id in ( 500,5820,2925,2014 )
and b.business_day_dt = j.fiscal_date_id
and cast(j.dt as format 'YYYYMMDD ' ) = '20090128'
and b.account_id <> 0
and b.shipped_qty <> 0
and b.sales_type_cd in (1,2)
and b.business_unit_cd in ('01','06','10')



36266
Re: SQL Question [message #388224 is a reply to message #388190] Mon, 23 February 2009 22:57 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
what you are looking for. Are you sure that both these queries are running.
Re: SQL Question [message #388245 is a reply to message #388190] Tue, 24 February 2009 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not Oracle SQL code:
SQL> select cast(sysdate as format 'YYYYMMDD') from dual;
select cast(sysdate as format 'YYYYMMDD') from dual
                              *
ERROR at line 1:
ORA-00907: missing right parenthesis

Note this is an Oracle forum.

Regards
Michel
Re: SQL Question [message #388259 is a reply to message #388190] Tue, 24 February 2009 01:10 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@chintu00,

Well,I didn't go through the details of your query but it looks like a case of Cartesian Product. I have tried to illustrate your problem with a simple example below:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 24 12:35:34 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set feedback on;
SQL> SELECT *  FROM test_tab;

COL_1      COL_2           COL_3
---------- ---------- ----------
A          B                   5
A          B                   5
A          C                  10

3 rows selected.

SQL> SELECT   a1.col_1, a1.col_2, SUM (a1.col_3) tot_qty
  2              FROM test_tab a1
  3          GROUP BY a1.col_1, a1.col_2;

COL_1      COL_2         TOT_QTY
---------- ---------- ----------
A          C                  10
A          B                  10

2 rows selected.

SQL> SELECT SUM (b.tot_qty)
  2    FROM (SELECT   a1.col_1, a1.col_2, SUM (a1.col_3) tot_qty
  3              FROM test_tab a1
  4          GROUP BY a1.col_1, a1.col_2) b,
  5         test_tab t1
  6   WHERE b.col_1 = t1.col_1 AND b.col_2 = t1.col_2;

SUM(B.TOT_QTY)
--------------
            30  <-- Wrong Result Due to Cartesian Product

1 row selected.

SQL> SELECT SUM (b.tot_qty), SUM (t1.col_3)
  2    FROM (SELECT   a1.col_1, a1.col_2, SUM (a1.col_3) tot_qty
  3              FROM test_tab a1
  4          GROUP BY a1.col_1, a1.col_2) b,
  5         test_tab t1
  6   WHERE b.col_1 = t1.col_1 AND b.col_2 = t1.col_2;

SUM(B.TOT_QTY) SUM(T1.COL_3)
-------------- -------------
            30            20 <-- SUM(t1.col_3) gives correct output

1 row selected.
SQL> 


You are joining a grouped resultset of a tables (gdw_views.sales_detail and gdw_views.od_calendar) with the same tables again. What is the purpose for that? Hope you can understand what is happening with the examples I gave.

Hope this helps,

Please follow the rules mentioned in OraFAQ Forum Guide.

Regards,
Jo
Re: SQL Question [message #388384 is a reply to message #388259] Tue, 24 February 2009 09:06 Go to previous message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thanks joicejohn,

I appreciate your help that is where the error was.
With the from SQL I plan to populate the Temporary table and use that in my main SQL. I am using sales_detail again because I require more columns in my resultset from few other tables joining the sales detail table.
Previous Topic: use substr/instr [Merged]
Next Topic: Creation of MV using dblink
Goto Forum:
  


Current Time: Fri Dec 09 05:57:01 CST 2016

Total time taken to generate the page: 0.07452 seconds