SQL Express Error, Please advise

@ronhu 

I think the error is telling you what it needs -- the expression needs to be a date result.

If I run the expression you have in a SELECT statement:

SELECT CASE EXTRACT(DAY from sysdate) AS Expression FROM Dual

this statement does not even run. 

If I use this expression:

SELECT EXTRACT(DAY from sysdate) AS Expression FROM Dual

it runs and returns an integer representing the current day of the current month. The result is not a date, though.

Finally, if I do this:

SELECT TRUNC(sysdate) AS Expression FROM Dual

I get today’s date back (time portion will be zeroed out due to the TRUNC).

What result do you want to get from the expression? That is, what date do you want to return (and it must be a date type according to the text on the screen and the error you are receiving).

Thanks,

Joe Kaufman

@sutekh137 thank you for your input.

my bad, i only list the first SQL script, it is a list of SQL scripts. after I copied them all into the expression field, system shows it is a valid one.

@ronhu 

So what is the full expression? And when you say it is a valid one, are you saying IFS states it is valid, but it errors out when it runs?

I assumed you were seeing the error when you click “Check”?

If I can have the full expression I can play around with a scheduled task and see if I get the same error. But I need the expression and need to know exactly what works and what doesn’t work.

Thanks,

Joe Kaufman

here is the full.

since i only copy the first line, hence the error. yes, I am using the check button. I am replicating one schedule to another.

case extract(DAY from sysdate)  
when 1
then sysdate + interval '3' day
when 2
then sysdate + interval '2' day 
when 3
then sysdate + interval '1' day
else sysdate +  interval '15' minute
end
 

@ronhu 

I copied the expression into a new test scheduled task, it checked successfully, then saved successfully.

The next run time was 15 minutes into the future, as the expression would dictate. I even changed the minutes to 25 and after I saved, the next execution time was 25 minutes into the future.

So, it is working for me. Are you sure some weird characters didn’t get in the expression when you copied it from another schedule? Can you paste the expression into a text editor that can display unprintable characters and make sure nothing strange is in there, such as a tilde instead of a single quote?

Alternately, copy the text from your post above and paste it in again. Doing that worked for me, so I am not sure why it wouldn’t work for you… We are on Apps 10, Update 15, I believe.

Thanks,

Joe Kaufman

@sutekh137 thank you. I think i missed the “End”. just fixed the issue. thank you for your time anyway.

Good deal! I wish they would let that expression fiend be a little bigger, it is hard to make sure what is in there.

Glad it is working now!

Joe Kaufman