1 2 Previous Next 17 Replies Latest reply on Aug 25, 2019 3:17 PM by AQH

    Wrong result set when use IN clause with multiple values

    AQH

      my sql query fetch records with accurate "CLOSING" column (as shown in below record set) when using single selection of accountid.

      i am facing issue in this query (below) with run with IN clause multiple accountid, it gives inaccurate CLOSING value, require assistance to fix this issue;

       

      Header 1

      CREATE TABLE AML

      (

        ID          NUMBER,

        ACCOUNT_ID  NUMBER,

        AAID        NUMBER,

        DEBIT       NUMBER,

        CREDIT      NUMBER,

        TRDATE      DATE

      )

       

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 18,2292,41,44000,0,'03-Aug-2019');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE) VALUES ( 19,2292,41,1299000,0,'03-Aug-2019');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 1,2440,40,0,177555,'31-Mar-2018');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 2,2440,41,69180,0,'12-Apr-2018');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 17,2440,40,69180,0,'12-Apr-2018');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 13,2441,42,0,10000,'31-Mar-2018');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 14,2441,42,69180,0,'12-Apr-2018');

      INSERT INTO AML (ID, ACCOUNT_ID, AAID, DEBIT, CREDIT, TRDATE)  VALUES ( 16,2441,42,0,1000,'16-Apr-2018');

       

       

       

      WITH ledger

           AS (SELECT account_id,

                      aaid,

                      TrDate,

                      Debit,

                      Credit,

                      ROW_NUMBER () OVER (ORDER BY trdate) AS row1

                 FROM aml

                WHERE account_id IN (2292,2440) AND aaid IN (40, 41))

        SELECT L1.account_id,

               L1.aaid,

               L1.TrDate,

               L1.Debit AS Debit,

               L1.Credit AS Credit,

               (CASE

                   WHEN SIGN (

                           (  NVL (L1.Credit, 0)

                            + NVL (SUM (L2.Credit), 0)

                            - NVL (SUM (L2.Debit), 0)

                            - NVL ( (L1.Debit), 0))) = -1

                   THEN

                      ABS (

                         (  NVL (L1.Credit, 0)

                          + NVL (SUM (L2.Credit), 0)

                          - NVL (SUM (L2.Debit), 0)

                          - NVL ( (L1.Debit), 0)))

                   ELSE

                        -1

                      * (  NVL (L1.Credit, 0)

                         + NVL (SUM (L2.Credit), 0)

                         - NVL (SUM (L2.Debit), 0)

                         - NVL ( (L1.Debit), 0))

                END)

                  AS closing

          FROM ledger L1

               LEFT JOIN ledger L2

                  ON     L1.row1 > L2.row1

                     AND L1.aaid IN (40, 41)

                     AND L1.account_id IN (2292,2440)

                     AND L1.trDate >= TO_DATE ('01-Apr-2018', 'dd-Mon-yyyy')

                     AND L1.trDate <=  TO_DATE ('03-Aug-2019','dd-Mon-yyyy')

      GROUP BY L1.account_id,

               L1.aaid,

               L1.TrDate,

               L1.Credit,

               L1.Debit

      ORDER BY L1.ACCOUNT_ID,

               L1.aaid,

               L1.TRDATE,

               closing

        • 1. Re: Wrong result set when use IN clause with multiple values
          L. Fernigrini

          Are you trying to do "running totals"?

           

          If so, try something like this:

           

          WITH vLedger AS (

          SELECT account_id,

                 aaid,

                 TrDate,

                 Debit,

                 Credit,

                 ROW_NUMBER () OVER (PARTITION BY account_id ORDER BY trdate) AS row1

          FROM aml

          WHERE account_id IN (2292,2440) AND aaid IN (40, 41)

          )

          SELECT account_id,

                 aaid,

                 TrDate,

                 Debit,

                 Credit,

                 SUM (Debit - Credit) OVER (PARTITION BY account_id ORDER BY row1) AS Closing

          FROM vLedger

          ORDER BY account_id, row1;

           

          • 2. Re: Wrong result set when use IN clause with multiple values
            AQH

            yes. but query gives  different result compre with yours  i.e, am i missing something.

            • 3. Re: Wrong result set when use IN clause with multiple values
              mathguy

              You need to tell us what the code is supposed to do. We cannot guess that from your code (since, as you admit yourself, the code does NOT do what it is supposed to do). And, without knowing the problem statement, how can we tell you what's wrong in the code?

              • 4. Re: Wrong result set when use IN clause with multiple values
                L. Fernigrini

                What I posted, and the results, are for Running Totals.

                 

                I assumed, by just viewing the query you have posted, that you wanted that behavior, and were doing it in a complex way..

                 

                Please explain in plain English what you want to show on the "closing" column and we can probably help you.

                • 5. Re: Wrong result set when use IN clause with multiple values
                  jaramill

                  Also per the forum guidelines on the FAQ link --> Re: 2. How do I ask a question on the forums?

                  please always answer #5 as that can determine the type answer you get:

                   

                  5) Database Version and IDE Version
                  Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

                   

                  If you're not sure what it is you can do the following:

                  select * from v$version;

                  in an SQL*Plus session and paste the results.

                  • 6. Re: Wrong result set when use IN clause with multiple values
                    Paulzip

                    It might be a red herring, as I'm not entirely sure what your query should be doing, but you do realise your row1 is non deterministic? You have ties and aren't ordering by a secondary term, say ROWID? You should do something like..

                    ROW_NUMBER () OVER (ORDER BY trdate, ROWID) AS row1

                     

                    Oracle may materialize LEDGER in which case you'll be ok, but you should add ROWID anyway.

                    • 7. Re: Wrong result set when use IN clause with multiple values
                      AQH

                      i am using version Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

                      • 8. Re: Wrong result set when use IN clause with multiple values
                        AQH

                        my query (posted in question when using IN (2292) ) gives result set with column CLOSING values 1299000 and 1343000 respectively but these values turns to 1259805 and 1303805

                        when same query runs with IN (2292,2440),  which is wrong and i need to fix the query so it should give result accuretly so it retain vales of id 2292 with  values 1299000 and 1343000 .

                        • 9. Re: Wrong result set when use IN clause with multiple values
                          mathguy

                          OK, I think I understand what's going on.

                           

                          You really want cumulative sums; but since there may be duplicate rows, you are using the ROWNUM() trick. That is unnecessary; instead, you must use a windowing clause, to take the sums based on ROWS, not RANGE.

                           

                          There is still some indeterminacy in the query below, but it does not matter; even if by some chain of events the specific row order is different in the two analytic sums, the output is still always the same (since rows may be interchanged, but the significant values DO NOT CHANGE).

                           

                          I came up with this. Notice the ORDER BY clause, with DESC for amounts (and NULLS LAST), and breaking ties by AAID first, in case of same account id and same transaction date. The order may not be what you want; perhaps you must always consider non-null debits, then all non-null credits, regardless of AAID? Impossible to tell from your test data, but whatever your real life need, it can be addressed through the ORDER BY clause.

                           

                          (Side note - when you insert into a DATE column, do not insert strings; my NLS_DATE_FORMAT was different from yours, so implicit conversion to DATE data type failed miserably. Use TO_DATE in your INSERT statements.)

                           

                           

                          select account_id, aaid, trdate, debit, credit,

                                   nvl(sum(debit)  over (partition by account_id order by trdate, aaid, debit desc nulls last, credit desc nulls last

                                                         rows between unbounded preceding and current row), 0)

                                 - nvl(sum(credit) over (partition by account_id order by trdate, aaid, debit desc nulls last, credit desc nulls last

                                                         rows between unbounded preceding and current row), 0) as closing

                          from   aml

                          where  account_id in (2292, 2440) and aaid in (40, 41)

                          order  by account_id, trdate, aaid, debit desc nulls last, credit desc nulls last

                          ;

                           

                           

                          ACCOUNT_ID       AAID TRDATE           DEBIT     CREDIT    CLOSING

                          ---------- ---------- ----------- ---------- ---------- ----------

                                2292         41 03-Aug-2019    1299000          0    1299000

                                2292         41 03-Aug-2019      44000          0    1343000

                                2440         40 31-Mar-2018          0     177555    -177555

                                2440         40 12-Apr-2018      69180          0    -108375

                                2440         41 12-Apr-2018      69180          0     -39195

                           

                          1 person found this helpful
                          • 10. Re: Wrong result set when use IN clause with multiple values
                            mathguy

                            AQH wrote:

                             

                            my query (posted in question when using IN (2292) ) gives result set with column CLOSING values 1299000 and 1343000 respectively but these values turns to 1259805 and 1303805

                            when same query runs with IN (2292,2440), which is wrong and i need to fix the query so it should give result accuretly so it retain vales of id 2292 with values 1299000 and 1343000 .

                             

                            The fundamental mistake in your original query is that you do not PARTITION BY account id in your analytic SUM functions. It makes no difference when you filter for a single account, but as soon as you have two or more accounts, you must keep the computations separate for each account - which is what PARTITION BY does in analytic functions.

                             

                            There are other assorted problems as well; the biggest one being an overly complicated query to begin with. No need for joins, for example. I will let my solution speak for itself (posted right above this Reply).

                            • 11. Re: Wrong result set when use IN clause with multiple values
                              L. Fernigrini

                              When there are more than 1 transaction for the same account on the same day, given current data, there is no defined way to sort it, That's why I used a row number column to create a unique id that could be used later on the main query to sort data, and included the partition clause that was missing.

                               

                              OP should provide an explanation on how to sort data uniquely / deterministic in order to have a final solution.

                              • 12. Re: Wrong result set when use IN clause with multiple values
                                mathguy

                                What I tried to say is this:

                                 

                                The ROW_NUMBER() thing is not needed. If we use analytic SUM() twice, and if we partition as needed and we also order (in some manner - the OP should say how, just like he needs to say even for the ROW_NUMBER() approach) - so , if we order within both analytic SUM() by the same criteria, and the criteria include ALL the columns that are used in the computations, and if moreover we use ROWS BETWEEN... instead of the default RANGE ...  - then even though the ordering is still non-deterministic, the result (the output) IS deterministic. Even if the rows could be distinguished by other means (for example by ROWID, if the tables were stored on disk - of course ROWID would not work for data from a WITH clause), and even if the ordering of rows for one SUM() calculation is different from the other (meaning, by ROWID, the rows are in different order - but they are still IN THE SAME ORDER by the columns used in the computation), then the OUTPUT is unaffected!

                                 

                                For what it's worth, I guided my specific ordering (which may still not be what the OP wanted) by the OP's output. The output shows the AAID column; if it needs to appear in the output in the order shown in the original post, the ordering must include AAID - but I still can't tell if it should come before DEBIT and CREDIT or after. If AAID was not included in the output, I wouldn't need it in any of the ORDER BY clauses either, for the same reason I explained above.

                                • 13. Re: Wrong result set when use IN clause with multiple values
                                  AQH

                                  i do as per this query it work fine with an issue raise with using date in where condition (below) it gives CLOSING value i.e 69180 and 138360 instead of -108375, -39195 which is require and accurate. (query mention in question gives this result value  -108375, -39195  when i use this date condition)

                                   

                                  where  account_id in (2292, 2440) and aaid in (40, 41)  AND

                                  aml.trDate >= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy') and trdate<= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy')

                                  • 14. Re: Wrong result set when use IN clause with multiple values
                                    Frank Kulash

                                    Hi,

                                    AQH wrote:

                                     

                                    i do as per this query it work fine with an issue raise with using date in where condition (below) it gives CLOSING value i.e 69180 and 138360 instead of -108375, -39195 which is require and accurate. (query mention in question gives this result value -108375, -39195 when i use this date condition)

                                     

                                    where account_id in (2292, 2440) and aaid in (40, 41) AND

                                    aml.trDate >= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy') and trdate<= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy')

                                    Analytic functions (like SUM in the query Mathguy posted in reply #9) are computed after the conditions in the WHERE clause have been applied.  If you filter by trdate before computing SUM, then the results from this sample data are

                                    ACCOUNT_ID       AAID TRDATE           DEBIT     CREDIT    CLOSING
                                    ---------- ---------- ----------- ---------- ---------- ----------
                                          2440         40 12-Apr-2018      69180          0      69180
                                          2440         41 12-Apr-2018      69180          0     138360

                                    Rows with trdate < April 12, 2018 are ignored completely, so the value of closing doesn't depend on them.

                                    If you want these results:

                                    ACCOUNT_ID       AAID TRDATE           DEBIT     CREDIT    CLOSING
                                    ---------- ---------- ----------- ---------- ---------- ----------
                                          2440         40 12-Apr-2018      69180          0    -108375
                                          2440         41 12-Apr-2018      69180          0     -39195

                                    then you don't want to ignore the earlier trdates completely.  You want the value of closing to depend on those rows, but you just don't want to display them.  In that case, you can compute SUM in a sub-query, and filter out the early dates in a super-query, like this:

                                    WITH    got_closing    AS

                                    (

                                        select account_id, aaid, trdate, debit, credit,

                                                 nvl(sum(debit)  over (partition by account_id order by trdate, aaid, debit desc nulls last, credit desc nulls last

                                                                       rows between unbounded preceding and current row), 0)

                                               - nvl(sum(credit) over (partition by account_id order by trdate, aaid, debit desc nulls last, credit desc nulls last

                                                                       rows between unbounded preceding and current row), 0) as closing

                                        from   aml

                                        where  account_id in (2292, 2440) and aaid in (40, 41)

                                        and    trdate  <= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy')

                                    )

                                    SELECT    *

                                    FROM      got_closing

                                    WHERE     trDate  >= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy')

                                    ORDER BY  account_id

                                    ,         trdate

                                    ,         aaid

                                    ,         debit    DESC  NULLS LAST

                                    ,         credit   DESC  NULLS LAST

                                    ;

                                    In this case, the condition

                                    trdate  <= TO_DATE ('12-Apr-2018', 'dd-Mon-yyyy')

                                    could be applied either before or after SUM is computed; the final results are the same either way.  However, it's more efficient to apply that condition before computing SUM.

                                    1 2 Previous Next