From: sfc@tpghq.com (Steve Caswell)
Subject: Re: Q: .RW spec finds no rows w/sent BETWEEN/IN clause
Organization: Palmer & Associates, Inc., Norcross, GA
Date: Thu, 7 Jan 1993 16:13:52 GMT

In article <00966334.5A9D35E0.4200@hal.hahnemann.edu> "Anthony J. Rzepela" <rzepela@hal.hahnemann.edu> writes:
>If someone could please help, it would be appreciated.  My patience
>is paper-thin at this point. 
>
>setup: 		Version 6.3/03 on VMS 5.4-2 (MicroVAX 3500)
>
>Tool:   	Report Writer
>
>Statement:	seems that where clauses that use such predicates
>        	as 'between' and 'in (itema,itemb,...)', while they work 
>        	perfectly fine as a part of the .RW spec, bomb when the 
>        	WHERE clause is sent as a replaceable parameter. 
>
>Behaviour:      * No rows are found when a "WHERE column-name in 
>                  ('itema','itemb')" is sent to the report writer to be 
>                  substituted.
>                * No rows are found when a "WHERE column-name between 
>                  itema and itemb" is sent to the report writer to be 
>                  substituted in the .QUERY statement. 
>
>                In *BOTH* cases, rows are found when the *same exact* 
>                string, quotes and all, is embedded as part of the original 
>                spec. 
>
>Parameters:     Report has been attempted from both the CALL REPORT in 4GL, 
>                or opsys command REPORT, with the clauses on the command
>                line. 
>	
>did u check:
>
>* quote stripping? 
>	
>     Yes.  I used the flag to force a report header to be printed on 
>     case of empty queries, and to print out what is sent as the 
>     WHERE clause.  There, staring me in the face, is my beautifully 
>     and carefully built clause, a clone of how it appears in my 
>     clause-inclusive version, but not finding a single f*cking row.   
>

I realize you have looked at your where clause, but did you check to see
if RW is forcing your quoted character string to lowercase?  RW has a "feature",
that if your RW code looks something like:

   SELECT ...
   FROM ...
   $where_clause

the entire line you pass in is converted to lowercase (including contents of
quoted strings.  If, however, your RW code looks like:

   SELECT ...
   FROM ...
   WHERE $where_clause

RW does not force quoted strings to lowercase.

>* presence of data?
> 
>     Of course.
>
>Sorry for the obscenity (or near-obscenity) but I tore out a lot of hair 
>and did a lot of revamping on the assumption that *I* was doing something
>wrong.  The only positive experience was that in the process of RTFM I 
>came across a Release Note on something unrelated that solved a different
>problem. 
>
>If anything in the statement is unclear, please let me know.  I need this 
>problem resolved ASAP, and I made my ABF code such a mess in the 
>diagnostic process, I can't hand over the problem to even my best brain here. 
>
>----------------------------------------------
>Anthony Rzepela    <rzepela@cvi.hahnemann.edu>
>"What IS this fascination with Axl Rose? He looks so...unhealthy."  M.L.C. 1991
>-------------------


-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."