Home » SQL & PL/SQL » SQL & PL/SQL » Automatic Value Passing to WHERE Clause...
icon9.gif  Automatic Value Passing to WHERE Clause... [message #387077] Tue, 17 February 2009 19:48 Go to next message
aimy
Messages: 209
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
Re: Automatic Value Passing to WHERE Clause... [message #387080 is a reply to message #387077] Tue, 17 February 2009 20:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
WHERE TO_CHAR (start_time, 'DD/MM/YY') = NVL ('&date', TO_CHAR (SYSDATE, 'DD/MM/YY'))

Re: Automatic Value Passing to WHERE Clause... [message #387081 is a reply to message #387077] Tue, 17 February 2009 20:11 Go to previous messageGo to next message
BlackSwan
Messages: 25043
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.
Re: Automatic Value Passing to WHERE Clause... [message #387082 is a reply to message #387080] Tue, 17 February 2009 20:37 Go to previous messageGo to next message
aimy
Messages: 209
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 Embarassed Embarassed

Thanks a lot Barbara.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif
icon7.gif  Re: Automatic Value Passing to WHERE Clause... [message #387083 is a reply to message #387081] Tue, 17 February 2009 20:38 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Wed, 18 February 2009 10:11
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.

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.. http://img01.picoodle.com/img/img01/3/11/1/aimy/f_tqm_bcc85fd.gif
icon7.gif  Re: Automatic Value Passing to WHERE Clause... [message #387084 is a reply to message #387081] Tue, 17 February 2009 20:40 Go to previous message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Wed, 18 February 2009 10:11
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.

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

Thank you.
Previous Topic: Numbering groups of consecutive dates
Next Topic: How know the tables on which I have select access
Goto Forum:
  


Current Time: Wed Dec 07 08:26:00 CST 2016

Total time taken to generate the page: 0.11343 seconds