Need help with understanding sql format [message #266742] |
Tue, 11 September 2007 09:31  |
californiagirl
Messages: 79 Registered: May 2007
|
Member |
|
|
Hello,
Somewhat new with sql. I am reading a script that I have to modify, but the previous person who wrote the sql*plus script has err_msg in his script that I don't understand what it's purpose serves. Any advice or explaination to help me understand will do.
--Format columns
col start_date noprint new_value s_date
col end_date noprint new_value e_date
col err_msg noprint new_value err_message
col sort_col noprint
SELECT s.start_date,
e.end_date,
decode(sign(e.end_date - s.start_date),
-1,'*** Note: From Period is later than To Period, no expenditures reported ***') err_msg
FROM apps.pa_periods s,
apps.pa_periods e
WHERE s.period_name(+) = '##1'
and e.period_name(+) = '##2';
set termout on
Thanks
Californiagirl
[Updated on: Tue, 11 September 2007 10:26] by Moderator Report message to a moderator
|
|
|
|
|
Re: Need help with understanding sql format [message #266756 is a reply to message #266753] |
Tue, 11 September 2007 09:58   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your post is in the Newbies forum because Frank moved it here. He left the message as a comment to let you know what had happened.
As a general rule for posting on OraFAQ, people who come across as aggressive and demanding frequently find their posts going unanswered.
The NEW_VALUE syntax in SQL*Plus is used to load a value from a query straight into a substitution variable.
You'll find that if you refer to &&err_message in the script after this select it will hold te results of tat DECODE statement.
|
|
|
Re: Need help with understanding sql format [message #266830 is a reply to message #266756] |
Tue, 11 September 2007 13:21  |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
decode(sign(e.end_date - s.start_date),
-1,'*** Note: From Period is later than To Period, no expenditures reported ***') err_msg
In English, this says:
add one column, named err_msg, to the result I get and if end_date is before start_date then show *** Note: (etc) *** in that column, otherwise show nothing in that column.
If you lookup decode and sign in the SQL reference, you'll be able to figure it out.
|
|
|