Needing help with a WHERE detail

[poolek3888]

Hello all. Thanks in advance for the help. My problem seemed like a simple one, but has turned into a big head ache.

I am basically selecting data in a certain time period that I allow the user to specify with the use of parameters. Now, in order to select that data, I ask for the user to input an ending date and ask how far back they would like to go. In my query, after SELECT, FROM, and WHERE, my query reads:

AND b.audit_timestamp between to_datemy_date,'MM-DD-YYYY') - INTERVAL '7' DAY and to_datemy_date,'MM-DD-YYYY')

You can see here that this will only go back 7 days. So I need a way to change "'7' DAY" into "'1' DAY" and "'1' MONTH." I figured I could make a new parameter for this and sub in the code I need, but it seems that wont work. I also checked out case and if statements, but they seem to only apply after SELECT and before FROM.

Do you have any advise for how I can make a WHERE statement be based on a variable?

Thanks all!
-Kyle

 

[PHV]

Using a CASE statement in the WHERE clause should be perfectly legal to calculate a VALUE (vs a keyword like, say, DAY)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[poolek3888]

Okay, now what about the number value that is '7' in my example. I need to change that as I change month to day. I'd also appreciate a short example of where the CASE would go in my code.

Thanks so much for the fast reply!

 

[PHV]

Assumptions: 3 parameters
1) a date (my_date)
2) a number (my_num)
3) an interval type (my_str)

AND b.audit_timestamp BETWEEN to_date(:my_date,'MM-DD-YYYY') - CASE :my_str
WHEN 'day' THEN INTERVAL :my_num DAY
WHEN 'month' THEN INTERVAL :my_num MONTH
END And to_date(:my_date,'MM-DD-YYYY')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[poolek3888]

Feels like it is really close now. But when I implemented the case into my code, I receive the error.

It highlights my first :num

Error: ORA-00905: missing keyword


AND b.audit_timestamp between to_datemy_date,'MM-DD-YYYY') - case eriod
when 1 then INTERVAL :num DAY
when 2 then INTERVAL :num DAY
when 3 then INTERVAL :num Month
end and to_datemy_date,'MM-DD-YYYY'))

 

[PHV]

And what about this ?
AND b.audit_timestamp between to_datemy_date,'MM-DD-YYYY') - :num * case eriod
when 1 then INTERVAL '1' DAY
when 2 then INTERVAL '1' DAY
when 3 then INTERVAL '1' Month
end and to_datemy_date,'MM-DD-YYYY'))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[poolek3888]

Getting closer! I now receive a new error:
ORA-00933: SQL command not properly ended

It points to my last statement in my WHERE (not in the code presented in this thread) which I know works correctly when I comment out the recent code added. So it is forcing an error. I figure it may be because I don't know what I should input for my :num value now. And would you please quickly explain what the added * does?

Thanks so much.

 

[PHV]

Could you please post the whole WHERE clause ?
I suspect a spurious closing parenthesis.

:num is supposed to be the number of intervals you want to subtract from :my_date.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[poolek3888]

WHERE a.correlation_id IN (
SELECT b.correlation_id
FROM audit_service b
WHERE b.source_application = 'text'
AND b.class_name = 'text'
AND b.operation_name = 'text'
AND b.audit_timestamp between to_date(to_charmy_date,'MM-DD-YYYY'),'MM-DD-YYYY') - :num * case eriod
when 1 then INTERVAL '1' DAY
when 2 then INTERVAL '1' DAY
when 3 then INTERVAL '1' Month
end AND to_date(to_charmy_date,'MM-DD-YYYY'), 'MM-DD-YYYY'))
AND (b.ERROR_CODE LIKE '12__'
OR b.ERROR_CODE in (1305,1310)))

 

[poolek3888]

Woah! there it is! I had two )) where there should have been one as you expected.

 

[poolek3888]

I now receive a new error which highlights my INTERVAL:

Error: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

I tried out putting INTERVAL before :num and taking it out of the case statement, but that only led to another error involving :num.

 

[PHV]

I wonder why you use a subquery instead of a join ...

 

[poolek3888]

Well, I am actually just modifying code that another had set up for me. The original was:

WHERE a.correlation_id IN (
SELECT b.correlation_id
FROM audit_service b
WHERE b.source_application = 'text'
AND b.class_name = 'text'
AND b.operation_name = 'text'
AND b.audit_timestamp between to_datedate_begin,'MM-DD-YYYY') and to_datedate_end,'MM-DD-YYYY')
AND (b.ERROR_CODE LIKE '12__'
OR b.ERROR_CODE in (1305,1310)))

I'll scope out the join feature. Do you feel this will help with the new Interval problem?

 

[poolek3888]

Okay, heading home from work. I'll be able to view this again in an hour or so. Thanks again.

 

[PHV]

Do you feel this will help with the new Interval problem?
No.

I'm afraid you have to consult your Oracle manual to discover how to make DateTime arithmetics as it seems to choke on code I believe ANSI compliant.