How to build dynamic query argument for external query?

Question

I want to build dynamic query parameter based on declared variable for EXTERNAL_QUERY. So I declare STRING variable via concatenation 2 strings:

DECLARE str STRING DEFAULT "SELECT * FROM public.stats WHERE import_date >= "||"'2021-11-29'";

Then trying to set this variable as argument into EXTERNAL_QUERY:

SELECT * FROM EXTERNAL_QUERY("dataset.location.conn_name", str);

It's returns an error:

Query error: Invalid table-valued function EXTERNAL_QUERY Connection argument in EXTERNAL_QUERY must be a literal string or query parameter

What I do wrong?

It seems to be related with this EXTERNAL_QUERY... What is this ? What means "connection argument" in it? — vitooh
– vitooh, Commented Dec 2, 2021 at 7:56
Unfortunately this is not allowed at the moment, there is FR for this in Public Issue Tracker. I suggest to show your interest there to increase the priority of the case. — vitooh
– vitooh, Commented Dec 6, 2021 at 11:09

Timogavk · Accepted Answer · 2021-12-06 14:40:11Z

3

Not perfect, but works for me.

    DECLARE str STRING DEFAULT '''"SELECT * FROM public.stats WHERE import_date >= "'''||"'2021-11-29'";
    EXECUTE IMMEDIATE """
    SELECT * FROM EXTERNAL_QUERY(\"dataset.location.conn_name\",?);
    """
    USING str

answered Dec 6, 2021 at 14:40

Timogavk

9091 gold badge9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlie A Over a year ago

I haven't been able to get this to work. Keep getting this error:

Query error: Unexpected TVF argument type found, expecting literal, query parameter, or variable of type STRING at [2:34]

. Pretty weird, since str is definitely a variable of type STRING..

neondot42 · Accepted Answer · 2023-01-10 02:06:09Z

0

Thanks @Timogavk for your answer, I spent a while looking for a solution.

Though in my case I had to change the DECLARE for something more like this, without the additional quotations for it to work (I was working on dynamically querying different tables):

DECLARE str STRING DEFAULT 'SELECT * FROM ' || <table_name> || ' Limit 10';

answered Jan 10, 2023 at 2:06

neondot42

315 bronze badges

Collectives™ on Stack Overflow

How to build dynamic query argument for external query?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related