Solved

Oracle Select Query - Using Date-Time Parameters

Posted on 2011-03-03
11
1,601 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 41

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle DR - data guard failover. 18 70
Oracle Insert not working 10 68
oracle sqlplus query delimiter 8 50
Oracle function return value when null 2 32
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 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