>"eView cannot use the SQL below to redefine the view because the SQL is invalid"
>
>I figure it either doesn't like multiple UNIONs, or like it's maker it doesnt like views based on views (duck) which is BTW unavoidable here unfortunately. Any thoughts? Mucho TIA!

There is no such intelligence built into eView to like or dislike multiple UNIONS. The sole criteria used to determine if your SQL is invalid is if VFP throws an error when the CREATE VIEW command is run.

But you're probably running into the limits of VFP with regard to length of a command that can be macro expanded. eView uses code something like the following when defining your view:

lcViewCode = "CREATE SQL VIEW MyView AS SELECT * FROM SomeTable"

&lcViewCode

It traps for errors on this line, and if an error is thrown, shows you the message you are seeing. So try a test:

At the command window, load your view SQL into a variable prefaced with "CREATE SQL VIEW MyView AS " + lcSQL, and macro execute it using &lcViewCode. Do you get an error?

I knew this would come up someday. I have already tried working around it. Unfortunately the SQL in the CREATE SQL VIEW command is not a named expression, so it cannot be represented with a variable. IOW, I tried:

CREATE SQL VIEW MyView AS (lcSQL)

But this gives a "select statement is invalid" error.

So the only way to create a view based on a string built on the fly is with macro substitution, and the limit in length of a macro substituted command is finite (although I thought it was several thousand characters, and your statement comes in at around 1200).

I'm interested to hear your findings, as well as any suggestions you might have.

Thanks for the heads up.