Solved

Oracle Select Query - Using Date-Time Parameters

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

808 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