7 Replies Latest reply on Dec 12, 2018 9:59 PM by Frank Kulash

    Query rewrite

    3134376

      Hi All,

       

      Please suggest me how the below query can be re written and any alternative way available to avoid union, I want to avoid multiple table pass as I am doing here in two union clauses.

       

      WITH t AS ( SELECT *

                     FROM ( SELECT po_line_id

                                   , SUM(debit) totdb

                                   , SUM(credit) totcr

                                   , nvl(SUM(debit), 0) - nvl(SUM(credit), 0) lc

                                   , MAX(effective_date) posted_date

                            FROM ( SELECT t1.credit

                                          , t1.debit

                                          , t1.effective_date

                                          , t3.po_line_id

                                   FROM t1 t1

                                        , t3 t3

                                   WHERE t1.category          = 'A'

                                         AND t1.source_id_int_1   = t3.transaction_id

                                         AND trunc(t1.effective_date) <= nvl(TO_DATE(TO_CHAR(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), trunc(SYSDATE))

                                   UNION

                                   SELECT t1.credit

                                          , t1.debit

                                          , t1.effective_date

                                          , t3.po_line_id

                                   FROM t1 t1

                                        , t2 t2

                                        , t3 t3

                                   WHERE t1.category          = 'B'

                                         AND t1.source_id_int_1   = t2.transaction_id

                                         AND t3.transaction_id    = t2.rcv_transaction_id

                                         AND trunc(t1.effective_date) <= nvl(TO_DATE(TO_CHAR(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), trunc(SYSDATE))

                            )

                            GROUP BY po_line_id

                            HAVING nvl(SUM(debit), 0) - nvl(SUM(credit), 0) <> 0

                          )

      )

      SELECT * FROM t;

        • 1. Re: Query rewrite
          jaramill

          Why do you want to AVOID union?  You say "multiple" passes, so are you saying that your query is SLOW?  How many rows are you expecting to be returned to you?  How big is your database?

          Please read the link on --> Re: 2. How do I ask a question on the forums?

          and provide the information asked for numbers #5 - #9

          • 2. Re: Query rewrite
            3134376

            I don't think it all matters for me else I could have provided, I am only interested in an alternate way to write the query.

            • 3. Re: Query rewrite
              jaramill

              3134376 wrote:

               

              I don't think it all matters for me else I could have provided, I am only interested in an alternate way to write the query.

              trust me it matters, as that is WHY we have the link so we have all the information.

               

              And why are you are aliasing your tables with the SAME table name (t1 t1, etc...)?  An alias is a short-handed/abbreviation or meaningful name).  Also why are you using to_date then to_char then to_date again?  that's unnecessary.

               

              I formatted your query to be more readable and removed that and added extra aliases and used ANSI SQL instead.

               

              with datum as
                   (
                    select t4.po_line_id
                          ,sum(t4.debit)                               as totdb
                          ,sum(t4.credit)                              as totcr
                          ,nvl(sum(t4.debit), 0) - nvl(sum(t4.credit), 0) as lc
                          ,max(t4.effective_date)                      as posted_date
                      from (
                            select t1.credit
                                  ,t1.debit
                                  ,t1.effective_date
                                  ,t3.po_line_id
                              from t1
                             inner join
                                   t3
                                on t1.source_id_int_1 = t3.transaction_id
                             where 1 = 1
                               and t1.category        = 'A'
                               and trunc(t1.effective_date) <= nvl(to_date(:p_date, 'YYYY/MM/DD HH24:MI:SS'), trunc(sysdate))
                             union
                            select t1.credit
                                  ,t1.debit
                                  ,t1.effective_date
                                  ,t3.po_line_id
                              from t1
                             inner join
                                   t2
                                on t1.source_id_int_1 = t2.transaction_id
                             inner join
                                   t3
                                on t3.transaction_id  = t2.rcv_transaction_id
                             where 1 = 1
                               and t1.category        = 'B'
                               and trunc(t1.effective_date) <= nvl(to_date(:p_date, 'YYYY/MM/DD HH24:MI:SS'), trunc(sysdate))
                           ) t4
                     group by t4.po_line_id
                    having nvl(sum(t4.debit), 0) - nvl(sum(t4.credit), 0) != 0
                   )
              select d1.*
                from datum d1;
              

               

              But you STILL have not answered the question as to WHY you want an alternate way?

              • 4. Re: Query rewrite
                Frank Kulash

                Hi,

                3134376 wrote:

                 

                Hi All,

                 

                Please suggest me how the below query can be re written and any alternative way available to avoid union, I want to avoid multiple table pass as I am doing here in two union clauses.

                 

                WITH t AS ( SELECT *

                FROM ( SELECT po_line_id

                , SUM(debit) totdb

                , SUM(credit) totcr

                , nvl(SUM(debit), 0) - nvl(SUM(credit), 0) lc

                , MAX(effective_date) posted_date

                FROM ( SELECT t1.credit

                , t1.debit

                , t1.effective_date

                , t3.po_line_id

                FROM t1 t1

                , t3 t3

                WHERE t1.category = 'A'

                AND t1.source_id_int_1 = t3.transaction_id

                AND trunc(t1.effective_date) <= nvl(TO_DATE(TO_CHAR(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), trunc(SYSDATE))

                UNION

                SELECT t1.credit

                , t1.debit

                , t1.effective_date

                , t3.po_line_id

                FROM t1 t1

                , t2 t2

                , t3 t3

                WHERE t1.category = 'B'

                AND t1.source_id_int_1 = t2.transaction_id

                AND t3.transaction_id = t2.rcv_transaction_id

                AND trunc(t1.effective_date) <= nvl(TO_DATE(TO_CHAR(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS'), trunc(SYSDATE))

                )

                GROUP BY po_line_id

                HAVING nvl(SUM(debit), 0) - nvl(SUM(credit), 0) <> 0

                )

                )

                SELECT * FROM t;

                This is equivalent to the UNION:

                SELECT  t1.credit

                ,       t1.debit

                ,       t1.effective_date

                ,       t3.po_line_id

                FROM    t1    -- no point in specifying same name as alias

                ,       t3

                WHERE   (     (    t1.category         = 'A'

                              AND  t1.source_id_int_1  = t3.transaction_id

                              )

                        OR    (    t1.category         = 'B'

                              AND  (t1.source_id_int_1, t3.transaction_id)

                                       IN  (

                                               SELECT  transaction_id, rcv_transaction_id

                                               FROM    t2

                                           )

                              )

                        )

                AND     TRUNC (t1.effective_date)  <= NVL ( TO_DATE (:p_date, 'YYYY/MM/DD HH24:MI:SS') -- One TO_DATE is enough

                                                          , SYSDATE  -- no need for TRUNC

                                                          )

                If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

                 

                Whenever you're tempted to use

                TO_DATE (TO_CHAR ...)     or

                TO_CHAR (TO_DATE ...)     , let alone both, look for a simpler, more efficient way.  There almost always is one.

                • 5. Re: Query rewrite
                  jaramill

                  Frank Kulash wrote:

                   

                   

                   

                  Whenever you're tempted to use

                  TO_DATE (TO_CHAR ...) or

                  TO_CHAR (TO_DATE ...) , let alone both, look for a simpler, more efficient way. There almost always is one.

                  it is the first thing that jumped out at me, hence my re-write of that part of the where clause.

                  • 6. Re: Query rewrite
                    3134376

                    Hi Frank,

                     

                    I tried below but some how row count returned by below is not matching with my original query, can you suggest what could be issue.

                     

                    WITH t as ( SELECT t1.credit

                                       , t1.debit

                                       , t1.effective_date

                                       , t3.po_line_id

                                FROM t1 t1

                                WHERE trunc(t1.effective_date) <= nvl(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)

                              )

                    ) SELECT po_line_id

                             , SUM(debit) totdb

                             , SUM(credit) totcr

                             , nvl(SUM(debit), 0) - nvl(SUM(credit), 0) lc

                             , MAX(effective_date) posted_date

                      FROM ( SELECT t.credit

                                    , t.debit

                                    , t.effective_date

                                    , t.po_line_id

                             FROM t

                                  , t3

                             WHERE t.category           = 'A'

                                   AND t3.source_id_int_1   = t3.transaction_id

                             UNION

                             SELECT t.credit

                                    , t.debit

                                    , t.effective_date

                                    , t.po_line_id

                             FROM t

                                  , t2 t2

                                  , t3 t3

                             WHERE t.category          = 'B'

                                   AND t.source_id_int_1   = t2.transaction_id

                                       AND t3.transaction_id   = t2.rcv_transaction_id

                      )

                      GROUP BY po_line_id

                      HAVING nvl(SUM(debit), 0) - nvl(SUM(credit), 0) <> 0

                    • 7. Re: Query rewrite
                      Frank Kulash

                      Hi,

                      3134376 wrote:

                       

                      Hi Frank,

                       

                      I tried below but some how row count returned by below is not matching with my original query, can you suggest what could be issue.

                       

                      WITH t as ( SELECT t1.credit

                      , t1.debit

                      , t1.effective_date

                      , t3.po_line_id

                      FROM t1 t1

                      WHERE trunc(t1.effective_date) <= nvl(TO_DATE(:p_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)

                      )

                      ) SELECT po_line_id

                      , SUM(debit) totdb

                      , SUM(credit) totcr

                      , nvl(SUM(debit), 0) - nvl(SUM(credit), 0) lc

                      , MAX(effective_date) posted_date

                      FROM ( SELECT t.credit

                      , t.debit

                      , t.effective_date

                      , t.po_line_id

                      FROM t

                      , t3

                      WHERE t.category = 'A'

                      AND t3.source_id_int_1 = t3.transaction_id

                      UNION

                      SELECT t.credit

                      , t.debit

                      , t.effective_date

                      , t.po_line_id

                      FROM t

                      , t2 t2

                      , t3 t3

                      WHERE t.category = 'B'

                      AND t.source_id_int_1 = t2.transaction_id

                      AND t3.transaction_id = t2.rcv_transaction_id

                      )

                      GROUP BY po_line_id

                      HAVING nvl(SUM(debit), 0) - nvl(SUM(credit), 0) <> 0

                      Do you really care?  If you don't want to use UNION (isn't that the whole point of this thread?), then do you really want to see the differences between two queries that both use UNION?

                       

                      If you do really care, that's a legitimate question, but it's different from the question in this thread.  Start a new, separate thread if you have a new, separate question.

                       

                      Always post a little sample data (CREATE TABLE and INSERT statements) so the people who want to help you can re-create the problem and test their ideas.