>Hi Nadya,
>
>>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