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

    Unanswered: understanding delete query

    Hi

    Could someone please help me understand how 'IDATE' is being used?

    Code:
    DELETE FROM SCHEMA.TABLE WHERE DATETIME BETWEEN TO_DATE(IDATE,'DD-MM-YY') AND TO_DATE(IDATE,'DD-MM-YY')+1-1/1440;
    I tried
    Code:
    SELECT SCHEMA.TABLE WHERE DATETIME BETWEEN TO_DATE(IDATE,'DD-MM-YY') AND TO_DATE(IDATE,'DD-MM-YY')+1-1/1440;
    but got IDATE Invalid Identifier.

    Regards
    Shajju
    Last edited by shajju; 04-25-10 at 08:26.

  2. #2
    Join Date
    Apr 2010
    Posts
    16

    IDATE may be a variable in any Procedure/Function.

    Seems you have pasted a DELETE Statement from a procedure/function and WHERE clause of which you are trying in SELECT query.

    Regards,
    Vibhor Kumar
    (PCP,OCP)
    ITIL V3 Certified\n

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks for replying Vibhor. Yes, the delete statement is from a proc.

    Code:
    CREATE OR REPLACE PROCEDURE PROC_NAME(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 ) AS
    BEGIN
    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;
    The result of the above query is that only hours 00:00 to 20:00 are inserted into the table. If I check what the output of the select query is I have to comment out the line
    Code:
    AND TRUNC(OMIP_RAW.DATETIME)=IDATE
    and then I get all 24hrs.
    Please help me undersetand what IDATE is?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Please help me undersetand what IDATE is?
    It's a parameter to your procedure

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks shammat.

    Would it be possbile to explain the first line a bit?

    Code:
    CREATE OR REPLACE PROCEDURE PROC_NAME(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 ) AS
    I've seen code on the net like

    Code:
    CREATE OR REPLACE FUNCTION (IDATE IN DATE)
    Just trying to get my head round
    Code:
    (IDATE IN DATE)/(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 )

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Thanks shammat.

    Would it be possbile to explain the first line a bit?

    Code:
    CREATE OR REPLACE PROCEDURE PROC_NAME(IDATE IN DATE DEFAULT TRUNC(SYSDATE)-1 ) AS
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1709

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1726

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks. Appreciated.

Posting Permissions

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