Hi, I picked up the technique of using Select Into along with Where exists from the fine folks who post here. I implemented it and it works, will show you.
Here is some background on my problem. System at V7R1 but all our work is compiled target release V5R4M0 to make sure it restores and runs anywhere.
The code snip.
Error message is : SQL7906 Position 30 SQL function not supported on prior release. Function is - FETCH.
Cause . . . . . : The *PRV or VxRxMx value was specified for the Target Release (TGTRLS) parameter on the precompile command or for the SET OPTION statement. The created program cannot be saved and restored to that release because an SQL function is not supported on the target release.
Recovery . . . : The program is compiled with a severity level 30 message in the precompile listing for each SQL statement containing a function which cannot be restored, and the program object is marked as not being able to be restored. To avoid the messages, change the value on the TGTRLS parameter to the *CURRENT value or change the program so every SQL statement contains only the functions that are supported on the release to which you want to restore.
If the SET OPTION statement is used for a routine, the routine will not create unless all SQL statements within the routine are supported on the release specified for the TGTRLS value.
I have tried syntactical changes to the Fetch First.. 1 row only for example. but same result. Drop the fetch first and it compiles and runs fine at target V5R4M0
The thing is, the V5 SQL Reference has the Select Into with fetch first clause well documented. It also has the Exists predicate documented. It seems everything IS supported. Does Anyone have any suggestions as to how to submit the compile so it will compile at V5R4M0?
Thanks,
Here is some background on my problem. System at V7R1 but all our work is compiled target release V5R4M0 to make sure it restores and runs anywhere.
The code snip.
Code:
Exec SQL Select 1 into :@tr_found From SFPTR Where Exists (Select * From SFPTR Where CPIDTR = :CPIDTR AND PRIDTR = :PRIDTR AND CRNOTR = :CRNOTR AND TRIDTR = :TRIDTR AND (( DHYRTR = :Bw1StrDHYR AND TIMETR between :Bw1StrTIME AND :Bw1EndTIME ) OR ( DHYRTR = :Bw2StrDHYR AND TIMETR between :Bw2StrTime AND :Bw2EndTIME )) Fetch First Row Only); // Not support in prior release??
Cause . . . . . : The *PRV or VxRxMx value was specified for the Target Release (TGTRLS) parameter on the precompile command or for the SET OPTION statement. The created program cannot be saved and restored to that release because an SQL function is not supported on the target release.
Recovery . . . : The program is compiled with a severity level 30 message in the precompile listing for each SQL statement containing a function which cannot be restored, and the program object is marked as not being able to be restored. To avoid the messages, change the value on the TGTRLS parameter to the *CURRENT value or change the program so every SQL statement contains only the functions that are supported on the release to which you want to restore.
If the SET OPTION statement is used for a routine, the routine will not create unless all SQL statements within the routine are supported on the release specified for the TGTRLS value.
I have tried syntactical changes to the Fetch First.. 1 row only for example. but same result. Drop the fetch first and it compiles and runs fine at target V5R4M0
The thing is, the V5 SQL Reference has the Select Into with fetch first clause well documented. It also has the Exists predicate documented. It seems everything IS supported. Does Anyone have any suggestions as to how to submit the compile so it will compile at V5R4M0?
Thanks,
Comment