OraFAQ Forum: SQL & PL/SQL » Automatic Value Passing to WHERE Clause...

Home » SQL & PL/SQL » SQL & PL/SQL » Automatic Value Passing to WHERE Clause...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Automatic Value Passing to WHERE Clause... [message #387077]

Tue, 17 February 2009 19:48

aimy
Messages: 225
Registered: June 2006

Senior Member

How could I extend this sql statement in such a way that it will automatically pass the current date to the where clause if the prompt value was set to NULL?

SELECT 
START_TIME , END_TIME , PROCEDURE_NAME , TABLE_NAME , ROWS_INSERTED "INSERTED" , ROWS_UPDATED "UPDATED" , 
ROWS_DELETED "DELETED" , ROWS_INSERTED_OF_DELETION "INS_OF_DEL" , ROWS_INSERTED_AFTER_DELETION "INS_AFT_DEL" , 
ROWS_REJECTED "REJECTED" , ELAPSE_TIME , -- REMARKS , 
case when SUBJECT_AREA = 'SERVICE PROFILE, SERVICE PROFILE CHARGES' then 'SRV PRO'||' & '||'CHG' 
else SUBJECT_AREA end SUBJECT_AREA 
FROM PROCEDURE_TRACKING_LOG 
where 
to_char(START_TIME, 'DD/MM/YY') = '&date'
ORDER BY START_TIME;

Thank you very much.

http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif

Report message to a moderator

Re: Automatic Value Passing to WHERE Clause... [message #387080 is a reply to message #387077]

Tue, 17 February 2009 20:10

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

WHERE TO_CHAR (start_time, 'DD/MM/YY') = NVL ('&date', TO_CHAR (SYSDATE, 'DD/MM/YY'))

Report message to a moderator

Re: Automatic Value Passing to WHERE Clause... [message #387081 is a reply to message #387077]

Tue, 17 February 2009 20:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

What Operating System (OS) name & version?
What Oracle version to 4 decimal places.

>if the prompt value was set to NULL?
You question implies you are using sqlplus as the application user interface. BAD idea!

If the user inputs 03/04/05, what date did the USER mean?
2003-Apr-05
2005-Mar-04
2005-Apr-03

Keep in mind you can NOT control what the user enters!
When you intend to compare DATE you should be using TO_DATE!

>such a way that it will automatically pass the current date
You'll need to do this at the OS level.

Report message to a moderator

Re: Automatic Value Passing to WHERE Clause... [message #387082 is a reply to message #387080]

Tue, 17 February 2009 20:37

aimy
Messages: 225
Registered: June 2006

Senior Member

Barbara Boehmer wrote on Wed, 18 February 2009 10:10

WHERE TO_CHAR (start_time, 'DD/MM/YY') = NVL ('&date', TO_CHAR (SYSDATE, 'DD/MM/YY'))

Ahahaha.. what a big shame for me.. Embarassed

Thanks a lot Barbara..

Report message to a moderator

Re: Automatic Value Passing to WHERE Clause... [message #387083 is a reply to message #387081]

Tue, 17 February 2009 20:38

aimy
Messages: 225
Registered: June 2006

Senior Member

BlackSwan wrote on Wed, 18 February 2009 10:11

Thanks for your comments.

But sorry sir, actually that is only for my perusal to check the tracking log of procedure execution, that's all.

Therefore, Barbara's answer is exactly what I've been looking for.

Thank you..

Report message to a moderator

Re: Automatic Value Passing to WHERE Clause... [message #387084 is a reply to message #387081]

Tue, 17 February 2009 20:40

aimy
Messages: 225
Registered: June 2006

Senior Member

BlackSwan wrote on Wed, 18 February 2009 10:11

Anyway, at least you've got some points there which I've been overlooked all this while.

Thank you.

Report message to a moderator

Previous Topic:	Numbering groups of consecutive dates
Next Topic:	How know the tables on which I have select access

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 00:33:04 CST 2024