PL/SQL Questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL Questions

  1. #1
    Join Date
    Jul 2001
    Posts
    59

    Question

    I already have a query which looks like this:
    ------------------------------------------------------
    SET PAGESIZE 100
    SET LINESIZE 200

    SPOOL C:\PKCNT2ND

    ACCEPT CREATION_DATE DATE PROMPT "Enter the date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy
    ACCEPT NEXT_DAY DATE PROMPT "Enter the next day's date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy

    SELECT login, COUNT(DISTINCT sequence)
    FROM history
    WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
    AND to_date('03:30 &&next_day', 'HH24:MI MM-DD-YY')
    GROUP BY login
    ORDER BY COUNT(DISTINCT sequence) DESC
    /
    QUIT
    /

    ---------------------------------------------------------------------
    This is invoked by an NT script and prints out the report automatically and quits the sqlplus application. Basically, it gives a history of scanning transactions for the 2nd shift which is from 16:00 to 03:30. What I don't like about this is that they have to put in two dates because 2nd shift spans two different days. For the life of me, I cannot figure out how to create a variable which just adds one day to the day they input (creation_time) and insert this into the query. I had thought of something like this, but it always fails, no matter how I change it.
    --------------------------------------------------------------------
    ACCEPT CREATION_DATE DATE PROMPT "Enter the date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy

    DECLARE
    v_next_day DATE := '&&creation_date + 1';

    SELECT login, COUNT(DISTINCT sequence) count
    FROM history
    WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
    AND to_date('03:30 v_next_day', 'HH24:MI MM-DD-YY')
    GROUP BY login
    ORDER BY COUNT(DISTINCT sequence) DESC;
    count_record count_cursor%ROWTYPE;

    BEGIN
    OPEN count_cursor;
    LOOP
    FETCH count_cursor INTO count_record;
    EXIT WHEN count_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Sequencer ' || count_record.login || 'picked ' || count_record.count || 'sequences today')
    END LOOP;
    END;
    /
    ----------------------------------------------------------------------
    I'm sure there is more than one reason why this won't work, but I can't figure out what the MAIN problem is...how do I use a variable to add one day to what they input and use it in the query??

    Thanks for the help.

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Going after your original query

    AND to_date('03:30 &&next_day', 'HH24:MI MM-DD-YY')
    goes to

    AND to_date('03:30 '||to_char(to_date(&&creation_date)+1),'HH24:MI MM-DD-YY')

    at least something like this should work.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    In your original script, change the query to:

    SELECT login, COUNT(DISTINCT sequence)
    FROM history
    WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
    AND to_date('03:30 &&creation_day', 'HH24:MI MM-DD-YY')+1
    GROUP BY login
    ORDER BY COUNT(DISTINCT sequence) DESC;

    That way you don't need the second date entered.

    In your second script, you should change the declaration part to:

    DECLARE
    v_next_day DATE := TO_DATE('&&creation_date,'MM-DD-YY') + 1;

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2001
    Posts
    59
    Thanks guys...why is it that it always seems so darn simple once you know the answer?

Posting Permissions

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



Click Here to Expand Forum to Full Width