Re: Select depending on...

[Manoj Gurjar <mgurjar@HOTMAIL.COM>]

! Use dynamic variable substitution method:
!eg:

if $NIVEAU = '2'
   let $dynamic_where = ' CREATOR = ' || ''''$CODE''''
end-if
if $NIVEAU = '4'
   let $dynamic_where = ' TYPE = ' || ''''$CODE''''
end-if

! And in your select, see where condition

Begin-Procedure Select
DISPLAY '<'
Begin-Select
GRANTEE &t_grantee
  DISPLAY &t_grantee NOLINE
  DISPLAY ',' NOLINE
CREATOR &t_creator
  DISPLAY &t_creator NOLINE
  DISPLAY ',' NOLINE
NAME &t_name
  DISPLAY &t_name NOLINE
  DISPLAY ',' NOLINE
TYPE &t_type
  DISPLAY &t_type
>From Q.AUTH_LIST
[$dynamic_where]
End-Select


! Hope this Helps,
! Manoj


>From: Per Ericsson <perifras@YAHOO.COM>
>Reply-To: sqr-users@list.iex.net
>To: SQR-USERS@list.iex.net
>Subject: Select depending on...
>Date: Tue, 14 Dec 1999 01:43:51 -0800
>
>Hello everybody!
>
>I'm working on a SQR procedure with the aim to search values for
>combo-boxes on a webpage. We have six combo-boxes with values fetched
>from a DB2-database. The user can choose a value from one of
>combo-boxes
>(of his/her free choice) and an SQR-procedure is then called by a
>Java
>servlet with two parameters given: CODE and NIVEAU.
>The CODE represents the value hosen by the user in one of the
>combo-boxes and the NIVEAU represents the combo-box (each combo-box
>represents a field in the database table).
>The SQR procedure is supposed to fetch the values for the other
>combo-boxes based upon the CODE hosen y the user. This is done by a
>simple SELECT statement with a WHERE clause. Today, I have solved
>this
>by creating a new SELECT statement for each WHERE clause, but I would
>like to make one select statement and define the field in the
>WHERE clause beforehand.
>
>Does anybody know how to do this?
>I've tried "#DEFINE" as in the example below, but it didn't work.
>Moreover, I tried to use "$" or "#" variables without success.
>
>Thanks for any kind of help!
>
>Per Eriksson
>
>This is how it is done today:
>-----------------------------------------------------------------------
>Begin-Program
>  Do Get-Input
>  Do Main
>End-Program
>
>Begin-Procedure Get-Input
>!--------------------
>Input $CODE 'Enter a CODE' MaxLen=10 Type=Char
>If IsNull($CODE) or IsBlank($CODE)
>!  Show 'Input Error (7734): No value was entered. A value is
>required.'
>   Let #return-status = 7734
>   Stop
>End-If
>!--------------------
>Input $NIVEAU 'Enter a NIVEAU' MaxLen=4 Type=Char
>If IsNull($NIVEAU) or IsBlank($NIVEAU)
>!  Show 'Input Error (7734): No value was entered. A value is
>required.'
>   Let #return-status = 7734
>   Stop
>End-If
>End-Procedure !Get-Input
>
>Begin-Procedure Main
>If $NIVEAU = '2'
>   Do Creator
>End-If
>If $NIVEAU = '4'
>   Do Type
>End-If
>End-Procedure !Main
>
>Begin-Procedure Creator
>DISPLAY '<'
>Begin-Select
>GRANTEE &t_grantee
>  DISPLAY &t_grantee NOLINE
>  DISPLAY ',' NOLINE
>CREATOR &t_creator
>  DISPLAY &t_creator NOLINE
>  DISPLAY ',' NOLINE
>NAME &t_name
>  DISPLAY &t_name NOLINE
>  DISPLAY ',' NOLINE
>TYPE &t_type
>  DISPLAY &t_type
>From Q.AUTH_LIST
>Where CREATOR = $CODE
>End-Select
>DISPLAY '>'
>End-Procedure !Creator
>
>Begin-Procedure Type
>DISPLAY '<'
>Begin-Select
>GRANTEE &grantee
>  DISPLAY &grantee NOLINE
>  DISPLAY ',' NOLINE
>CREATOR &creator
>  DISPLAY &creator NOLINE
>  DISPLAY ',' NOLINE
>NAME &name
>  DISPLAY &name NOLINE
>  DISPLAY ',' NOLINE
>TYPE &type
>  DISPLAY &type
>From Q.AUTH_LIST
>Where TYPE = $CODE
>End-Select
>DISPLAY '>'
>End-Procedure !Type
>-----------------------------------------------------------------------
>This is an example of what I would like to do:
>
>Begin-Program
>  Do Get-Input
>  Do Main
>End-Program
>
>Begin-Procedure Get-Input
>!--------------------
>Input $CODE 'Enter a CODE' MaxLen=10 Type=Char
>If IsNull($CODE) or IsBlank($CODE)
>!  Show 'Input Error (7734): No value was entered. A value is
>required.'
>   Let #return-status = 7734
>   Stop
>End-If
>!--------------------
>Input $NIVEAU 'Enter a NIVEAU' MaxLen=4 Type=Char
>If IsNull($NIVEAU) or IsBlank($NIVEAU)
>!  Show 'Input Error (7734): No value was entered. A value is
>required.'
>   Let #return-status = 7734
>   Stop
>End-If
>End-Procedure !Get-Input
>
>Begin-Procedure Main
>If $NIVEAU = '2'
>   #DEFINE FIELD 'CREATOR'
>   Do Select
>End-If
>If $NIVEAU = '4'
>   #DEFINE FIELD 'TYPE'
>   Do Select
>End-If
>End-Procedure !Main
>
>Begin-Procedure Select
>DISPLAY '<'
>Begin-Select
>GRANTEE &t_grantee
>  DISPLAY &t_grantee NOLINE
>  DISPLAY ',' NOLINE
>CREATOR &t_creator
>  DISPLAY &t_creator NOLINE
>  DISPLAY ',' NOLINE
>NAME &t_name
>  DISPLAY &t_name NOLINE
>  DISPLAY ',' NOLINE
>TYPE &t_type
>  DISPLAY &t_type
>From Q.AUTH_LIST
>Where FIELD = $CODE
>End-Select
>DISPLAY '>'
>End-Procedure !Select
>
>=====
>Per Eriksson
>43, Avenue Charles de Gaulle
>92200 Neuilly sur Seine
>01.47.22.54.46
>06.88.29.39.47
>erper@home.se
>__________________________________________________
>Do You Yahoo!?
>Thousands of Stores.  Millions of Products.  All in one place.
>Yahoo! Shopping: http://shopping.yahoo.com

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com