7 Replies Latest reply: Mar 8, 2013 2:57 AM by BluShadow RSS

    Common Table Expersion - inside existing query - How to add it in?

    GMoney
      I need to add somethint like the below query to an existing query. How do I do that?
      with emp_att_t
      as
      (
      select empid,
             count(*) over(partition by empid order by 1) total_cnt,
             sum(decode(present_yn, 'N',1,0)) over(partition by empid order by 1) abs_cnt
        from emp_att
      )  
      select eh.empid, eh.name
        from emp eh
        join emp_att_t ed
          on eh.empid = ed.empid
       where ed.total_cnt > 100
         and ed.abs_cnt > 5
      thanks for looking.

      Edited by: GMoney on Mar 7, 2013 2:00 PM
        • 1. Re: Analytic inside existing query - How to add it in?
          APC
          GMoney wrote:
          I need to add somethint like the below query to an existing query. How do I do that?
          I would suggest a decent text editor. But Notepad will do at a pinch.

          Cheers, APC
          • 2. Re: Analytic inside existing query - How to add it in?
            GMoney
            2 points for pushing the "Post Button" :)
            • 3. Re: Common Table Expersion - inside existing query - How to add it in?
              Frank Kulash
              Hi.
              GMoney wrote:
              I need to add somethint like the below query to an existing query. How do I do that?
              Sorry, I don't understand what the problem is.
              What is your existing query? What do you mean by "add" to it? What problem did you have when you tried it?

              Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
              Always say which version of Oracle you're using (for example, 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              with emp_att_t
              as
              (
              select empid,
              count(*) over(partition by empid order by 1) total_cnt,
              sum(decode(present_yn, 'N',1,0)) over(partition by empid order by 1) abs_cnt
              What is the point of "ORDER BY 1" in the 2 functions above?
              from emp_att
              )  
              select eh.empid, eh.name
              from emp eh
              join emp_att_t ed
              on eh.empid = ed.empid
              where ed.total_cnt > 100
              and ed.abs_cnt > 5
              Are you saying that you have an existing query, and you want to add conditions like the ones in the WHERE clause above to that query? It looks like you already know how to do it. Again, what is the question? You really need to post a concrete example, with sample data and desired results.
              • 4. Re: Common Table Expersion - inside existing query - How to add it in?
                GMoney
                Please disregard the query it's self - it has nothing to do with my question really.

                The below is from a question you answered for me in this thread: Re: MAX Value for multiple fields in same Query
                WITH     got_rn          AS
                (
                     SELECT     ck.circuit_design_id
                     ,     sr.document_number
                     ,     ck.product_id
                     ,     sr.activity_ind
                     ,     sr.order_compl_dt
                     ,     ROW_NUMBER () OVER ( PARTITION BY  ck.circuit_design_id
                                               ORDER BY          sr.document_number     -- or sr.order_compl_dt
                                                   DESC
                                       ) AS rn
                     FROM    ck
                     JOIN     src     ON     ck.circuit_design_id     = src.circuit_design_id
                     JOIN     sr     ON     src.document_number     = sr.document_number
                --     WHERE     ...     -- If you need other filtering, put it here
                )
                SELECT       circuit_design_id
                ,       document_number
                ,       product_id
                ,       activity_ind
                ,       order_compl_dt
                FROM       got_rn
                WHERE       rn     = 1
                ORDER BY  circuit_design_id     -- if wanted
                All I am really asking is how to do an INNER JOIN on one or more other queries.

                Example
                select *
                from (
                Select abc, def
                from t1, t2
                where t1.abc = t2.abc) result1
                
                inner join
                
                WITH     got_rn          AS
                (
                     SELECT     ck.circuit_design_id
                     ,     sr.document_number
                     ,     ck.product_id
                     ,     sr.activity_ind
                     ,     sr.order_compl_dt
                     ,     ROW_NUMBER () OVER ( PARTITION BY  ck.circuit_design_id
                                               ORDER BY          sr.document_number     -- or sr.order_compl_dt
                                                   DESC
                                       ) AS rn
                     FROM    ck
                     JOIN     src     ON     ck.circuit_design_id     = src.circuit_design_id
                     JOIN     sr     ON     src.document_number     = sr.document_number
                --     WHERE     ...     -- If you need other filtering, put it here
                )
                SELECT       circuit_design_id
                ,       document_number
                ,       product_id
                ,       activity_ind
                ,       order_compl_dt
                FROM       got_rn
                WHERE       rn     = 1
                ????????
                
                result2
                
                ON result1.abc = result2.activity_ind
                Edited by: GMoney on Mar 7, 2013 2:23 PM
                • 5. Re: Common Table Expersion - inside existing query - How to add it in?
                  Frank Kulash
                  Hi,

                  Make WITH the first word in the query. Define all the sub-queries first, then, after they are defined, use them wherever you want to.
                  For example:
                  WITH     result1          AS
                  (
                       SELECT  ...
                  )
                  ,     got_rn          AS
                  (
                       SELECT  ...
                  )
                  SELECT  ...
                  FROM    result1
                  JOIN     got_rn     ON ...
                  No kidding, post some sample data and desired results, and an explanation of how you get those results from that data.
                  If you can show the problem using commonly available tables, such as those in the scott schema, then you don;t need to post CREATE TABLE or INSERT statements for the sample data; just post the reuslts and the explanation.
                  • 6. Re: Common Table Expersion - inside existing query - How to add it in?
                    AlbertoFaenza
                    Like this maybe:
                    WITH result1 AS
                    (
                       SELECT *
                         FROM (SELECT abc, def
                                FROM t1, t2
                               WHERE t1.abc = t2.abc)
                    )
                    , got_rn        AS
                    (
                        SELECT    ck.circuit_design_id
                        ,    sr.document_number
                        ,    ck.product_id
                        ,    sr.activity_ind
                        ,    sr.order_compl_dt
                        ,    ROW_NUMBER () OVER ( PARTITION BY  ck.circuit_design_id
                                               ORDER BY         sr.document_number    -- or sr.order_compl_dt
                                                DESC
                                       ) AS rn
                        FROM    ck
                        JOIN    src    ON    ck.circuit_design_id    = src.circuit_design_id
                        JOIN    sr    ON    src.document_number    = sr.document_number
                    --    WHERE    ...    -- If you need other filtering, put it here
                    )
                    SELECT r2.circuit_design_id
                    ,      r2.document_number
                    ,      r2.product_id
                    ,      r2.activity_ind
                    ,      r2.order_compl_dt
                    FROM   got_rn r2 
                           INNER JOIN result1 r1
                              ON (r1.abc = r2.activity_ind) 
                    WHERE      r2.rn    = 1;
                    Regards.
                    Al
                    • 7. Re: Common Table Expersion - inside existing query - How to add it in?
                      BluShadow
                      Just for information, in Oracle these are not called Common Table Expressions (that's an SQL Server name).
                      In Oracle they are called "subquery factoring" and can be found in the documentation for the SELECT statement:

                      http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2161315