0 votes

Hi there, 

I have one question related to global variables in DSS. Namely, is it possible to define a list as global variable and then use this in an SQL statement? What I am thinking of is something like this:

Global variable:

{"test_list": ["A", "B"]} 

SQL Statement

SELECT *

FROM test

WHERE "test_col" IN ('${test_list}')

Thanks a lot for your help!

Best, 

Oliver

 

by
edited by

1 Answer

+1 vote

Hi,

You query

SELECT *
FROM test
WHERE "test_col" IN ('${test_list}')

will be executed as

SELECT *
FROM test
WHERE "test_col" IN ('["A","B"]')

Then you need to cast this string to an array, and the how depends on your SQL database.

EDIT: here is an example for PostgreSQL using the jsonb type and the ? operator.

SELECT *
FROM test
WHERE '${test_list}'::jsonb ? "test_col";

 

by
edited by
Great, thanks for your help. Do you know a solution of how to do this in a Amazon AWS database? I only found a solution using SPLIT_PART or JSON_EXTRACT_ARRAY_ELEMENT_TEXT where one has to specify a position of the word which should be returned whereas I want to return all words in the list. Cheers!
Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.
Sorry, wanted to answer that in the previous reply. I use Amazon Redshift which is based on an older version of PostgreSQL 8.0.2. Cheers!
Hi jereze,
I tried to play around with it again but do not seem to find a solution. Did you have a chance to look into this? Thanks a lot for your help!
I personally don't use Redshift but I can give you an example using PostgreSQL (I updated the orginal answer). If you really need to work with Postgresql Array type (and not the json type), you can have a look at this: https://stackoverflow.com/questions/47359288/convert-a-text-to-an-array-postgresql
I think the method you describe is unfortunately not supported in Redshift. Nevertheless, thanks a lot for your help! I will ask in the AWS community and if there is a possibility post it here.
1,157 questions
1,192 answers
1,343 comments
11,533 users

┬ęDataiku 2012-2018 - Privacy Policy