Solved

Oracle Select Query - Using Date-Time Parameters

Posted on 2011-03-03
11
1,585 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
 

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
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.

 
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.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now