Re: Syntax question - how do you continue to the next line on a load lookup where clause?

[Ed Kelly <ed_kelly@HOTMAIL.COM>]

You cannot continue a string literal on multiple lines.  To accomplish what
you are trying to do with the load-lookup command, you have to basic
approaches.  The approach you choose will probably be influenced by which
section of your SQR program is issuing the Load-Lookup command.

1.  Use separate substitution variables to define each line of the where
clause.  Create another Substitution variable to string together all of the
line.  Use the last substitution variable in your load-lookup where clause.
For example:

    #Define L1 EFFDT = (SELECT MAX(A_ED.EFFDT)
    #Define L2            FROM PS_SUBCUST_Q1_TBL A_ED
    #Define L3           WHERE SETID = A_ED.SETID AND
    #Define L4             SUBCUST_QUAL1 = A_ED.SUBCUST_QUAL1 AND
    #Define L5             A_ED.EFFDT <= SYSDATE) AND
    #Define L6 SETID = 'CIS00'

    #Define MyWhere {L1} {L2} {L3} {L4} {L5} {L6}

    load-lookup
      name=RGN-DESCR
      table=PS_SUBCUST_Q1_TBL
      key=SUBCUST_QUAL1
      return_value=DESCR
      where='{MyWhere}'

Note: This method can be used in any program section.  Using this method,
your Where clause cannot exceed the SQR line limit (256 characters).


2.  Use a string variable for your where clause.  Each line of the where
clause can be written as a string literal which is concatenated with the
rest.  Use the string variable in your load-lookup where clause.  For
example:

  Let $MyWhere = 'EFFDT = (SELECT MAX(A_ED.EFFDT) ' ||
                 'FROM PS_SUBCUST_Q1_TBL A_ED ' ||
                 'WHERE SETID = A_ED.SETID AND ' ||
                 'SUBCUST_QUAL1 = A_ED.SUBCUST_QUAL1 AND ' ||
                 'A_ED.EFFDT <= SYSDATE) AND ' ||
                 'SETID = ''CIS00'''

    load-lookup
      name=RGN-DESCR
      table=PS_SUBCUST_Q1_TBL
      key=SUBCUST_QUAL1
      return_value=DESCR
      where=$MyWhere    ! Not [$MyWhere]

Note: This method can be used in any program section EXCEPT the Setup
section.  Using this method, your Where clause cannot exceed the SQR string
variable limit (3000+ characters).

Hope this helps.

Ed Kelly


>From: Debra Benton <debra.benton@NPAC.COM>
>Reply-To: SQR-USERS@list.iex.net
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>Subject: Syntax question - how do you continue to the next line on a load
>            lookup where clause?
>Date: Thu, 8 Jul 1999 12:44:28 -0500
>
>Hopefully this is an easy syntax question:
>
>My load-lookup has a lengthy where clause that I would like to put on
>several lines to make it easier to read in the future.  How can I continue
>on the next line?  I've tried the underscore at the end of the line with no
>success.
>
>Thanks,
>Debbie
>
>load-lookup
>   name=RGN-DESCR
>     table=PS_SUBCUST_Q1_TBL
>     key=SUBCUST_QUAL1
>     return_value=DESCR
>     where='EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SUBCUST_Q1_TBL A_ED
>WHERE
>SETID = A_ED.SETID AND SUBCUST_QUAL1 = A_ED.SUBCUST_QUAL1 AND A_ED.EFFDT <=
>SYSDATE) AND SETID = ''CIS00'''


_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com