Unexpected cartesian product in Foxpro SQL statement -

-

i executing following visual foxpro sql statement c# program. expecting 1 result not getting results.

select      iplclaim.lc_lname, ipcname.cn_lname, ipcbusn.cb_bname   ;      iplclaim  ; inner join      iplclsub on iplclsub.ls_claimno + iplclsub.ls_sclaimno = iplclaim.lc_claimno + 'a' ; left outer join      ipcname on ipcname.cn_inqno = iplclsub.ls_inqno  ; left outer join      ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  ;      iplclaim.lc_claimno = '  1105'     ;     , not deleted()

i able reproduce problem running vfp using following program...

close databases open database ipcust.dbc use c:\testvfp\ips\data\ppdata\iplclaim in 0 use c:\testvfp\ips\data\ppdata\iplclsub in 0 use c:\testvfp\ips\data\ppdata\ipcname in 0 use c:\testvfp\ips\data\ppdata\ipcbusn in 0 sys(3054,12)  select iplclaim.lc_lname, ipcname.cn_lname, ipcbusn.cb_bname   ;  iplclaim  ; inner join iplclsub on iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a'     ;  left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno  ;  left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  ; iplclaim.lc_claimno = '  1105' ;   , not deleted()

it seems if tables not open before program executed results 0 records. if open tables , execute sql statement returns expected 1 record.

the other thing not expect when run program above, sys(3054,12) returns following results (notice "cartesian product")...

select ipcname.cn_lname, ipcbusn.cb_bname iplclsub left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a' , not ip using index tag claimall rushmore optimize table iplclsub rushmore optimization level table iplclsub: partial rushmore optimization level table ipcname: none rushmore optimization level table ipcbusn: none joining table iplclsub , table ipcname (cartesian product) joining intermediate result , table ipcbusn (cartesian product)

but when run sql statement on own tables open output looks different (notice no "cartisian product")

select ipcname.cn_lname, ipcbusn.cb_bname iplclsub left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a' , not ip using index tag claimall rushmore optimize table iplclsub rushmore optimization level table iplclsub: partial rushmore optimization level table ipcname: none rushmore optimization level table ipcbusn: none joining table iplclsub , table ipcname using tag inqno joining intermediate result , table ipcbusn using tag inqno

final note: there 1 record in iplclaim matches where clause. there 1 record in iplclsub matches inner join iplclsub on clause. there 1 record in ipcname matches left outer join ipcname on clause , there no record in ipcbusn matches left outer join ipbusn on clause.

can explain causing problem , have resolve it?

edit:

well figured out problem and not deleted(). if remove clause works expected. can explain , can tell me alternative way exclude deleted records?

there warning in foxpro documentation select-sql command possibly returning unexpected results when using functions deleted() in multiple table queries.

the set deleted on command can used ignore records marked deletion. option turned on looks use of deleted() function removed query.

for c# code, assuming you're using oledb connection, set deleted on command can executed on same open foxpro connection prior executing query.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more