Using Dynamic In-DB tool Advanced Query

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?

Input

Dynamic Processing

In Database

In-Database

Accepted answers

suppahc

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

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

All comments

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

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.

suppahc

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703