Using VB6 SP4 with DSN to FoxPro 2.6 tables using VFP driver (6.01.8629.01) trying to link tables to create a recordset (ultimately to feed Crystal Reports 8 field definition report)
Using Data Environment Designer to create recordset using:
SELECT WKJOBS.WJ_WJID, WKJOBS.WJ_MTYPE, MSRFSYS.SS_DESC, MSRFITEM.IT_DESC, CMUNIT.CU_UNITNO, MNPDEI.PE_DESC, MNCRMNT.CM_DESC, MNPRSFTY.RS_DESC, MNPDTHRM.PD_DESC, MNPSSKED.SK_DESC
FROM (((((((((WKJOBS LEFT JOIN MNPDEI ON WKJOBS.WJ_WJID = MNPDEI.PE_WJID_) LEFT JOIN MNCRMNT ON WKJOBS.WJ_WJID = MNCRMNT.CM_WJID_) LEFT JOIN MNPRSFTY ON WKJOBS.WJ_WJID = MNPRSFTY.RS_WJID_) LEFT JOIN MNPDTHRM ON WKJOBS.WJ_WJID = MNPDTHRM.PD_WJID_) LEFT JOIN MNPSSCHD ON WKJOBS.WJ_WJID = MNPSSCHD.KS_WJID_) LEFT JOIN MNPSSKED ON MNPSSCHD.KS_SKID_ = MNPSSKED.SK_SKID) LEFT JOIN CMEQPT ON WKJOBS.WJ_EQID_ = CMEQPT.EQ_EQID) INNER JOIN CMUNIT ON WKJOBS.WJ_CUID_ = CMUNIT.CU_CUID) LEFT JOIN MSRFSYS ON CMEQPT.EQ_SSID_ = MSRFSYS.SS_SSID) LEFT JOIN MSRFITEM ON CMEQPT.EQ_ITID_ = MSRFITEM.IT_ITID;
Basically one table links through to several others and depending upon contents of field in first table decides which table to retrieve the second field from. The recordset of course retrieves the fields from all the tables but eventually Crystal will sort this out to just report as one field.
This works fine in Access SQL designer attached to FoxPro tables but in VB6 the VFP driver reports "SQL:Too many subqueries". As someone who does not really understand SQL too deeply, anyone have any idea of how I could rewrite the above so the VFP driver will not object?
TIA
John
Using Data Environment Designer to create recordset using:
SELECT WKJOBS.WJ_WJID, WKJOBS.WJ_MTYPE, MSRFSYS.SS_DESC, MSRFITEM.IT_DESC, CMUNIT.CU_UNITNO, MNPDEI.PE_DESC, MNCRMNT.CM_DESC, MNPRSFTY.RS_DESC, MNPDTHRM.PD_DESC, MNPSSKED.SK_DESC
FROM (((((((((WKJOBS LEFT JOIN MNPDEI ON WKJOBS.WJ_WJID = MNPDEI.PE_WJID_) LEFT JOIN MNCRMNT ON WKJOBS.WJ_WJID = MNCRMNT.CM_WJID_) LEFT JOIN MNPRSFTY ON WKJOBS.WJ_WJID = MNPRSFTY.RS_WJID_) LEFT JOIN MNPDTHRM ON WKJOBS.WJ_WJID = MNPDTHRM.PD_WJID_) LEFT JOIN MNPSSCHD ON WKJOBS.WJ_WJID = MNPSSCHD.KS_WJID_) LEFT JOIN MNPSSKED ON MNPSSCHD.KS_SKID_ = MNPSSKED.SK_SKID) LEFT JOIN CMEQPT ON WKJOBS.WJ_EQID_ = CMEQPT.EQ_EQID) INNER JOIN CMUNIT ON WKJOBS.WJ_CUID_ = CMUNIT.CU_CUID) LEFT JOIN MSRFSYS ON CMEQPT.EQ_SSID_ = MSRFSYS.SS_SSID) LEFT JOIN MSRFITEM ON CMEQPT.EQ_ITID_ = MSRFITEM.IT_ITID;
Basically one table links through to several others and depending upon contents of field in first table decides which table to retrieve the second field from. The recordset of course retrieves the fields from all the tables but eventually Crystal will sort this out to just report as one field.
This works fine in Access SQL designer attached to FoxPro tables but in VB6 the VFP driver reports "SQL:Too many subqueries". As someone who does not really understand SQL too deeply, anyone have any idea of how I could rewrite the above so the VFP driver will not object?
TIA
John
