Using Dynamic In-DB tool Advanced Query

[suppahc]

I'm trying to query a SQL database and am able to query it if I just put the following in query:

db.schema.table

However, when I try to refine this and only pull a few columns, I get a no columns returned error. See below for my query:

Select_Statement:
'SELECT col1, col2, col3, col4 '

Query:
[Select_Statment] + 
'FROM schema.db.table'

What could be my issue? 

[suppahc]

I just tried quotes around my columns because some do have spaces but that didn't fix the issue.

[christine_assaad]

Hi @suppahc 

Have you tried to remove the quotes around the Select statement?

[suppahc]

How do I do that? It looks like the quote are inherent in the query. The quotes I have in the select statement above are me building the select statement or just typing text into a formula tool. 

The error I'm getting does says "SELECT [col1], [col2] etc FROM db.schema.table" but I'm not explicitly putting those quotations around my statement if that makes sense. 

Paul

EDIT: I checked my connection and I do have the option 5. Table/FieldName SQL Style is set to Quoted.

Could that be the issue?

[christine_assaad]

Hi @suppahc 

I did it as below. I specified the table name in front of the col name and specified the connection name/alias

I then added the formula to build the statement. 

I then used the Dynamic input in-DB to pass the connection name and query

Output

Perhaps you need to pass the connection name to be able to query the DB.

[suppahc]

Can you do it without passing [TableNm].[Col1] for all columns? I'm doing this for six different tables that all have the same columns so I was hoping to simply have the following so it can be generalized across the code:

SELECT [col1], [col2], [col3]

FROM db.schema.table

The table name is being built via arguments passed through the program so it'll get very messy and difficult to read if I have to add the table name in front of every column.

I don't think its an issue with my connection because I'm able to pull the tables by simply passing the db.schema.table through the query field and is the equivalent of Select * From db.schema.table.

EDIT:

I just tested it by doing, SELECT [tbl1].[col1] FROM db.schema.tbl1 and still getting the same error.

[suppahc · Accepted solution]

Think I found the issue. I think my connection isn't defaulting to quoted.. so when I'm trying to query using in-db dynamic, I need to add quotes as follows for the from statement:

"db"."schema"."table"

Currently testing but wondering if there is a way to default to this option (my dynamic inputs that query this table have option 5 as quoted.

[christine_assaad · Accepted solution]

Can you try to put he table name between [] and see if this will work?

[suppahc]

Yes, the [] in place of the "" also work.