Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    12

    Question Unanswered: open statement for dynamically prepared select statement in 4gl

    How can I open a declared cursor that is a dynamically prepared select statement behind it (different number of parameters for the using clause)?

    I built the open statement as a variable, now I need to 'run' it..
    (i.e.: depending on the criteria entered, the where clause of the prepared select statement can have 1 to 5 variables.. Can have any combination of the 5 criteria, so if a user wants all programs, there would be no 'and program = ?' clause. The full where clause is:where fy = ? and quarter = ? and program = ? and facility = ? and agency_code = ?. But as mentioned, it could have just 'where fy = ?'

    Does anyone have any ideas?


    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    I think that can you use built-in functions ARG_VAL(), NUM_ARGS().
    Exemp:
    DEFINE pa_args ARRAY[5] OF CHAR(10),
    i SMALLINT
    FOR i = 1 TO NUM_ARGS()
    LET pa_args[i] = ARG_VAL(i)
    END FOR


    Gustavo.

  3. #3
    Join Date
    Aug 2004
    Posts
    2

    Red face

    Hi,

    First,I think you need to form the null query strings like "1=1".
    Secondly,Add more query string like "and ?" if the input condition is not blank.
    Thirdly,prepare the sql statement using the query stings you created.

    I think that will work.

  4. #4
    Join Date
    Aug 2004
    Posts
    10
    You have to use the CONSTRUCT statement to dynamically prepare the WHERE clause. Then prepare that statement to declare the cursor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •