Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: understanding idate....

    Hi guys

    Seeking ur advice once again...

    I'm trying to test the result of the query below as I am not getting the expected 24hrs in the result but only from 00:00 to 20:00 hrs.

    but when I test the select statement and comment out the line
    Code:
    AND TRUNC(OMIP_RAW.DATETIME)=IDATE
    I get the result containing all 24hrs.

    TABLE1 contains all 24hrs, TABLE2 is static (manually created)

    Could someone please help me understand what effect
    Code:
     AND TRUNC(OMIP_RAW.DATETIME)=IDATE
    has on the result?
    Thanks.

    Code:
    CREATE OR REPLACE PROCEDURE PROC_NAME(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 ) AS
    
    DELETE FROM SCHEMA.TABLE WHERE DATETIME BETWEEN TO_DATE(IDATE,'DD-MM-YY') AND TO_DATE(IDATE,'DD-MM-YY')+1-1/1440;
    
       INSERT INTO SCHEMA.TABLE
        SELECT TRUNC(OMIP_RAW.DATETIME,'hh'),'ps200', 1, CONCAT(omip_pre.partner_name,CONCAT(':',omip_pre.application_name)),
          NVL(SUM(CASE WHEN OMIP_RAW.state IN (1) THEN 1 ELSE NULL END),0)  "Successful",
          NVL(SUM(CASE WHEN OMIP_RAW.state IN (0) THEN 1 ELSE NULL END),0)  "Unsuccessful"
    
          FROM SCHEMA.TABLE1 OMIP_RAW,SCHEMA.TABLE2 omip_pre
          WHERE omip_pre.TRANS_PREFIX=SUBSTR(OMIP_RAW.transaction_id,1,4)
    	  AND TRUNC(OMIP_RAW.DATETIME)=IDATE
          GROUP BY TRUNC(OMIP_RAW.DATETIME,'hh'),omip_pre.application_name,OMIP_PRE.PARTNER_NAME
    
    COMMIT;
    END;
    Last edited by shajju; 04-25-10 at 08:20.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    OMIP_RAW is no oracle package that I ever saw. what does it do?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    OMIP_RAW is no oracle package that I ever saw. what does it do?
    In the examle above it is simply a table

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Hm. The condition contains call of TRUNC function on a column and comparison with a parameter . You should know the first one up from this answer: http://www.dbforums.com/oracle/16476...ml#post6422215 (wow, it is over 7 months ago) Comparison is the same as in maths; anyway you may find its description in SQL Reference book too. So, are you really unable to combine these operations together?

    @beilstwh: OMIP_RAW is alias of SCHEMA.TABLE1 in the query posted in the end.

  5. #5
    Join Date
    Feb 2005
    Posts
    57
    Quote Originally Posted by shajju View Post
    Code:
    CREATE OR REPLACE PROCEDURE PROC_NAME(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 ) AS
    
    DELETE FROM SCHEMA.TABLE WHERE DATETIME BETWEEN TO_DATE(IDATE,'DD-MM-YY') AND TO_DATE(IDATE,'DD-MM-YY')+1-1/1440;
    Why are you using TO_DATE against the parameter IDATE when it has been declared as a DATE?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by outrider View Post
    Why are you using TO_DATE against the parameter IDATE when it has been declared as a DATE?
    good question. use TRUNC or somesuch.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    1.can default value be assigned to a variable in declare section with trunc ?
    2.is it a correct declaration .?

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Guys, the problems been sorted. Appreciate all the advice and questions. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •