RE: [sqr-users] SQR logic in SQL

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

I have to disagree with the advice you've gotten so far.  I think you're
close to having it right.  But, what exactly are you putting on your command
line?  You should only have the text you want to be inserted into your SQL.
Also, you're using both the ask command and the dynamic variable feature.
That's redundant.  You could just use the ask command, and let the SQR
preprocessor place your text within the select clause without dynamic
variables.  Or you could use the input command, and bypass defined
constants.

-----Original Message-----
From: Don Mellen [mailto:donm@ontko.com]
Sent: Monday, June 28, 2004 8:47 AM
To: This list is for discussion about the SQR database reporting
languagefr om Hyperion Solutions.
Subject: Re: [sqr-users] SQR logic in SQL


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

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