Re: String me up, please.

[Art DiRamio <ADiRamio@OPENSOLUTIONS.COM>]

Good answer Dave!

One other thing that may be confusing is the underscore between the $
and variabl name.  This is the syntax for using global variables within
a local procedure (the global variable $abc would be $_abc within a
local procedure) this may avoid more confusion later in Chris' SQR
mess/life

> -----Original Message-----
> From: David Donnelly [SMTP:dave@ISISBIO.COM]
> Sent: Friday, April 24, 1998 12:26 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: String me up, please.
>
> Gladly, Chris.
>
> I didn't find the info on literals where it oughta be, either, which
> would
> be about page 10, or somewhere in the index under "literal" or
> "character
> constant" or some such.
>
> As in most languages, you represent the single-quote character by
> typing two
> of them.  So if you want to assign one single-quote character to a
> variable,
> it's
>
>         let $quote = ''''
>
> This is hard to read in my e-mail font ... it's four single-quote
> characters.  The outer two delimit the string, as always, and the
> inner two
> represent the character.  More examples:
>
>         ''Don''t forget'    'Say your abc''s'   '''Wow!!'', exclaimed
> Frank
> to Joe.' [note double !!]
>
> I'm guessing that in your example the '01/' parts are just examples,
> as of
> course if they are really constants you can say:
>
>         Let $_Min_reportdate = '''01/01/' || $year || ''''
>
> which will concatenate the seven characters   '01/01/    the year, and
> a
> final quote, giving you perhaps '01/01/1998'  as I'm sure no one will
> ever
> use a two-digit year again....  BTW, spaces help a lot when typing all
> this.
>
> As for the Where, your example is missing something as you can't make
> a
> where clause out of a single date, you need some SQL too!  You can use
>
> [$where1]
>
> where $where1 is a string variable containing
>         where datefield = to_date('01/01/1998','mm/dd/yyyy')
> which you would have to construct with a Let statement such as this:
>
>    let $where1 =     ' where datefield =
> to_date(''01/01/1998'',''mm/dd/yyyy'')'
>
>
> Or you could do
>
> where datefield = [$where2]
>
> and in this case $where2 is a string variable containing
>          to_date('01/01/1998','mm/dd/yyyy')
>
>
> Any part of the query after the list of tables can be done this way..
> This
> is called Dynamic SQL, and cannot contain references to bind variables
> (page
> 33).  So you can't set $where1 to
>
>         where datefield = to_date($_Min_reportdate)
>
> Dynamic SQL works nicely if you don't know at design time that you'll
> always
> need a where clause.  If you do know that, and just need to supply the
> comparand, you can also say
>
> where datefield = to_date($_Min_reportdate)
>
> This isn't dynamic SQL, it's just a bind variable, but of course it's
> only
> evaluated before the query starts ... no fair trying to put different
> values
> into the variable as the query returns rows!
> And look ... in this case you DON'T (DON''T?) want any outer quotes
> ...it
> should just contain the string 01/01/1998.  The difference is this:
> in
> Dynamic SQL, you're making real SQL for Oracle or whoever to scan, and
> if
> the syntax requires a quote, you have to make one by using two.  In a
> substitution variable, its just data.
>
> If the query is executed a few times, performance won't be much
> different.
> If you execute the query a thousand times, it's faster to use bind
> variables
> as they're only evaluated once.  SQL will look at the dynamic SQL
> every time
> the query starts.
>
> Wishing you much fun and amusement in the world of SQR!
>
>
>
>
>
> At 04:24 PM 4/23/1998 -0400, you wrote:
> >I'm coding a huge mess.  (Well, okay, it's not a particularly large
> >mess, but since I'm coding it in SQR, it's by definition a big mess.)
> >My problem arises from coding semi-quasi-dynamic variables.
> >
> >To get SQR to correctly use a date from a user prompt, the LET
> statement
> >looks something like this:
> >Let $_Min_reportdate = ''||'01/'||'01/'||$year||''
> >I understand that || concatenates two strings.  I'm cool with this.
> I
> >understand that: 'a' || 'b' would equal 'ab'.
> >What I don't understand is the two single quotes to start and end the
> >expression.  Am I really concatenating two single quotes? Is SQR
> >stripping them off for some reason?  What's the deal with this?
> >
> >The WHERE clause holds even more fun for me.  This works:
> >"[$_Min_reportdate]".  Take out the double quotes, bomb.  Take out
> the
> >brackets, bomb.  Take them BOTH out, bomb.  Bomb city.
> >
> >I can adapt to what SQR wants me to do, but I'm running version 3 and
> >the User's Guide isn't yielding the secrets I need.  I want to know
> more
> >about when to use two single quotes, double quotes, and brackets.  I
> >need, like, RULES.  Guidelines.  Generalities AND exceptions.
> >
> >If it's in the manual and I missed it, tell me where.  I really did
> try
> >to get it from the text before posting, but I'm frustrated by the
> >syntax.
> >
> >-Chris
> >
> >
> Dave Donnelly           <dave@isisbio.com> or <isisdave@usa.net>
> ISIS BioComp            phone (909) 677-2446       fax (909) 677-3991