Re: & Removal in Expressions

I create and run my SQL statements like this:

lcWhere = 'wostatus = "B"'
DO CASE
    Case Condition#1
        lcWhere = lcWhere + ' AND blahblahblah '
    Case Condition #2
        lcWhere = lcWhere + ' AND blahblahblah '
ENDCASE

TEXT TO m.lcSQL TEXTMERGE NOSHOW
    SELECT * ;
    FROM table ;
    WHERE <<m.lcWhere>> ;
ENDTEXT
lcSQL = ALLTRIM(CHRTRANC( m.lcSQL, CHR(13) + CHR(10) + CHR(9) + ";", " "
))
&lcSQL


This way I can conditionally put anything in the SQL I want. If I want to 
include different fields under different conditions, I can create a
variable 
for the conditional fields and just plop that in with <<lcFields>>. I can 
even conditionally and JOINS to the table, which I sometimes need based on

the fields I want included. Using TEXT / ENDTEXT makes it extremely easy 
because I can add variables and keep my formatting instead of trying to
add 
all those quotes and plus signs.

Cathy Pountney


"Gene Wirchenko" <genew@[EMAIL PROTECTED]
> wrote in message 
news:brorc31mq1n33n3h7gh1qnlqf5cuech7lt@[EMAIL PROTECTED]
>     I frequently build expressions for use in xBASE and SQL
> statements.  For example:
>
>      do case
>      case !empty(this.datefrom) and !empty(this.dateto)
>         dateexpwo="(trndtlow>=this.datefrom and
> trndthi<=this.dateto)"
>         dateexpol=;
>          "((trndtlow<this.datefrom and this.datefrom<trndthi) or "+;
>          "(trndtlow<this.dateto and this.dateto<trndthi))"
>      case !empty(this.datefrom) and empty(this.dateto)
>         dateexpwo="(trndtlow>=this.datefrom)"
>         dateexpol="(trndtlow<this.datefrom and
> this.datefrom<trndthi)"
>      case empty(this.datefrom) and !empty(this.dateto)
>         dateexpwo="(trndthi<=this.dateto)"
>         dateexpol="(trndtlow<this.dateto and this.dateto<trndthi)"
>      case empty(this.datefrom) and empty(this.dateto)
>         dateexpwo=".t."
>         dateexpol=".f."     && Overlap with ends is impossible if no
> ends!
>         endcase
>
>      . . .
>
>      select . . .
>      where;
>       wostatus="B" and &dateexpwo and . . .
>
>     Short of writing four SQL selects for the above case, is there
> any way to get rid of the & use ("&dateexpwo")?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
>     I have preferences.
>     You have biases.
>     He/She has prejudices.