Search code examples
excelvbateradatapowerquerym

Expression.Error when dynamically passing in Teradata SQL query (with column aliases) to ODBC query

I have a macro that prompts me for a SQL query (unless it was called by another Sub, in which case it uses the argument that was passed into its optional string parameter as the query) and then executes the query against my Teradata SQL database.

It works fine, unless there's a column alias containing a space in the query.

Example query: SELECT 2 + 2 AS "Query Result";

Error: 

Run-time error '1004':

[Expression.Error] The name 'Source' wasn't recognized. Make sure it's spelled correctly.

The line of code which I believe is the culprit is as follows (my apologies for the readability-- I recorded the macro, modified it just enough to get it to work somewhat dynamically and then haven't touched it since).

ActiveWorkbook.Queries.Add Name:=queryName, formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Odbc.Query(""dsn=my-server-name"", " & Chr(34) & code & Chr(34) & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

I assume it has to do with the fact that the example query above has double quotes for the alias, which are confusing the syntax when trying to be interpolated. Any help would be greatly appreciated!

Solution

  • Here's what the string for the formula is set to in this line:

     ActiveWorkbook.Queries.Add Name:=queryName, formula:=<string here>

    after all the chr() and concatenation are done:

    let
    Source = Odbc.Query("dsn=my-server-name", "<code>")
in
    Source

    That token <code> is replaced by whatever is in your variable code. So I suspect you are correct in that this formula would need to have it's double quotes escaped fully.

    In other words this string you are building form Formula is going to be evaluated as code itself, and even in that evaluation it will be passing more code (your SQL) onto the Teradata server to be evaluated there.

    You are in code inception. VBA code writing powerquery code writing Teradata code.

    Understanding that and guessing a bit here, I'm thinking your current code variable looks something like:

     code="SELECT 2 + 2 AS ""Query Result"";"

    Your double quotes are already escaped for VBA. BUT because you have to survive another round of eval in powerquery you need to escape once again. Instead:

     code="SELECT 2 + 2 AS """"Query Result"""";"

    *I think...