Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: max(time column)

    Hi All,

    I have a situation here where I have to check if there was an entry in a table for the current period. If it didnt , then it should exit.

    I have something like this-

    TABLE per_test
    Name Null? Type
    --------------------------- -------- ----------------------------
    PERIOD NUMBER

    select * from per_test
    PERIOD
    ----------
    200708
    200801
    200803
    200709
    200712
    BEGIN
    FOR rec in (select max(period) period from per_test)
    LOOP
    IF rec.period <> sysdate THEN
    DBMS_OUTPUT.PUT_LINE(' No data for current period');
    END IF;
    END LOOP;
    END;
    If there is no entry in per_test for current period then it should exit
    When I execute the above code, I get the below error.

    ORA-06550: line 4, column 16:
    PLS-00306: wrong number or types of arguments in call to '!='
    Is there any other way of getting the maximum period? Any ideas?
    Last edited by nandinir; 09-22-08 at 16:14.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure; don't compare numbers (per_test.period column datatype) with dates (result that the SYSDATE function returns). Note that SYSDATE returns both date and time (check use of the TO_CHAR function).

    By the way, why do you need a LOOP if it iterates only once (as SELECT returns only maximum value)?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    nd you have a cursor loop with no name for the max column. Every column in a cursor MUST have an alias.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    Thanks to you!
    I will make the changes and see.

    -Nandinir

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Thanks it works when I changed it to

    BEGIN
    FOR rec in (select max(period) period from per_test)
    LOOP
    IF rec.period <> to_char(sysdate,'YYYYMM') THEN
    DBMS_OUTPUT.PUT_LINE(' No data for current period');
    END IF;
    END LOOP;
    END;

    -Nandinir

Posting Permissions

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