On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, October 22, 2008

Display a Calendar and Prompt for a Date

The code for the calendar procedure was found on OTN and was created by Pablo Rovedo, DBA at ADEA S.A., in Buenos Aires, Argentina.

My script gets the current month and year, and passes it to the procedure. I then prompt the user for the day of the month. The goal of this is to run a report based on a date. If the users picks something later than the Sysdate, they are prompted with an error message (but the script doesn't really stop.)

In the above picture, the script is run twice. The first time, a report is returned; the second, an error message.

This is the test table I created:
Create Table Test
(Person Varchar2(10)
,Testdate Date
);
Insert Into Test Values('Diane' ,Sysdate-2);
Insert Into Test Values('Lynn' ,Sysdate-3);
Insert Into Test Values('Craig' ,Sysdate-4);
Insert Into Test Values('Denise',Sysdate-5);
Insert Into Test Values('Glenn' ,Sysdate-6);
Insert Into Test Values('Jacque',Sysdate-7);

The script:
--------------------------------------------------------------------
-- Get month and year to display this month's calendar
--------------------------------------------------------------------
Set Term Off Serveroutput On Verify Off
Column mm New_Value mm
Column yyyy New_Value yyyy

Select To_Char(Sysdate,'mm' ) mm,
To_Char(Sysdate,'yyyy') yyyy
From Dual;
Set Term On

exec calendar ('&mm','&yyyy');

--------------------------------------------------------------------
-- Explain the purpose of report and Get the day of month
--------------------------------------------------------------------

Prompt
Prompt This report displays records for a day.
Accept DOM Number Prompt 'Please choose the day: '

--------------------------------------------------------------------
-- DOM needs to be trimmed since it was defined as 'Number' in the
-- Accept statement.
-- Use case to set up for possible error msg. The prompt will either
-- display a message or it will display nothing
--------------------------------------------------------------------

Set Term Off
Column DateError New_Value DateError
Column Test_DOM New_Value Test_DOM

Select Ltrim(&DOM,' ') Test_DOM,
Case When &DOM > To_Char(Sysdate,'dd')
Then 'Error: Day must be today or before.'
Else ' '
End
As DateError
From Dual;

Set Term On
Prompt &DateError

--------------------------------------------------------------------
-- Select everything from our test table where the date matches the
-- date we put together. The table date will have to be
-- truncated to remove any time, and get a match
--------------------------------------------------------------------

Select *
From Test
Where To_Date('&Test_Dom'||'-'||'&mm'||'-'||'&yyyy','dd-mm-yyyy')
= Trunc(Testdate);

No comments:

Post a Comment