Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SQL puzzle
Message
From
01/10/2001 01:50:55
Vladimir Zhuravlev
Institute of the Physics of Earth,Russia
Moscow Region, Russia
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
SQL puzzle
Miscellaneous
Thread ID:
00562396
Message ID:
00562396
Views:
59
It is pretty shame too me to ask this. But I faced with very stange problem inside SQL.
I have to say I write and wrote many SQL and write it each day, and wrote in Oracle and MS SQL and in VFP.

I have 25 SQL in one method, and all they work at our database
Yesterday one client brought his base, where the code of one of sql fails.
The messages about mistake are casual at the same line
it could be variable '' is not defined, variable zxcvrt325 is not defined
( never used such variable) or wrong data types.
The text of this SQL is



Select sum(nsgn(ndr.kpp)*iif(ck_nspr.type=0, ;
getsum(rsh.str_id),00000000.00000)) as sm , ;
sum(nsgn(ndr.kpp)*iif(ck_nspr.type=2, ;
getsum(rsh.str_id),00000000.00000)) as smt ;
,thisform.proc_bar1.sayproc();
from dbf_ck!rsh,dbf_ck!ck_nspr,dbf_ck!ndr;
WHERE ck_nspr.product_id = rsh.product_id and not
empty(rsh.product_id) and;
empty(rsh.otkl_id) and rsh.doc_id = ndr.doc_id and ndr.dop into cursor inr
there are similar sql before this line and after it and all they work
We sat few ours with all company staff and did find the stupid advice
to fix the trouble, placed a fake variable 0 as sm2 before sum, like this

Select 0 as sm2,sum(nsgn(ndr.kpp)*iif(ck_nspr.type=0, ;
getsum(rsh.str_id),00000000.00000)) as sm , ;
sum(nsgn(ndr.kpp)*iif(ck_nspr.type=2, ;
getsum(rsh.str_id),00000000.00000)) as smt ;
,thisform.proc_bar1.sayproc();
from dbf_ck!rsh,dbf_ck!ck_nspr,dbf_ck!ndr;
WHERE ck_nspr.product_id = rsh.product_id and not
empty(rsh.product_id) and;
empty(rsh.otkl_id) and rsh.doc_id = ndr.doc_id and ndr.dop into cursor inr

Function getsum is pretty usual, also has sql inside, not touching aliasis
inside the main sql,
Function getsum(rid,_s)
Local _smres[1],m.rss
_smres[1]=00000000.00000000
select sum(iif(prh.kol!=0,round(prh.smr/prh.kol*prhrsh.kol,2)
,00000000.00000000));
from dbf_ck!prh,dbf_ck!prhrsh ;
where prh.str_id=prhrsh.strp_id and prhrsh.strr_id=rid into array _smres
m.rss=_smres[1]
if type('_smres')!='U'
release _smres
endif
return m.rss

mistake appeares only when we have sertain numbers of lines inside rsh
table. It works OK with 100, 1000 lines and fails somewhere at 3000 lines.
We tested it on the computers with different memory and hard drives space
(big and small), it failed. I have thrown code out the form , thrown parts of
it connected with form ( like thisform.sayproc()), ran in command window and again have mistake.
Obviously variable zxcvrt325 comes from internal sql mechanism. I have
nothing like this. For me , it is shame, because I write sqls on Oracle and
MS SQL with closed eyes. I propably did not care about it becase found
stupid way to avoid it, but I just wrote new bookkeeper software, where
complex sqls are 50% of all code.
I looked MS FAQ and did not see any hints on the mistakes in SQL.
The code was seen by 8 colegues with same result.
We did reindexing of all tables, it did not help.
I just need clever advice , what to do.
Somebody would say, it is too complex query, but I have much more complex, and these run very good.
Thanks in advance, Vladimir.
MVP-2006-2011, PHD in Math and Physics ,
host of www.foxclub.ru,
VFP lector at Interface and Microinform companies
Head science researcher of VNIIA Rosatom.
Next
Reply
Map
View

Click here to load this message in the networking platform