Home » SQL & PL/SQL » SQL & PL/SQL » Variables in SQL Query (Oracle 10.2.0.1.0)
Variables in SQL Query [message #423857] Mon, 28 September 2009 13:48 Go to next message
myorafaq
Messages: 3
Registered: September 2009
Junior Member
Hi,

I have a sql query like this that will have variables:

select
* from AUDIT_RUN_HISTORY
where
(ENT_TIME - START_TIME) BETWEEN &LOWER_LIMIT AND &UPPER_LIMIT;

My req is a) if i didn't give a value for LOWER_LIMIT then it has to take 0 value as LOWER_LIMIT.
b) if i didn't give a value for UPPER_LIMIT then it has to take only LOWER_LIMIT value.

I need to do this in SQl Query only.Any help is appreciated.

Thanks
Sekar
Re: Blank value to Variables in SQL Query [message #423860 is a reply to message #423857] Mon, 28 September 2009 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NVL

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 28 September 2009 14:14]

Report message to a moderator

Re: Blank value to Variables in SQL Query [message #423862 is a reply to message #423857] Mon, 28 September 2009 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>b) if i didn't give a value for UPPER_LIMIT then it has to take only LOWER_LIMIT value.

BETWEEN requires 2 expressions separated by AND
An error results when only a single expression exists
Varaible in Where condition of SQl Query [message #423867 is a reply to message #423857] Mon, 28 September 2009 16:28 Go to previous messageGo to next message
myorafaq
Messages: 3
Registered: September 2009
Junior Member

I have a SQL query like this:

select
* from Job_Table
where
(ENT_TIME - START_TIME) >= &LOWER_LIMIT;

Here is my Req:

a) if i didn't give a value for LOWER_LIMIT then it has to take 0 value as LOWER_LIMIT.

b) if i have given a value for LOWER_LIMIT then it has to take that value for LOWER_LIMIT.

Thanks for looking
Sekar
Re: Varaible in Where condition of SQl Query [message #423869 is a reply to message #423867] Mon, 28 September 2009 16:36 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
Run following if you dont give the date parameter (which is null) it will take 0 as default

select SYSDATE
from dual
WHERE sysdate-(sysdate)>=DECODE(:DATE_PARAM,NULL,0,:DATE_PARAM)

which is

select
* from Job_Table
where
(ENT_TIME - START_TIME) >= DECODE(:LOWER_LIMIT,null,0,:LOWER_LIMIT)

on your query.

[Updated on: Mon, 28 September 2009 16:37]

Report message to a moderator

Re: Varaible in Where condition of SQl Query [message #423878 is a reply to message #423869] Mon, 28 September 2009 23:23 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
decode(X,null,Y,X) is nvl(X,Y), simplify your expressions.

Regards
Michel
Previous Topic: Convert 1/1/1900 to NULL
Next Topic: Drop all table under a user without dropping user
Goto Forum:
  


Current Time: Mon Sep 26 11:11:44 CDT 2016

Total time taken to generate the page: 0.11587 seconds