Requires Free Membership to View
"Jan-2005" or "Nov-2004 to Feb-2005".
If you were willing to edit a script every time you had to do this sort of query, you might write a script like this:
-- res_dt.sql -- Find reservations made in or between
-- two given months.
-- Dates are hard-coded in this script. To do a query
-- for any two months, you have to change two lines below.
-- (Look for the comments on the right-hand side.)
-- Note: the first month IS included, the second one
-- IS NOT.
SELECT *
FROM reservation
WHERE made_dt >= TO_DATE ( '01-Nov-2004'
-- ^^^^^^^^ <== Change above
, 'DD-Mon-YYYY'
)
AND made_dt < TO_DATE ( '01-Mar-2005'
-- ^^^^^^^^ <== Change above
, 'DD-Mon-YYYY'
)
ORDER BY made_dt
, id;
There are three main problems with this:
-
You have to edit a file (typing the months in question) and then say
"
@res_dt" in SQL*Plus. It would be much easier to just say something like "@res_dt Nov_2004 Feb-2005" - You can't let users do this themselves. Someone is bound to make a editing mistake that will ruin the script. Also, two users trying to use it at the same time would get in each other's way (unless they had their own copies).
- Instead of typing the last month you want, you have to type the first month you don't want. That's confusing, plus you have to figure it out.
We can solve problems 1 and 2 by using SQL*Plus substitution variables
to pass the variable parts of the script. When SQL*Plus parses the script, it
will substitute the value of the first parameter (from the command that
called the script) for all occurrences of the string &1, and
it will replace all occurrences of the string &2 with the
second parameter. SQL*Plus shows exactly what it is doing by printing each line
that includes substitution variables before and after making the substitutions,
like this:
old 3: WHERE made_dt >= TO_DATE ( '01-&1' new 3: WHERE made_dt >= TO_DATE ( '01-Nov-2004'The line labeled "new" is what SQL*Plus will execute.
Problem 3 can be solved by using the ADD_MONTHS function.
Making these changes, we have:
-- res_dt.sql -- Find reservations made in or between
-- two given months.
-- Call this script with two Mon-Year arguments,
-- like this:
-- @res_dt Nov-2004 Feb-2005
-- All reservations made in either month, or
-- in months between them, will be shown.
SELECT *
FROM reservation
WHERE made_dt >= TO_DATE ( '01-&1'
, 'DD-Mon-YYYY'
)
AND made_dt < ADD_MONTHS ( TO_DATE ( '01-&2'
, 'DD-Mon-YYYY'
)
, 1
)
ORDER BY made_dt
, id;
This script takes two arguments.
To run this script for a single month, you still have to pass
two arguments, like "@res_dt Jan-2005 Jan-2005".
This will produce the correct results, but it's not very convenient.
Using optional arguments in a SQL*Plus script is a separate question.
This was first published in February 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation