6 Replies Latest reply on Dec 5, 2012 11:06 AM by AlbertoFaenza

CARTESIAN PRODUCT problem

hello,

i m trying to find the difference for calculating the DIFFENCE IN STOCK FOR OPC,PPC,CLINKER BY USING FOLLOWING SQL-QUERY

-----------------

SELECT TO_CHAR(a.dateofmtrl,'YYYY') YEAR,
SUM(a.rm1) - SUM(b.rm1) - SUM(c.rm1) OPC,
SUM(a.rm2) - SUM(b.rm2) - sum(c.rm2) PPC,
SUM(a.rm3) - SUM(b.rm3) - sum(c.rm3) CLINKER
FROM rawmtrl_graph a
INNER JOIN rawmtrl_graph b ON TO_CHAR(a.dateofmtrl,'YYYY') =TO_CHAR(B.dateofmtrl,'YYYY')
INNER JOIN rawmtrl_graph c ON TO_CHAR(a.dateofmtrl,'YYYY') =TO_CHAR(C.dateofmtrl,'YYYY')
WHERE a.mtrl_flag='P'
AND b.mtrl_flag='D'
AND c.mtrl_flag='CS'
GROUP BY TO_CHAR(a.dateofmtrl,'YYYY')

-------------------------------------
BUT IT IS GIVING CARTESIAN PRODUCT...
HOW TO MODIFY THE QUERY FOR FINDING EXACT FIGURE??????

THANKS IN ADVENCE FOR ANY HELP.....
• 1. Re: CARTESIAN PRODUCT problem
Hi,

If you have a performance issue have a look at SQL and PL/SQL FAQ

Additionally when you put some code please enclose it between two lines starting with {noformat}
``````{noformat}
i.e.:
{noformat}``````
{noformat}
SELECT ...
{noformat}
``````{noformat}

Post sample data (create table and insert statement).

Regards.
Al

Edited by: Alberto Faenza on Dec 5, 2012 9:56 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ``````
• 2. Re: CARTESIAN PRODUCT problem
Alberto's advice is good. I suggest you follow it from now on.

Just looking at your query, you are joining and then summing. You need to sum in subqueries, then join.

Don't use TO_CHAR in the JOIN and the GROUP BY: TO_CHAR should only be used to format selected columns for display.

Use trunc(<your_date>, 'YY').

Once you follow Alberto's advice, we can actually show you how to do this.
• 3. Re: CARTESIAN PRODUCT problem
NOW I M TRYING TO IMPROVE THE FORMAT OF MY QUERY AS DESIRED BY U GUYS ..

SO MY PROBLEM IS

I HAVE CREATED A TABLE "RAWMTRL_GRAPH" LIKE

SQL> CREATE TABLE RAWMTRLGRAPH (
2 RM1 NUMBER (6,2),
3 RM2 NUMBER (6,2),
4 RM3 NUMBER (6,2),
5 DATEOFMTRL DATE ,
6 MTRL_FLAG CHAR (3),
7 PRIMARY KEY ( DATEOFMTRL, MTRL_FLAG ) ) ;

Table created.

SQL> INSERT INTO RAWMTRLGRAPH SELECT * FROM RAWMTRL_GRAPH;

6 rows created.

SQL> INSERT INTO RAWMTRLGRAPH VALUES (50,54,55,TO_DATE('05/06/2012','DD/MM/YYYY'),'P');

1 row created.

SQL> INSERT INTO RAWMTRLGRAPH VALUES (50,54,55,TO_DATE('06/06/2011','DD/MM/YYYY'),'P');

1 row created.

SQL> INSERT INTO RAWMTRLGRAPH VALUES (50,54,55,TO_DATE('07/08/2012','DD/MM/YYYY'),'CS');

1 row created.

SQL> SELECT * FROM RAWMTRLGRAPH;

RM1 RM2 RM3 DATEOFMTR MTR
-------------
4 4 1 03-MAR-12 CS
50 14 15 04-APR-12 P
50 14 15 04-MAY-12 D
50 14 15 05-JUN-12 CS
10 20 10 01-JAN-12 P
5 5 5 01-FEB-12 D
50 54 55 05-JUN-12 P
50 54 55 06-JUN-11 P
50 54 55 07-AUG-12 CS

9 rows selected.

NOW I M TRYING TO FIND THE DIFFERENCE IN STOCK YEAR WISE FOR ALL YEAR LIKE 2011,2012 ...SO ON .I M USING FOLLOWING QUERY...
-------------
``````SELECT TO_CHAR(a.dateofmtrl,'YYYY') YEAR,
SUM(a.rm1) - SUM(b.rm1) - SUM(c.rm1) OPC,
SUM(a.rm2) - SUM(b.rm2) - sum(c.rm2) PPC,
SUM(a.rm3) - SUM(b.rm3) - sum(c.rm3) CLINKER
FROM rawmtrlgraph a
INNER JOIN rawmtrlgraph b ON TO_CHAR(a.dateofmtrl,'YYYY') =TO_CHAR(B.dateofmtrl,'YYYY')
INNER JOIN rawmtrlgraph c ON TO_CHAR(a.dateofmtrl,'YYYY') =TO_CHAR(C.dateofmtrl,'YYYY')
WHERE a.mtrl_flag='P'
AND b.mtrl_flag='D'
AND c.mtrl_flag='CS'
GROUP BY TO_CHAR(a.dateofmtrl,'YYYY')``````
------------
BUT IT IS GIVING CARTESIAN PRODUCT AND ALSO NOT CALULATING DATA FOR THE YEAR 2011
HOW TO MODIFY THE QUERY FOR FINDING EXACT FIGURE??????

it is giving output as below

YEAR OPC PPC CLINKER
--------------------
2012 -459 -75 -126

my aspected output is

YEAR OPC PPC CLINKER
--------------
2011 50 54 55
2012 -49 -3 -11

THANKS IN ADVENCE FOR ANY HELP.....
• 4. Re: CARTESIAN PRODUCT problem
Hi,

please always put your code between two lines starting with {noformat}
``````{noformat}.

Additionally when you put insert statement put them entirely and not copying from one table we don't have.

Here the way to get the result you want:``````
SELECT EXTRACT(YEAR FROM dateofmtrl) yr
, SUM(CASE mtrl_flag WHEN 'P' THEN rm1 ELSE -rm1 END) AS opc
, SUM(CASE mtrl_flag WHEN 'P' THEN rm2 ELSE -rm2 END) AS ppc
, SUM(CASE mtrl_flag WHEN 'P' THEN rm3 ELSE -rm3 END) AS clinker
FROM rawmtrlgraph
GROUP BY EXTRACT(YEAR FROM dateofmtrl);

YR OPC PPC CLINKER
---------- ---------- ---------- ----------
2011 50 54 55
2012 -49 -3 -11
``````Regards.
Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ``````
• 5. Re: CARTESIAN PRODUCT problem
Thanks for your immediate reply and ofcourse for teaching me how to format my query.

thank you once again.