[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