>>I have run some SQL statements that are less than 1000 characters. These I execute as a macro , ie
>>cSQL = "create SQL view as select * from specs .... etc"
>>&cSQL
>>
>>- this works in all cases.
>>
>>So the problem is when I text to file, compile the file and run it.
>
>Hmm... if your goal is to run a long CREATE SQL VIEW statement I'm not sure why you're writing it out to a separate .PRG in the first place.
>
>Some things you might try instead:
>
>- this is really basic, but I've got to ask: you *do* know about the use of * in SELECT - SQL e.g.

SELECT ;
>  * ;
>  FROM MyTable...
>
>* or, for multiple tables you can get * from just those that are needed:
>SELECT ;
>  Spec.*, ;
>  ...some other columns from other tables...
>  FROM Spec, OtherTables...

- use the alias clause for tables in the join. This can shorten the string length:

SELECT ;
>  A.Col1, ;
>  A.Col2, ;
>  ... ;
>  FROM Spec A ;
>  ...

Another thing you can do is use macro expansion for individual clauses in SELECT, rather than for the whole thing:

SELECT ;
>  &lcColumnExpression ;
>  FROM &lcJoinExpression ;
>  WHERE &lcWhereExpression ;
>  ORDER BY &lcOrderByExpression ;
>  ...

The as select ... statement is generated from a meta data table which contains all of my views, fields, tables and indexes. This is a very old application which predates VFP 3.0 and database containers.

Fields are entered seperately into views rather than using the * because some fields from join tables have the same name. A bad pratice for sure, but it was the lesser of two evils for indexing a parent table on inforamation in a child table. For example the vendor name is stored in the equipment table as well as the vendor file.

Therefore I had to select all all fields from the equip table except those from the join table vendor. Needless to say I am trying to get rid of these duplicated fields.

I was not aware of the :

select;
  Spec.*,Vendor.* ;  

notation for which I thank you
However your best suggestion is to 'use macro expansion for individual clauses in SELECT, rather than for the whole thing'.

I had macro expanded the whole statement to allow for statements like :

if not empty(cJoinCondition)

Best Regards
Don Simpson