>>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