Re: [sqr-users] INPUT Parms

[Don Mellen <donm@ontko.com>]

The problem with the multiple input statements approach is that you need
as many inputs as the maximum number of elements you want to allow.  And
if the users want one more than that, then you have to change the code.

Allowing a comma seperated string for a single input is more flexible.
Then you can either do....

   let $campus = '''' || 
                 replace( translate($campus,' ',''), ',', ''',''') ||
                 ''''

... if you're on 6.0 or higher (replace was added then), or write a while
loop to pick it apart and reconstruct it, something like ....

begin-procedure make_quoted_string (:$str)
   move '''' to $qstr
   move 1 to #start
   while 1
      let #pos = instr($str, ',', #start)
      if #pos = 0
         break
      end-if
      let $qstr = $qstr ||
                  ltrim(rtrim(
                    substr($str, #start, (#pos - #start))
                  , ' '), ' ') ||
                  ''','''
      let #start = #pos + 1
   end-while
   let $qstr = $qstr ||
               ltrim(rtrim(
                 substr($str, #start, length($str) + 1)
               , ' '), ' ') ||
               ''''
   move $qstr to $str
end-procedure


... Both of these assume input of values that will not contain spaces, of
course.

On Mon, 20 Oct 2003, Gar Longworth wrote:

> The way I do it is to concatinate the quotes seperately.  If you collect 
> the input values BL, IN and SB from input variables $LOC1, $LOC2 and 
> $LOC3 and want to build the following where clause:
>  
> where $SCHOOL in ('BL', 'IN','SB')
> 
> First build the part in parenthesise with code like this:
> 
> LET $CLAUSE  =
>  '('
> ||  ''''  ||  $LOC1 || ''''  ||  ',' 
> ||  ''''  ||  $LOC2 || ''''  ||  ','  ! repeat as many times as needed 
> for all locations
> ||  ''''  ||  $LOC3 || 
> ||  ')'
> 
> Then add the where clause like this:
> 
> select
> SCHOOL
> from (table name)
> where SCHOOL in [$CLAUSE]
> 
> 
> >-----Original Message-----
> >From: Hunsaker, Michael S [mailto:mhunsake@indiana.edu]
> >Sent: Monday, October 20, 2003 8:51 AM
> >To: sqr-users@sqrug.org
> >Subject: [sqr-users] INPUT Parms
> >
> >
> >Hello -
> >
> >I am trying to use an INPUT statement to grab one or more choices from
> >the user.  For example, my input statement:
> >
> >Input $campus 'Enter Campus Code: ' type=char
> >
> >I am asking the user to enter the campus or campuses to view data for.
> >The main problem I am running into is building the dynamic where clause.
> >Since the user input will look something like the below line, I cannot
> >build the data into a where clause without the single quotes.
> >
> >BL, IN, SB
> >
> >Is there an easy way to pad each input with single quotes?  I would like
> >the response to look like this in order to build the dynamic where
> >clause.
> >
> >'BL','IN','SB'
> >
> >Since I am new to SQR, I am probably overseeing a particular function
> >that would help me out.  Normally there are "explode/implode" functions
> >to be used in cases like these. 
> >
> >Thanks.
> >
> >Mike
> >

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users