1 2 Previous Next 27 Replies Latest reply: Dec 30, 2012 6:01 AM by 978776 Go to original post RSS
      • 15. Re: Add a table in this query?
        978776
        Thanks again!

        This is what I want. But it does not work. I get the "missing SELECT keyword". Do not know where ...
        SELECT       material
        ,       NVL (SUM (sales_quantity), 0)          AS total_sales_quantity
        ,       NVL (SUM (con_quantity),   0)          AS total_con_quantity
        ,       TO_CHAR ( a_period_start
                      , MIN (date_format)
                    )               AS period
        FROM       (
                      WITH  params  AS
        (
             SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
             ,     TO_DATE ('2012-06-30', 'yyyy-mm-dd') AS last_posting_date
             ,     'MONTH'                                AS week_or_month
             FROM     dual
        )
        ,     periods_vw     AS
        (
             SELECT     CASE
                      WHEN  week_or_month = 'WEEK'
                      THEN  TRUNC (first_posting_date, 'IW') 
                             + (7 * (LEVEL - 1))
                      ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                       , LEVEL - 1
                                 )
                  END                    AS a_period_start
             ,     CASE
                      WHEN  week_or_month = 'WEEK'
                      THEN  TRUNC (first_posting_date, 'IW') 
                             + (7 * LEVEL)
                      ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                       , LEVEL
                                 )
                  END                    AS next_period_start
             ,     CASE
                      WHEN  week_or_month = 'WEEK'
                      THEN  'IYYYIW'
                      ELSE  'YYYYMM'
                  END                    AS date_format
             FROM    params
             CONNECT BY     LEVEL <= 1 + CASE
                                     WHEN  week_or_month = 'WEEK'
                                  THEN  ( ( TRUNC (last_posting_date,  'IW')
                                                           - TRUNC (first_posting_date, 'IW')
                                              )
                                             / 7
                                             )
                                  ELSE  MONTHS_BETWEEN ( TRUNC (last_posting_date,  'MONTH')
                                                        , TRUNC (first_posting_date, 'MONTH')
                                                        )
                                       END
        )
        ,     agg     AS
        (
             SELECT       b.material
             ,       b.sales_quantity
             ,       0          AS con_quantity
             ,       pb.a_period_start
             ,       pb.date_format
             FROM            periods_vw  pb
             LEFT OUTER JOIN       tblbill     b  PARTITION BY (b.material)
                                          ON  b.posting_date >= pb.a_period_start
                                           AND b.posting_date <  pb.next_period_start
             WHERE     b.material   IN ('Label1', 'Label2')
            UNION ALL
             SELECT       c.mtrl
             ,       0
             ,       c.con_quantity
             ,       pc.a_period_start
             ,       pc.date_format
             FROM            periods_vw  pc
             LEFT OUTER JOIN       tblcon      c  PARTITION BY (c.mtrl)
                                          ON  c.posting_date >= pc.a_period_start
                                           AND c.posting_date <  pc.next_period_start
             WHERE     c.mtrl     IN ('Label1', 'Label2')
        )
                )
        GROUP BY  material
        ,            a_period_start
        ORDER BY  material
        ,            a_period_start
        ;
        • 16. Re: Add a table in this query?
          Frank Kulash
          Hi,

          The WITH clause is a way of defining a sub-query before a main query. The syntax is
          WITH   sub_query_1  AS
          (
              SELECT  ...
          )
          ,       sub_query_2  AS   -- If needed
          (
              SELECT  ...
          )
          ...
          ,       sub_query_N  AS   -- If needed
          (
              SELECT  ...
          )
          SELECT  ...   -- This is the main query
          ;
          When the entire thing is done as an in-line view (as in your case), it keeps the same structure.
          You don't have a main query. The last sub-query (agg) should be the main query.

          Sorry, I don't have time to do the whole thing and test it now. Maybe later.
          • 17. Re: Add a table in this query?
            978776
            Ok, I understand. I'm waiting for your test. I guess you know what I mean.
            • 18. Re: Add a table in this query?
              Frank Kulash
              Hi,
              570142 wrote:
              Ok, I understand. I'm waiting for your test. I guess you know what I mean.
              I think I know what you mean. I believe you're saying that this isn't important enough for you to spend your time on it. I can understand that.
              • 19. Re: Add a table in this query?
                978776
                Hi again Frank,

                What do you mean with your answer?
                • 20. Re: Add a table in this query?
                  Frank Kulash
                  Hi,
                  570142 wrote:
                  Hi again Frank,

                  What do you mean with your answer?
                  You seem to be saying that you have better things to do with your time than work on this problem. You might consider reading an answer, as long as you don't have to read it very carefully or think about it, but actually spending time trying to solve the problem, or post a clear question, takes too much time, and the problem isn't that important to you.
                  I can understand that. There are some things I'd like to do, but I suspect I never will do because there always seem to be more important things for me to do.
                  • 21. Re: Add a table in this query?
                    978776
                    Hi again,

                    I still not understand what you mean. I ask for help because I did not manage to solve it by my own competence. I have difficulty in understanding and writing English. Which I apologize for.
                    But I have nowhere near the knowledge you have and I have so far been extremely grateful for your help. And as I wrote earlier, I learn at the same time.

                    So you are not able to help me with what little is left?


                    Perhaps someone else can help me if you dont have the possibility?
                    • 22. Re: Add a table in this query?
                      Hoek
                      What part of Frank's last example isn't clear?
                      You have no main query, please reread:
                      "You don't have a main query. The last sub-query (agg) should be the main query"
                      Re: Add a table in this query?

                      Are you not able to adjust that yourself, given Frank's input?
                      • 23. Re: Add a table in this query?
                        978776
                        Hi,

                        Thanks for the answer.

                        No, I'm not able to fix this by ny self, So I need help.

                        And yes, I have read Frank's answer. But the problem is that the SQL must start with SELECT and not WITH when I call it from Net ... here I'm stucked..
                        • 24. Re: Add a table in this query?
                          978776
                          Frank, I really need your help then I guess you are the only one who understands the problem.
                          • 25. Re: Add a table in this query?
                            Frank Kulash
                            Hi,

                            In the 24 hours since I described the solution, have you had a chance to try it?
                            Something like this:
                            SELECT       material
                            ,       NVL (SUM (sales_quantity), 0)          AS total_sales_quantity
                            ,       NVL (SUM (con_quantity),   0)          AS total_con_quantity
                            ,       TO_CHAR ( a_period_start
                                          , MIN (date_format)
                                        )                    AS period
                            FROM       (     -- Begin in-line view
                                   WITH     params          AS
                                   (
                                      SELECT     TO_DATE ('2012-05-01', 'yyyy-mm-dd') AS first_posting_date
                                      ,     TO_DATE ('2012-06-30', 'yyyy-mm-dd') AS last_posting_date
                                      ,     'MONTH'                                AS week_or_month
                                      FROM     dual
                                   )
                                   ,     periods_vw     AS
                                   (
                                       SELECT     CASE
                                                   WHEN  week_or_month = 'WEEK'
                                                   THEN  TRUNC (first_posting_date, 'IW') 
                                                          + (7 * (LEVEL - 1))
                                                  ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                                                     , LEVEL - 1
                                                               )
                                           END                    AS a_period_start
                                       ,          CASE
                                                   WHEN  week_or_month = 'WEEK'
                                                   THEN  TRUNC (first_posting_date, 'IW') 
                                                            + (7 * LEVEL)
                                                   ELSE  ADD_MONTHS ( TRUNC (first_posting_date, 'MONTH')
                                                                     , LEVEL
                                                               )
                                           END                    AS next_period_start
                                      ,     CASE
                                                   WHEN  week_or_month = 'WEEK'
                                                   THEN  'IYYYIW'
                                                   ELSE  'YYYYMM'
                                           END                    AS date_format
                                      FROM    params
                                      CONNECT BY     LEVEL <= 1 + CASE
                                                                 WHEN  week_or_month = 'WEEK'
                                                            THEN  ( ( TRUNC (last_posting_date,  'IW')
                                                                                           - TRUNC (first_posting_date, 'IW')
                                                                        )
                                                                             / 7
                                                                             )
                                                            ELSE  MONTHS_BETWEEN ( TRUNC (last_posting_date,  'MONTH')
                                                                                       , TRUNC (first_posting_date, 'MONTH')
                                                                                       )
                                                                     END
                                      )
                                 --     The MAIN query associated with the WITH clause begins here:
                                 SELECT       b.material
                                 ,       b.sales_quantity
                                 ,       0          AS con_quantity
                                 ,       pb.a_period_start
                                 ,       pb.date_format
                                 FROM            periods_vw  pb
                                 LEFT OUTER JOIN       tblbill     b  PARTITION BY (b.material)
                                                              ON  b.posting_date >= pb.a_period_start
                                                               AND b.posting_date <  pb.next_period_start
                                 WHERE     b.material   IN ('Label1', 'Label2')
                                UNION ALL
                                 SELECT       c.mtrl
                                 ,       0
                                 ,       c.con_quantity
                                 ,       pc.a_period_start
                                 ,       pc.date_format
                                 FROM            periods_vw  pc
                                 LEFT OUTER JOIN       tblcon      c  PARTITION BY (c.mtrl)
                                                              ON  c.posting_date >= pc.a_period_start
                                                               AND c.posting_date <  pc.next_period_start
                                 WHERE     c.mtrl     IN ('Label1', 'Label2')
                                 )     -- End in-line view
                            GROUP BY  material
                            ,            a_period_start
                            ORDER BY  material
                            ,            a_period_start
                            ;
                            • 26. Re: Add a table in this query?
                              978776
                              Thanks!

                              When I run this query directly in orcale it works excellent.
                              When I try to run it via VB.net 2010 I get the following error message:
                              ORA-04030: out of process memory when trying to allocate 44 bytes (kxs-heap-w, cursor work heap)
                              What have I missed?
                              • 27. Re: Add a table in this query?
                                978776
                                Someone...?
                                1 2 Previous Next