Home » SQL & PL/SQL » SQL & PL/SQL » Need help with understanding sql format
Need help with understanding sql format [message #266742] Tue, 11 September 2007 09:31 Go to next message
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 #266748 is a reply to message #266742] Tue, 11 September 2007 09:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
californiagirl wrote on Tue, 11 September 2007 16:31
Hello,

Somewhat new with sql.

Then please post in the Newbie forum next time.
Re: Need help with understanding sql format [message #266753 is a reply to message #266748] Tue, 11 September 2007 09:51 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I am in the newbies forum. Look before you comment.

Can someone answer the question please.

Californiagirl
Re: Need help with understanding sql format [message #266756 is a reply to message #266753] Tue, 11 September 2007 09:58 Go to previous messageGo to next message
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 Go to previous message
skooman
Messages: 912
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.
Previous Topic: number datatype
Next Topic: "rows returned" versus "result set"
Goto Forum:
  


Current Time: Wed Dec 07 10:24:24 CST 2016

Total time taken to generate the page: 0.09734 seconds