Re: [sqr-users] SQR logic in SQL

[Don Mellen <donm@ontko.com>]

Eric,

You can't use SQR in that way, but you can construct dynamic SQL
statements (which is what you have between the "[]").  Just do it for a
whole clause so it will always evaluate properly, such as....

   if isblank('{CARTYPE}')
      move '' to $car_type_clause
   else
      let $car_type_clause = ' AND C.TYPE IN {CARTYPE}'
   end-if

   if isblank('{CARYEAR}')
      move '' to $car_year_clause
   else
      let $car_year_clause = ' AND C.YEAR IN {CARYEAR}'
   end-if

SELECT *
FROM  CAR  C,
      YEAR Y,
WHERE       C.FAST > 150
 [$car_type_clause]
 [$car_year_clause]


... you'll still need to add a where clause to join the 2 tables to
prevent cartesian products, of course.

On Mon, 28 Jun 2004, DICKMAN, ERIC C. wrote:

> > Hello Everyone,
> > 
> > I am fairly new to the programming language of SQR.  I am attempting to
> > dynamically populate some SQL values in SQR and when the value does not
> > exist it creates problems the SQL (returns nothing).  Is it possible to
> > put SQR logic around the SQL. I created an example below of SQR logic in
> > SQL Is it possible to do something like that or is there another was to
> > handle this problem in the SQR?  The CARYEAR value is '' (no parameter
> > entered by the user) many times, because the values in passed in from a
> > Search page. 
> > 
> > I pass in the follow variables:
> > CARTYPE = ('Mustang','Corvette','Accord','Ranger')
> > CARYEAR = ('')
> > 
> > ask {CARTYPE}
> > ask {CARYEAR}
> > 
> > move  '{CARTYPE}'                 to $input_car_type
> > move  '{CARYEAR}'                 to #input_car_year 
> > 
> > SELECT * 
> > FROM  CAR  C,
> >       YEAR Y,
> > WHERE       C.FAST > 150
> > AND         C.TYPE IN ([$input_car_type])
> > 
> > if $input_car_year  <> '' 
> >  AND         Y.YEAR IN ([#input_car_year])
> > end-if
> > 
> Thanks,
> 
> Eric 
> 
> 
> NOTE: The Texas Department of Protective and Regulatory Services (TDPRS) has
> changed its name to the Department of Family and Protective Services(DFPS)
> resulting in the following e-mail address format change for all employees:
> firstname.lastname@dfps.state.tx.us 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
> 

-----------------------------------------------------------------------
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