Hi Nadya,

I see where you are coming from. You do a SELECT * but if you have one field with exprssion you will need to enter list of fields isn't that. If you have a
field with one exprssion and you call a UDF to handle that, you may losse optimization in here. I am talking about RushMore.

Regards,

Doron

>>>If you have fields with the same name in different tables (cursors) and you do
>>>SELECT * from Table1 join Table2 ...
>>>You will have fields like field1_a, field1_b, etc. in the result. In our query >engine we wrote a special method to change all names with _a suffix to name >without it. We also added a routine to get rid of NULLs, which are produced as >a result of left (right/outer) joins.
>>
>>But those duplicates fields still be there. Can you get rid off them?<s>.
>>My problem is since I will need to create a JOIN based on RECNO(), I
>>will have to add all of those fields in addition to the regular fields.
>
>I can give you a brief info, what we're having here. For the customer extract jobs we usually create a job, which consists of three steps: Query, XForm and Report.
>
>The first step produces a record set of unformatted fields, the second step allows to format into custom defined output table structure and the last step produces "Report" (Excel, Fox2x, PDF, SDF, Comma-delimited, etc.).
>
>Each application is designed by my colleague programmer and uses MetaFile idea, e.g. info about each select statement, each transformation or each report definition is stored in a table, which called MetaFile. Here is the link for the description of this applications and discussion http://www.ideaxchg.com/ix08/d8/00000026.htm (you have to go through this site)
>
>Currently we have SelectCmd1, SelectCmd2 and OutputDest as fields in Query MetaFile. Select Statement is reconstructed this way:
>
> selectCmd1=evaluate(SelectCmd1Exp)
> selectCmd2= evaluate(SelectCmd2Exp)
> OuputDest = evaluate(OuputDest)
>
> select &SelectCmd1 ;
> &SelectCmd2 ;
> &OutputDest
>
> We hadn't ran into 8192 limit so far, but we rarely use field list, we usually put * in the field list. If I need to construct a long string in either SelectCmd1 or 2, I put a function call in this field, like MaprefStr(), and in this simple program generate the string.
>
> The resulting table would be then transformed into customer specific table and we care only about _a fields. So, in a special method we get rid of _a suffix.
>
>>How did you handle longer sql strings in your query engine? Did you address this problem at all?
>>
>>Doron
>>
>>>
>>>>Hi Sergey,
>>>>
>>>>The theory with all macro under same SQL statement does not work.
>>>>VFP is looking at one command at the time, and when expanding all macros
>>>>under same command and if it is greater then 8192 it will produce
>>>>an error.
>>>>
>>>>See this link as well:
>>>>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/conSystem_Capacities.asp
>>>>
>>>>Also if I break those field as suggested at first like the following it will
>>>>pass the 8192 characters limit, that does not work as well since at the bottom
>>>>I entered all fields names and exprssions into an SQL statement and that will
>>>>pass the 8192 characters limit as well.
>>>>
>>>>SELECT aaa, bbb, RECNO() as rn ;
>>>> FROM temp1 ;
>>>> INTO CURSOR xxx1
>>>>
>>>>SELECT ccc,ddd, RECNO() as rn ;
>>>> FROM temp2 ;
>>>> INTO CURSOR xxx2
>>>>
>>>>SELECT ;
>>>> xxx1.aaa ,;
>>>> xxx1.bbb ,;
>>>> xxx2.ccc ,;
>>>> xxx2.ddd ;
>>>>FROM ;
>>>> xxx1 ;
>>>>INNER JOIN XXX2 ;
>>>> ON xxx1.rn==xxx2.rn ;
>>>>INTO CURSOR ResultCursor
>>>>
>>>>If I add the following:
>>>>This way no need to enter all exprssions but I will fields like RN
>>>>all over.
>>>>
>>>>SELECT *;
>>>>FROM ;
>>>> xxx1 ,;
>>>> xxx2 ;
>>>>INNER JOIN XXX2 ;
>>>> ON xxx1.rn==xxx2.rn ;
>>>>INTO CURSOR ResultCursor
>>>>
>>>>Regards,
>>>>
>>>>Doron