Home » SQL & PL/SQL » SQL & PL/SQL » displaying date in reports using SQL*PLUS
displaying date in reports using SQL*PLUS [message #188630] Mon, 21 August 2006 01:44 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I want to display SYSDATE in TTITLE using SQL*PLUS.

I searched on net but couldn't found much documentation with good examples on creating Reports using SQL*PLUS. I have to convert Crystal Reports to Reports using SQL*PLUS. There are some complex reports too.
Please advice.

Thanks,
Mona
Re: displaying date in reports using SQL*PLUS [message #188640 is a reply to message #188630] Mon, 21 August 2006 02:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
COLUMN sd NOPRINT NEW_VALUE sd
TTITLE &sd

SELECT to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') AS sd,
... whatever ...


The COLUMN command does two things:
- NOPRINT means any column by that name (or aliased to that name) will not display in the regular output of the SQL
- NEW_VALUE places the contents of column SD into a SQL*Plus replacement variable &SD, which you can then use in your title and footer.

Ross Leishman
Re: displaying date in reports using SQL*PLUS [message #188642 is a reply to message #188640] Mon, 21 August 2006 02:15 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Ross,
As I told that I have to convert complex Crystal Reports to SQL*PLUS report, can you help me getting some good documentation with examples.

Thanks again,
Mona
Re: displaying date in reports using SQL*PLUS [message #188646 is a reply to message #188642] Mon, 21 August 2006 02:20 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Ross,
When I executed the file it asks for the value of the paramater &sd. Can't I display System Date automatically and I do not have to enter the date.

Thanks,
MOna
Re: displaying date in reports using SQL*PLUS [message #188661 is a reply to message #188646] Mon, 21 August 2006 02:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry, drop the '&'.

Check out the SQL*Plus doco.
Re: displaying date in reports using SQL*PLUS [message #188690 is a reply to message #188661] Mon, 21 August 2006 04:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks rleishman.

I am looking for a good example for creating report that involves grouping up to two levels and several other things. But the docuent was not of much help. Can I get a good example in the same regard?
Thanks,
Mona
,
Re: displaying date in reports using SQL*PLUS [message #188692 is a reply to message #188690] Mon, 21 August 2006 04:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The O'Reilly book Oracle SQL*Plus is a very comprehensive guide with a lot of worked examples.
Re: displaying date in reports using SQL*PLUS [message #188695 is a reply to message #188692] Mon, 21 August 2006 04:26 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

But this is not for free Sad
Re: displaying date in reports using SQL*PLUS [message #188799 is a reply to message #188630] Mon, 21 August 2006 13:37 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
use good old google. Search on "ORACLE REPORTS EXAMPLES". 16 million links are found, check the first page.
Previous Topic: update using group by
Next Topic: to_date problem
Goto Forum:
  


Current Time: Sun Dec 04 18:33:29 CST 2016

Total time taken to generate the page: 0.13490 seconds