This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Dec 30, 2012 4:01 AM by 978776 Go to original post RSS
  • 15. Re: Add a table in this query?
    978776 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi again Frank,

    What do you mean with your answer?
  • 20. Re: Add a table in this query?
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Someone...?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points