Re: Select depending on...

[Arjan Hoornstra <ahoor@YAHOO.COM>]

Per,

The way to do is is underneath:


Begin-Procedure Main

! I prefer 'evaluate' instead of many 'if' statements

Evaluate $NIVEAU
when = '2'
  let $where = 'CREATOR = ' || $CODE
  break
when = '4'
  let $where = 'TYPE = ' || $CODE
  break
End-If

 do Select

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 [$where]

End-Select
DISPLAY '>'
End-Procedure !Select


I must say that personally I prefer the 'old' way
for better readability.

Arjan Hoornstra
Holland




--- Per Ericsson <perifras@YAHOO.COM> wrote:
> 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
>

__________________________________________________
Do You Yahoo!?
Thousands of Stores.  Millions of Products.  All in one place.
Yahoo! Shopping: http://shopping.yahoo.com