[Babase] Re: Fw: SQL for Updating in FP

Karl O. Pinc kop at meme.com
Sat May 5 15:20:54 EDT 2007


cc-ing to the list so others can chime in.

On 05/05/2007 12:46:22 PM, Leah Gerber wrote:
> 
> Hi Karl,
> 
> I tried this update statement and can't get it to work. I have never
> used
> the update statement and can't find any documentation on it so not
> sure
> where to hunt down the error. Any clues? The error is
> 
> operator/operand type mismatch

That means that the operator (e.g. <) does not work with
the datatype of the supplied data (e.g. 1995 or YEAR(date)
in the expression YEAR(date) < 1995).  You'd get that
sort of error trying to, for example, add the letter A
to the number 3.  (However, the "+" operator means
string concatenation in foxpro, and it's supposed to automatically
convert the numbers to strings so if you wrote: "A" + 3
you should get "A3".)

I don't see a problem anywhere but you can try the
different pieces in a select like:
SELECT date, CTOD("1/" + MONTH(date) + "/" + YEAR(date)) ;
   FROM interact
    WHERE DAY(date) != 1 ;
          AND (act = "A" OR act = "AS" OR act = "OS" OR act = "DS") ;
          AND YEAR(date) < 1995 ;
          AND sid != 0

Humm... Maybe it's the way numbers change to strings.

Try it with a CTOD expression that looks like:
CTOD("1/" + ALLTRIM(MONTH(date)) + "/" + ALLTRIM(YEAR(date)))

> 
> Leah
> 
> -----Forwarded by Leah Gerber/Urology/Surgery/mc/Duke on 05/05/2007
> 01:44PM
> -----
> 
> To: Leah Gerber <leah.gerber at duke.edu>
> From: Lacey Maryott <lacey.maryott at duke.edu>
> Date: 05/05/2007 01:19PM
> Subject: SQL for Updating in FP
> 
> UPDATE interact ;
>   SET date = CTOD("1/" + MONTH(date) + "/" + YEAR(date)) ;
>   WHERE DAY(date) != 1 ;
>         AND (act = "A" OR act = "AS" OR act = "OS" OR act = "DS") ;
>         AND YEAR(date) < 1995


You'll also probably need to add: AND ISNULL(sid)
or maybe: sid != 0
I forget just what foxpro requires.





Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein



More information about the Babase mailing list