• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1627
  • Last Modified:

Oracle Select Query - Using Date-Time Parameters

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
dataman2004
Asked:
dataman2004
  • 6
  • 4
2 Solutions
 
MrNedCommented:
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
 
dataman2004Author Commented:
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
 
MrNedCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
dataman2004Author Commented:
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
 
SharathData EngineerCommented:
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
 
MrNedCommented:
Also try running this first in case something else has changed the default behaviour:

set define = &
0
 
dataman2004Author Commented:
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
 
dataman2004Author Commented:
Ned, I tried your solution and I get an error: "Invalid "set define" command
0
 
dataman2004Author Commented:
Additional Note: I am running this query in Oracle SQL-Developer
0
 
MrNedCommented:
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
 
dataman2004Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now