Solved

Oracle Select Query - Using Date-Time Parameters

Posted on 2011-03-03
11
1,594 Views
Last Modified: 2012-05-11
Hello Experts. I have a union query that is run daily that requires the updating of the same 2 variables 16 times each. This is bothersome and subject to errors over time. Can you advise as to the best way to address this.

The approach that occurred to me was to simply pass the first date in as Begin_Date and pass the second date in as End_Date.

Here's a basic example of the type of union query in which the ability to pass parameters would be helpful:

select * from(
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '02-MAR-11 12:00:00 AM' -- Begin Date Variable here
AND created_at   < '03-MAR-11 12:00:00 AM')  -- End Date Variable here
and source = 'Eastern'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '02-MAR-11 12:00:00 AM' -- Begin Date Variable here    
AND created_at   < '03-MAR-11 12:00:00 AM') -- End Date Variable here  
and source = 'Western'))
order by status, source desc;

In trying to do this I have toyed with substitution variables, but these don't seem to work well with my union query. The query simply does not run. Also, when running this, though I defined the Begin and End dates in the query, I still get prompted for the dates when I run the query.

Here's an example of that attempt:

define varDateBegin = '02-MAR-11 12:00:00 AM'
define varDateEnd = '03-MAR-11 12:00:00 AM'

select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > :&varDateBegin    
AND created_at   < :&varDateEnd)  
and source = 'BSHS_MISYS'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > :&&varDateBegin    
AND created_at   < :&&varDateEnd)  
and source = 'Western'

In short, I am looking for an efficient way to simply provide the user with the ability to change the Begin and End dates, once each every morning, within the query/sql, preferably without a prompt.  (Entering the dates in the SQL is preferred as it will be in date-time format and mistakes in typing are a concern.)

One last note, I don't have the privileges to write tables to the database.
0
Comment
Question by:dataman2004
  • 6
  • 4
11 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35032610
Dont put the : before the variable names:

define varDateBegin = '02-MAR-11 12:00:00 AM'
define varDateEnd = '03-MAR-11 12:00:00 AM'

select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > &varDateBegin    
AND created_at   < &varDateEnd)  
and source = 'BSHS_MISYS'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > &varDateBegin    
AND created_at   < &varDateEnd)  
and source = 'Western'
0
 

Author Comment

by:dataman2004
ID: 35033272
Ned, I followed your advice and ran the query after deleting the ":" before the variable names. But, when I ran the modified query, I was prompted for the parameter values anyway, and after I enter them in the prompts, I am then prompted to enter a Bind value; specifically with a name of "00". Can you advise how I can get to the point where I can simply assign the desired dates to the 2 date variables and pass those to the query?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35033300
Ah I see, you also need to quote the variables.

select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&varDateBegin'    
AND created_at   < '&varDateEnd')  
and source = 'BSHS_MISYS'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&varDateBegin'    
AND created_at   < '&varDateEnd')  
and source = 'Western'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dataman2004
ID: 35061927
I changed the query as follows, but still get prompted for both variables, 2 times each.

define varDateBegin = '02-MAR-11 12:00:00 AM'
define varDateEnd = '03-MAR-11 12:00:00 AM'

select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&varDateBegin'    
AND created_at   < '&varDateEnd')  
and source = 'Eastern'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&varDateBegin'    
AND created_at   < '&varDateEnd')  
and source = 'Western'
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 35063198
try with &&
define varDateBegin = '02-MAR-11 12:00:00 AM'
define varDateEnd = '03-MAR-11 12:00:00 AM'

select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&&varDateBegin'    
AND created_at   < '&&varDateEnd')  
and source = 'Eastern'
Union all
select Created_at, source, status, message_id
from w_syn_messages
WHERE (created_at > '&&varDateBegin'    
AND created_at   < '&&varDateEnd')  
and source = 'Western'

Open in new window

0
 
LVL 7

Expert Comment

by:MrNed
ID: 35063458
Also try running this first in case something else has changed the default behaviour:

set define = &
0
 

Author Comment

by:dataman2004
ID: 35073207
Sharath, this works, for the most part, the first time I run it. When I do run it, it disregards the date parameters that I put in the "define" part of the statement. When I run the query, I do get prompted for the begin-date and end date parameters, regardless of what I put in the "define" part of the statement. It does return accurate data based on that.

When I try to run the query a second time, it runs, but returns nothing. To run it again, I have to close out the query entirely and re-open it. Is there a practical way to clear out the variables perhaps?
0
 

Author Comment

by:dataman2004
ID: 35073217
Ned, I tried your solution and I get an error: "Invalid "set define" command
0
 

Author Comment

by:dataman2004
ID: 35073226
Additional Note: I am running this query in Oracle SQL-Developer
0
 
LVL 7

Assisted Solution

by:MrNed
MrNed earned 250 total points
ID: 35074898
The define command is for sql*plus, and some other tools specifically designed to recognise it. I'm not familiar with SQL-Developer so can't say if it should work there or not!
0
 

Author Comment

by:dataman2004
ID: 35147570
OK, I think what I need to do is get an ODBC connection so that I can use SQL-Plus. Your comments have been helpful. I can get some of what I am looking for in SQL-Developer using your suggestions, but think that I will be far better off using SQL-Plus for what I am trying to accomplish. Thanks for your input.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question