RE: [sqr-users] SQR logic in SQL

[Dattatraya Shetty <datta_shetty@yahoo.com>]

I think all you need to do is loose the parens on the in cartype 
ie 
let $CARTYPE = 'Mustang','Corvette','Accord','Ranger'

that should do it.

HTH
Datta. 

--- "Alexander, Steve" <Steven.Alexander@sanjoseca.gov> wrote:
> 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
> 



                
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

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