running long sql in VB

[nn992]

Hello everyone,

I am trying to run a long sql code within my VBA, and I am using correct concatenate signs, but still getting the error message.

I even tried to remove the concatenation and run it in SQL developer and it worked, but when I run it within VBA, I am getting error "ORA - 00923 FROM keyword not found where expected runtime error 2147467259 (80004005)"


Any ideas are welcome!

 

[Joe4]

Can you please post the VBA code addressing the SQL so we can see it?
Maybe something will stand out.

 

[nn992]

Here it is:

 

[Joe4]

That is odd, because I do not even see "ORA - 00923 FROM" anywhere in the SQL code.

This part does look odd to me though:

[COLOR=#333333]FROM [/COLOR][EMAIL="flt_perf.dim_ntfm_ftfm@PORAPE5_ACCESS.WORLD"]flt_perf.dim_ntfm_ftfm@PORAPE5_ACCESS.WORLD[/EMAIL][COLOR=#333333] a[/COLOR]

I don't like the "@" in the table name, and the ".WORLD" structure usually indicates a field named "WORLD" in that table, but you uses data sources (tables/queries) in your FROM clauses, not fields (fields would be used in the JOIN part of the statement).

Can you explain this part of the code?
Maybe it is just some syntax I am not familiar with...

 

[nn992]

It is just a connection to the table from another database...

Could it maybe be that he reads some part of the code as a comment? Because I had to keep commented parts at the end of the continuations, due to the reading issue.

Important to note is that when I remove continuations and run this same code as it is in SQL developer it works perfectly.

 

[nn992]

That is odd, because I do not even see "ORA - 00923 FROM" anywhere in the SQL code.

I think he is trying to tell me that there is a bad connection between FROM and SELECT statements within the code

 

[Joe4]

Could it maybe be that he reads some part of the code as a comment? Because I had to keep commented parts at the end of the continuations, due to the reading issue.

Important to note is that when I remove continuations and run this same code as it is in SQL developer it works perfectly.

That would seem to be suggest that is probably the issue.

 

[nn992]

is there some SQL function to tell him "stop reading as comment" or something similar?

Do you maybe have idea how it could be applied to the code?

 

[Joe4]

In using VBA code to build SQL code, I have actually never tried to mix comments in with the code. I am not sure you can do that.
Why would you want to anyway? SQL code written via VBA is really not all that readable anyway.

What are you going with the SQL code you are building?
Are you just running it, or are you assigning it to a query?

 

[Joe4]

I am also a little unclear if you are trying to add comments to be included in your VBA code (what I first) thought, or just comments to the VBA code to explain what your VBA code is doing (not the SQL code).

You may want to take a look at this conversation. I think it will answer some questions for you:
https://stackoverflow.com/questions/13178817/sql-queries-in-vba-code-how-to-comment-after-sql-string