Sorin,

At the risk of sounding selfconfident, I still suspect a "feature" in the SQL engine. The problem seems to be that a division like that on fields that are not necessarily defined in the first Join is dangerous.

I solved the problem by dividing the SQL in 2, one on the virtual table and the other on the other 2.

Thanks for caring anyway.

Kind regards,

Marc












>Hi Marc !
>
>Huh, what a thread! I think I read something about T-shirts (?!) and I didn't read any further than that, t/fore I may repeat some other suggestions that may have been posted on this thread, if so I appologize.
>
>To summarize
>1. Definitely, leave a space before the semicolon (you may have tried it in the Command window in a sigle command line, w/o semicolons?)
>
>2. Alias your tables and fields (i.e. ... FROM Virtual a, Stock b, Sale c WHERE a.stk_parcel = b.v_stock ... and so on.). Since one of the tables may be already open (with its own name as alias), using the same alias names in the SQL statement may not be a good idea (I'm guessing it would disagree with the USE table AGAIN, performed by the SQL)
>
>3. Format the numeric values, as previously suggested [i.e. iif(V_Weight=0,00000.000,v_Value/v_weight)] to avoid unwanted colmn widths.
>
>4. If it works in the Command window it must work in a program, as well.
>
>Again, I appologize for redundancy, if any.
>
>My 2c worth.
>
>Regards,
>Sorin
>
>
>
>
>>Hi,
>>
>>Has anybody seen something like this?
>>
>>

>>select virtual.*, iif(V_Weight=0,0,v_Value/v_weight) as price;
>>   from Virtual,Stock,Sale;
>>   where stk_Parcel=V_Stock;
>>   and sal_Docnr= stk_docnr;
>>   and V_Box= cKey;
>>   and V_Status= hStk_Sale;
>>   into cursor glSold
>>

>>
>>This works 1) when I open the files and executed it in the command window. 2) from the program if I leave out the iif statement, and 3) it worked yesterday :).
>>
>>It does not work from the program if I leave out the as price clause or change price into something else. And not only that it does not work, the sql makes vfp go south.
>>
>>The same happens with a similar sql but with other files.
>>
>>I'm running 5 with sp3 installed.
>>
>>TIA,
>>
>>Marc