Try to have to separate statements for the two parts of the UNION - and then a third, to combine the results.
Also, the following might not solve your problem, but for a start, you may want to try placing the nested iif() into a UDF for a - that would make everything more readable.
Some OR statements can be simplified with INLIST():
OR checks.lcode = "61" ;
OR checks.lcode = "62" ;
OR checks.lcode = "47" ;
OR checks.lcode = "48" ;
OR checks.lcode = "50" ;
OR checks.lcode = "63")
is equivalent to:
OR inlist(checks.lcode, "61", "62", "47", "48", "50", "63")
Regards,
Hilmar.
>Hello all.
>
>I'm trying to debug a program written in VFP 5.0 by a consultant in 1998. This query is dropping files somehow. Anyways, when I run the program in the test environment or run the query alone in a command window, I get the error - Not enough memory for file map. What do I have to do to get this to run on my computer?
>
>My computer is a Dell OptiPlex 110, pentium III, 798MHz, 10 gig ( over 6 free).
>
>The query it's hanging up on is:
>SELECT details.source, deposit.dep_nbr, deposit.rev_date, ;
> license.lcode, ;
> IIF(ALLTRIM(details.source) = "Point of Sale", license.p_eo, ;
> IIF(ALLTRIM(details.source) = "Stamp License", license.s_eo, ;
> IIF(ALLTRIM(details.source) = "Universal", license.u_eo, ;
> license.bass_eo))) AS eo_source, ;
> IIF(license.lcode = "60", "002301", ;
> IIF(license.lcode = "61", "002302", ;
> IIF(license.lcode = "62", "002303", ;
> IIF(license.lcode = "63", "002304", ;
> IIF(license.lcode = "47", "002147", ;
> IIF(license.lcode = "48", "002148", ;
> IIF(license.lcode = "50", "002149", ;
> license.objectcode))))))) AS g_objcode, ;
> IIF(license.lcode = "99", SUM(checks.total), ;
> (SUM(checks.units) * ;
> IIF(license.lcode = "60", 280.00, ;
> IIF(license.lcode = "61", 490.00, ;
> IIF(license.lcode = "62", 700.00, ;
> IIF(license.lcode = "63", 8.40, ;
> IIF(license.lcode = "47", 12.00, ;
> IIF(license.lcode = "48", 22.00, ;
> IIF(license.lcode = "50", 66.00, ;
> license.unit_cost))))))))) AS lic_tot, ;
> (SUM(checks.units) * license.tax_fee) AS tax_collector, ;
> license.taxobjcode, ;
> (SUM(checks.units) * IIF(details.source = "Bass Pro", license.tax_gfcfee, ;
> license.gfc_fee)) AS gfc_fee, ;
> IIF(details.source = "Bass Pro", license.taxgfcobj, "001143") AS gfc_obj, ;
> "11" AS gfc_eo, ;
> details.fyear AS fyear,;
> details.source AS g_source, license.lcode AS g_lcode ;
>FROM deposit, tblc_p_m, details, checks, license ;
>WHERE tblc_p_m.dep_id == deposit.dep_id ;
> AND details.cpm_id == tblc_p_m.cpm_id ;
> AND checks.det_id == details.det_id ;
> AND checks.lcode == license.lcode ;
> AND ALLTRIM(deposit.dep_nbr) = pub_deposit ;
> &lcl_BassSql ;
> GROUP BY g_source, g_lcode, g_objcode ;
>UNION ;
>SELECT details.source, deposit.dep_nbr, deposit.rev_date, ;
> license.lcode, ;
> IIF(ALLTRIM(details.source) = "Point of Sale", license.p_eo, ;
> IIF(ALLTRIM(details.source) = "Stamp License", license.s_eo, ;
> IIF(ALLTRIM(details.source) = "Universal", license.u_eo, ;
> license.bass_eo))) AS eo_source, ;
> "050003" AS g_objcode, ;
> (SUM(checks.units) * ;
> IIF(license.lcode = "60", 120.00, ;
> IIF(license.lcode = "61", 210.00, ;
> IIF(license.lcode = "62", 300.00, ;
> IIF(license.lcode = "63", 3.60, ;
> IIF(license.lcode = "47", 12.00, ;
> IIF(license.lcode = "48", 12.00, ;
> IIF(license.lcode = "50", 16.00, ;
> license.unit_cost)))))))) AS lic_tot, ;
> (SUM(checks.units) * ;
> IIF(license.lcode = "60", 00.00, ;
> IIF(license.lcode = "61", 00.00, ;
> IIF(license.lcode = "62", 00.00, ;
> IIF(license.lcode = "63", 00.00, ;
> IIF(license.lcode = "47", 00.00, ;
> IIF(license.lcode = "48", 00.00, ;
> IIF(license.lcode = "50", 00.00, ;
> license.tax_fee)))))))) AS tax_collector, ;
> license.taxobjcode, ;
> (SUM(checks.units) * 0) AS gfc_fee, ;
> IIF(details.source = "Bass Pro", license.taxgfcobj, "001143") AS gfc_obj, ;
> "11" AS gfc_eo, ;
> details.fyear AS fyear,;
> details.source AS g_source, license.lcode AS g_lcode ;
>FROM deposit, tblc_p_m, details, checks, license ;
>WHERE tblc_p_m.dep_id == deposit.dep_id ;
> AND details.cpm_id == tblc_p_m.cpm_id ;
> AND checks.det_id == details.det_id ;
> AND checks.lcode == license.lcode ;
> AND (checks.lcode = "60" ;
> OR checks.lcode = "61" ;
> OR checks.lcode = "62" ;
> OR checks.lcode = "47" ;
> OR checks.lcode = "48" ;
> OR checks.lcode = "50" ;
> OR checks.lcode = "63") ;
> AND ALLTRIM(deposit.dep_nbr) = pub_deposit ;
> &lcl_BassSql ;
>GROUP BY g_source, g_lcode, g_objcode ;
>INTO CURSOR c_temp
>
>
>Thanks,
>Sunshine
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)