Sietse, Seitse, Thanks for the heads up. On Saturday I did try removing the "Join" just as you suggested, as this was my first "gut reaction" but the query ran so slow (x 20) that I reverted to the ctod() solution just to get the damned thing working at a sensible speed.
As for the Evl() solution, that is a good one - well found! I might revisit the code and use that method. Yes, I've seen a similar problem when using UDF's but this particular one definitely goes in the book of "things to look for". The weird thing was that I was looking at the data as the culprit, thinking that I had some corrupted records in the table(s) as the actual live SQL syntax contains about 15 joins and is vastly more complicated than in the example. That's why it took me so long to find out what was happening. I'll report it as a "bug" though as the statement shouldn't error. Dave Crozier The secret to staying young is to live honestly, eat slowly, and to lie about your age -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sietse Wijnker Sent: 31 July 2006 12:13 To: profox@leafe.com Subject: RE: Wierd SQL Error - Any Ideas? Hmm. strange indeed. What I'm seeing: The 1st call to the date function, the curTemp.cDate contains an empty value! I'm not sure but I'm bettiong it has to do with optimization of the query. I've seen this result by using an UDF that accepts 1 param and prints it out to the screen. Then include that UDF as a column, passing the curTemp.cDate as the param The solution: use EVL() in the expression: Date(2000+Val(Substr(EVL(curTemp.cDate, "01/01/00"),7,2)), ; Val(Substr(EVL(curTemp.cDate, "01/01/00"),4,2)), ; Val(Substr(EVL(curTemp.cDate, "01/01/00"),1,2))) ; Another solution: You're only using the join to filter out the records from the lookup: Don't use the join but use Where curTemp.F1="A01" AND curTemp.FK1 IN (SELECT pk FROM curTemp2) Regards, Sietse Wijnker > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dave Crozier > Sent: maandag 31 juli 2006 12:04 > To: [EMAIL PROTECTED] > Subject: Wierd SQL Error - Any Ideas? > > To All, > I had a WEIRD error come up over the weekend in a set of programs that > have been working for ages. I managed to tie the problem down to what > I think is an error in VFP and wonder what you all think. > > Run the program below and you should find that the first and second > select statement works OK and the third one doesn't! > My question is WHY? > I can't come up with a reasonable answer here other than the fact that > in > (3) VFP seems to be trying to "pre process" or parse the > Date() expression with a dataset containing "null" records > (maybe?) caused by the "Join". > > As the only difference is the Join in statement (3), this is obviously > the culprit but WHY? > > Important Note: > If you replace the date() statement with > Ctod(Substr(curTemp.cDate,1,8)) ; > as Date ; > > Then ALL the statements work. > > Surely this is a deep error with the Date() - or am I missing > something obvious here. All I know is that it ate up 4 hours to find > the problem! > > ******************* > * Start of Program > * > Close Tables all > > Set Date British && DD/MM/YY > Create Cursor curTemp (FK1 I, F1 C(10), cDate C(8)) > > Insert Into curTemp Values (1, "A01","01/01/06") Insert Into curTemp > Values (1, "A02","02/01/06") Insert Into curTemp Values (2, > "A03","03/01/06") Insert Into curTemp Values (2, > "A04","04/01/06") Insert Into curTemp Values (1, "A05","05/01/06") > > Create Cursor curTemp2 (PK I, F1 C(10)) Insert Into curTemp2 Values > (1, "Lookup") > > * (1) This statement works OK it has one record Select All ; > Date(2000+Val(Substr(curTemp.cDate,7,2)), ; > Val(Substr(curTemp.cDate,4,2)), ; > Val(Substr(curTemp.cDate,1,2))) ; > as Date ; > From curTemp ; > Where curTemp.F1="A01" ; > Into Cursor curResult > * > Browse > > * (2) This is OK, no records > Select All ; > Date(2000+Val(Substr(curTemp.cDate,7,2)), ; > Val(Substr(curTemp.cDate,4,2)),; > Val(Substr(curTemp.cDate,1,2))) ; > as Date ; > From curTemp ; > Where curTemp.F1="A99" ; > Into Cursor curResult > * > Browse > > * (3) This errors. Also No records > Select All ; > Date(2000+Val(Substr(curTemp.cDate,7,2)), ; > Val(Substr(curTemp.cDate,4,2)), ; > Val(Substr(curTemp.cDate,1,2))) ; > as Date ; > From curTemp ; > Join curTemp2 on curTemp2.PK=curTemp.FK1 ; > Where curTemp.F1="A01" ; > Into Cursor curResult > * > Browse > * > * End of Program > **************** > > Dave Crozier > The best way to make a Million pounds in computing. > ...Start with 5 million! > > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.394 / Virus Database: 268.10.5/403 - Release > Date: 28/07/2006 > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
