14 Replies Latest reply on Jan 18, 2019 9:35 AM by Cookiemonster76

    Query help

    Ricky007

      DEAR EXPERTS,

       

       

       

      I want to decline a customer who is doing transaction more than 5000 in 12 hours cooling period window since his first transaction.

       

       

      create table TRAN_LOG

      (

      id number,

      CUST_MOB varchar(20),

      TRAN_AMT number,

      TRAN_DATE timestamp default systimestamp

      );

       

      insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (1,'999999999',1000,TO_TIMESTAMP('17-JAN-19 10.24.54.918726000 AM','DD-MON-RR HH.MI.SS.FF AM'));

      insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (2,'999999999',3000,TO_TIMESTAMP('17-JAN-19 02.33.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

      insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (3,'88888888',2500,TO_TIMESTAMP('16-JAN-19 01.25.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

      insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (4,'88888888',3000,TO_TIMESTAMP('17-JAN-19 04.42.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

       

       

      create table TRAN_LIMIT

      (

      LIMIT_AMT number,

      LIMIT_TIME number

      );

       

       

      insert into TRAN_LIMIT values(5000,12);

       

       

      HERE I WANT to DECLINE 999999999

      and allow 88888888 because he has completed cooling period

       

      Tried one

       

       

       

      declare

      P_MOB varchar2(20);

      p_amt number;

       

       

      CURSOR CUR_LMT

        is

            select NVL(SUM(TRAN_AMT),0) MCL_AMT_PD

          from TRAN_LOG

         where CUST_MOB   =p_mob

           GROUP BY CUST_MOB;

       

       

      begin

      FOR J IN CUR_LMT

          LOOP

          

            if J.MCL_AMT_PD > P_AMT then

             dbms_output.put_line('wait for cooling period to complete');

            

              RETURN;

            end if;

          END LOOP;

      end;

      end;

       

      Please help

        • 1. Re: Query help
          Cookiemonster76

          Well in your attempted code you don't assign any values to the variables - so the cursor won't find anything and even if it did p_amt being null means the IF will never be true.

          I'm not sure why you're using a for loop since the cursor is designed to only ever return a single row.

          The group by in your select is pointless since the where clause specifies that cust_mob can only have one value.

          The code doesn't refer to tran_limit at any point.

          The code doesn't restrict by time period at any point.

           

          You need to query tran_limit and then query tran_log for rows within the time period - probably tran_date between sysdate - limit_time/24 and sysdate

          And p_mob would need to be a parameter for this code to be useful.

          • 2. Re: Query help
            Frank Kulash

            Hi,

            Ricky007 wrote:

             

            DEAR EXPERTS,

             

             

             

            I want to decline a customer who is doing transaction more than 5000 in 12 hours cooling period window since his first transaction.

             

             

            create table TRAN_LOG

            (

            id number,

            CUST_MOB varchar(20),

            TRAN_AMT number,

            TRAN_DATE timestamp default systimestamp

            );

             

            insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (1,'999999999',1000,TO_TIMESTAMP('17-JAN-19 10.24.54.918726000 AM','DD-MON-RR HH.MI.SS.FF AM'));

            insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (2,'999999999',3000,TO_TIMESTAMP('17-JAN-19 02.33.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

            insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (3,'88888888',2500,TO_TIMESTAMP('16-JAN-19 01.25.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

            insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (4,'88888888',3000,TO_TIMESTAMP('17-JAN-19 04.42.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));

             

             

            create table TRAN_LIMIT

            (

            LIMIT_AMT number,

            LIMIT_TIME number

            );

             

             

            insert into TRAN_LIMIT values(5000,12);

             

             

            HERE I WANT to DECLINE 999999999

            and allow 88888888 because he has completed cooling period

             

            Tried one

             

             

             

            declare

            P_MOB varchar2(20);

            p_amt number;

             

             

            CURSOR CUR_LMT

            is

            select NVL(SUM(TRAN_AMT),0) MCL_AMT_PD

            from TRAN_LOG

            where CUST_MOB =p_mob

            GROUP BY CUST_MOB;

             

             

            begin

            FOR J IN CUR_LMT

            LOOP

             

            if J.MCL_AMT_PD > P_AMT then

            dbms_output.put_line('wait for cooling period to complete');

             

            RETURN;

            end if;

            END LOOP;

            end;

            end;

             

            Please help

            Here's one way:

            WITH    got_min_tran_date    AS

            (

                SELECT  cust_mob, tran_amt, tran_date

                ,       MIN (tran_date)  OVER (PARTITION BY  cust_mob)  AS min_tran_date

                FROM    tran_log

            )

            SELECT    m.cust_mob

            ,         SUM (m.tran_amt)   AS mcl_amt_pd  -- if wanted

            FROM      got_min_tran_date  m

            JOIN      tran_limit         l  ON  m.tran_date  <= m.min_tran_date + NUMTODSINTERVAL (l.limit_time, 'HOUR')

            GROUP BY  m.cust_mob

            HAVING    SUM (m.tran_amt)  >= MIN (l.limit_amt)

            ORDER BY  m.cust_mob

            ;

            This displays the cust_mons who have a total of more than limit_amt in the initial period.  That means neither row from your sample data.  Cust_mob '88888888' didn't exceed the limit until 27 hours after the first transaction, and '999999999' never exceeded the limit at all.

             

            Notice you don't need PL/SQL to solve this problem.  If you're using PL/SQL for some other reason, then you may want to query tran_limit first, and save the limits in local variables.

            • 3. Re: Query help
              Ricky007

              Thanks for the reply.

               

              Please help to find the hour difference of minimum hours and with current time.

               

                select ((sysdate- min(TRAN_DATE))*24) DIFFERENCE_IN_HOURS

                    from tran_log where cust_mob='999999999';

               

              output i'm getting is

               

              5 5:54:1.950576

               

              actual result expected

               

              5.30

               

              Please help

              • 4. Re: Query help
                John Thorton

                Ricky007 wrote:

                 

                Thanks for the reply.

                 

                Please help to find the hour difference of minimum hours and with current time.

                 

                select ((sysdate- min(TRAN_DATE))*24) DIFFERENCE_IN_HOURS

                from tran_log where cust_mob='999999999';

                 

                output i'm getting is

                 

                5 5:54:1.950576

                 

                actual result expected

                 

                5.30

                 

                Please help

                how & why is 5.30 expected result?

                • 5. Re: Query help
                  jaramill

                  Keep in mind this link as you should read it --> Re: 2. How do I ask a question on the forums?

                  and pay attention to #2 (which I copied/pasted here)

                   

                  2) Thread Subject line

                  Give your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP".  This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.  By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond unfavorably.

                  • 6. Re: Query help
                    Cookiemonster76

                    Well date - timestamp (which is what tran_date is) gives an interval.

                    Do you want an interval?

                    Or something else? If so what? What is 5.30 supposed to be? A number? What's the 30? minutes? fraction of an hour?

                    • 7. Re: Query help
                      mathguy

                      I'm not sure you formulated your question correctly.

                       

                      Do you need to measure from the first transaction the same customer made? Even if the first transaction was two years ago, but the same customer had a transaction with value 3000 two hours ago, and now they want to make another transaction with 3000 value? Do you still care when the FIRST transaction was (two years ago - so the cooling period has ended)?

                       

                      If this is really your business requirement, then it is a very strange one. What I have seen much more often is a different calculation: How big and when the FIRST transaction was doesn't matter. What does matter is, for any given customer who wants to enter a transaction, what is the total of transactions the same customer made IN THE PAST 12 HOURS. No reference to the "first" transaction.

                       

                      So, please clarify your requirement first - having a perfectly correct and efficient solution to the wrong problem will not help you or anyone else.

                       

                      Then: Your message to the customer is "Wait for cooling period to complete." That is the wrong message. If they already have transactions for 3000 in the past 12 hours, they DON'T have to wait for the 12 hours to complete. Rather, they are limited to 2000 in additional transactions - THAT is what the error message should say to the customer. Obviously, this is not a computer related issue, but it is a problem 100% in the business/management area. Talk to your business users - do they really want the customer to be told to wait for the end of the cooling period, or should they instead tell the customer what the current limit is (so that the customer can change the amount of their transaction)?

                       

                      Another thing - you ask about cooling period since a different transaction. What if a customer has no other transactions, but is trying to enter a single transaction in an amount greater than the allowed limit? I suppose that should be declined too (and this would be solved by the same query); or do you need to handle that separately, and with a different message to the customer?

                       

                      How are the input variables presented to you? Meaning: customer number and amount of transaction? And where does the output go? I ask because you wrote this as a procedure, with a DBMS_OUTPUT message. It makes more sense to me to write your logic in a function, or perhaps in a plain SQL query - depending on how the output (the result) is used.

                      • 8. Re: Query help
                        Ricky007

                        select

                            trunc ( ( abc - ( trunc ( abc ) ) ) * 24 ) hours

                           from

                          (select sysdate -

                                  min(TRAN_DATE) abc

                          from tran_log

                          );

                         

                        getting below error

                         

                        ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

                        00932. 00000 -  "inconsistent datatypes: expected %s got %s"

                        *Cause:   

                        *Action:

                        Error at Line: 20 Column: 31

                        • 9. Re: Query help
                          Cookiemonster76

                          As I mentioned above date-timestamp gives an interval.

                          You can't apply trunc to an interval, if you try you get that error.

                          If you convert the timestamp to date (using cast) then the subtraction will give a number representing the fraction of days - you can apply trunc to that.

                          • 10. Re: Query help
                            jaramill

                            Per Cookiemonster76's suggestion:

                             

                            select trunc((abc - (trunc(abc))) * 24) hours
                              from (
                                    select sysdate - min(cast(tran_date as date)) abc
                                      from tran_log
                                   );
                            

                             

                            Also please UPDATE your subject heading of this post, as "Query help" is meaningless and frowned upon here at the forums.  Change it to something meaningful like (CASTING) or something else.  I posted in a previous reply the rules on titles to threads created.

                            • 11. Re: Query help
                              Ricky007

                              Thanks for query jaramil.

                               

                              when i try to get difference of hours for the below date i'm geting only 2 hours

                               

                              insert into tran_log (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (7,'858585',4500,TO_TIMESTAMP('18-JAN-17 10.40.28.918726000 AM','DD-MON-RR HH.MI.SS.FF AM'));

                               

                              select trunc((abc - (trunc(abc))) * 24) hours 

                              1.   from
                              2.         select sysdate - min(cast(tran_date as date)) abc 
                              3.           from tran_log 
                              4.        ); 

                               

                               

                              Actually it should be 17520 hour difference.

                               

                              Please help

                              • 12. Re: Query help
                                Ricky007

                                Tried with below block

                                 

                                declare

                                       V_DATE1 varchar2(50);

                                       V_DATE2 DATE:=SYSDATE;

                                      

                                       begin

                                       select min(TRAN_DATE) into V_DATE1 from TRAN_LOG  where CUST_MOB='858585';

                                      

                                        select (V_DATE2-V_DATE1)*24 as DIFF_HOURS

                                       from TRAN_LOG  where CUST_MOB='858585';

                                      

                                       end;

                                 

                                Error occured

                                 

                                PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

                                ORA-06550: line 8, column 9:

                                PL/SQL: SQL Statement ignored

                                06550. 00000 -  "line %s, column %s:\n%s"

                                *Cause:    Usually a PL/SQL compilation error.

                                *Action:

                                • 13. Re: Query help
                                  Cookiemonster76

                                  Why is v_date1 a varchar?

                                  It should be date. And you should be casting min(tran_date) to date when you select it.

                                  Also what is the point of the second select? You're not selecting any columns from the table. Also you're missing an INTO on the 2nd select.

                                   

                                  You need to think more about the code you write.

                                  • 14. Re: Query help
                                    Cookiemonster76

                                    Ricky007 wrote:

                                     

                                    Thanks for query jaramil.

                                     

                                    when i try to get difference of hours for the below date i'm geting only 2 hours

                                     

                                    insert into tran_log (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (7,'858585',4500,TO_TIMESTAMP('18-JAN-17 10.40.28.918726000 AM','DD-MON-RR HH.MI.SS.FF AM'));

                                     

                                    select trunc((abc - (trunc(abc))) * 24) hours

                                    1. from (
                                    2. select sysdate - min(cast(tran_date as date)) abc
                                    3. from tran_log
                                    4. );

                                     

                                     

                                    Actually it should be 17520 hour difference.

                                     

                                    Please help

                                    Well all Jaramil did was fix the casting issue, he didn't look at the calculation.

                                    If you've got a complicated calculation that isn't doing what you expect then the best thing to do is break it down into individual calculations to see what each bit is doing:

                                     

                                    SQL> select trunc((abc - (trunc(abc))) * 24) hours,
                                      2         abc,
                                      3         TRUNC(abc),
                                      4         abc - TRUNC(abc),
                                      5         (abc - (trunc(abc))) * 24
                                      6    from (
                                      7          select sysdate - min(cast(tran_date as date)) abc
                                      8            from tran_log
                                      9         );
                                    
                                    
                                         HOURS        ABC TRUNC(ABC) ABC-TRUNC(ABC) (ABC-(TRUNC(ABC)))*24
                                    ---------- ---------- ---------- -------------- ---------------------
                                            22 729.952222        729 0.952222222222      22.8533333333333
                                    

                                     

                                    abc is a number of days with fraction hours. trunc(abc) is the number of days without the fractional hours.

                                    If you subtract the two you are left with the fractional hours - which tops out at 24.

                                    If you want total hours, why are you doing that subtraction?

                                    You simply want

                                    abc * 24