-
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.
-
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'
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|