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

    CARTESIAN PRODUCT problem

    968357
      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
          AlbertoFaenza
          Hi,

          Please read SQL and PL/SQL FAQ

          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
            Stew Ashton
            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
              968357
              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
                AlbertoFaenza
                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
                  968357
                  Thanks for your immediate reply and ofcourse for teaching me how to format my query.

                  thank you once again.

                  Your answered solved my problem.

                  With Regards

                  Vishal Agrawal
                  • 6. Re: CARTESIAN PRODUCT problem
                    AlbertoFaenza
                    Hi Vishal,

                    if your question is solved please mark this question as answered.

                    Regards.
                    Al