ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Problem Compiling Select Into with Fetch First @ target release V5R4Mo

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem Compiling Select Into with Fetch First @ target release V5R4Mo

    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.
    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??
    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,



  • #2
    I assume the last closing parenthesis is wrong it must be after :Bw2EndTime!
    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;
    IMHO the nested sub-seclect is not necessary. The following statement should return the expected result
    Code:
    Exec SQL Select 1 into :@tr_found
    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;
    Birgitta
    Last edited by B.Hauser; March 25, 2017, 03:51 AM.

    Comment


    • #3
      Thank You Birgitta!
      I feel foolish not seeing/finding/fixing this syntactical error on my own. I does compile TGTRLS V5R4M0 now.
      While i am being schooled here Please let me ask a couple of questions. (All Opinions from anyone appreciated. )
      My original syntax was trying to have the Fetch First Row Only apply to the "where exists sub-select" to make it run more efficiently.
      Now it looks like the Fetch First Row Only applies to the "Select Into" part of the statement. What is Fetch First it really doing now?

      The SPFTR file is a third party file that our customers ask that we use to develop custom applications against.
      This file is very very large and I only need to know if 1 record exists in a given timeframe. (there could be many records in the same timeframe.)
      I don't need any data from the record, only its existence matters.
      I am investigating the existing Access Paths on our customer's system so I can maximize the performance of this statement when it runs.

      Your example which removes the EXISTS predicate, brings back data I don't need, but if it runs faster then I would want to use it.
      Why do you think it runs better without the EXISTS predicate?
      The most important thing about the statement is detecting the existence of the record as fast as possible. @tr_found = 1 or 0.

      I appreciate any and all feedback and especially appreciate your correction to my syntax error Birgitta.

      Comment


      • #4

        Fetch First Row only returns only the first row (if at least one row is found).

        My Statement without the EXISTS only returns 1 if found (Nothing else). Not sure why you think you'll get data returned you don't want.
        Using an additional EXISTS implements a nested Sub-Select (an additional self-join) that performs worse than just reading the table once.

        Just run both statements (with and without EXISTS) through Visual Explain and compare the result.

        Birgitta

        Comment


        • #5
          Another way

          :@tr_found = 0 ;
          Exec SQL Select count(*) into :@tr_found 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 )); If :@tr_found > 0 ....

          Comment


          • B.Hauser
            B.Hauser commented
            Editing a comment
            Fetch First Row willl stop after the first row found. (Order by should be avoided in the SELECT statement ... otherwise the complete query might be executed)
            Count(*) always needs to read all rows before the result is returned. How else should the total number of rows be calculated?
            It does not matter if the table has only a few rows, but is makes a big difference if the table has a few millions of rows.
            Birgitta
        Working...
        X