Alation Query Forms - Parameter not working with "IN" operator

Question

I need to create a query form with an input parameter that accepts multiple string inputs.

According to Alation's document "What is a query form? Do you have parameters?", the code is simple:

for passing multiple values:

select * from order_history 
where customer_id in (${customer_id | type : raw});

for string multiple values:

select * from order_history 
where customer in ${customer | type : raw};

(I don't know what the difference is between passing multiple values and string multiple values, so if anyone can share that insight I'd be grateful).

My code:

SELECT * FROM WTY_DM.PRODUCT
WHERE VIN IN ${VIN | type : raw};

Result: It produces the parameter option as expected:

But a comma-delimited list as shown in the box throws an identical error whether I use the "passing multiple values" version or the "string multiple values" version:

Expected Result: Everything from that table where the VIN matches either of the entries in the parameter field.

The `IN` predicate requires parentheses if it contains more than one expression. — mustaccio, Nov 20 '19 at 12:03
@mustaccio as in the `where customer_id in (${customer_id | type : raw})` example? I tried that. Same error. — Vesper Annstas, Nov 20 '19 at 16:20
It looks like your software (or you -- can't tell which) supplies supposed character literals unquoted, so they are being treated as identifiers. — mustaccio, Nov 20 '19 at 16:37

score 0 · Answer 1 · answered Jan 30 '21 at 00:33

Alation moved their documentation and help center, so your link doesn't seem to work anymore. I found the new one, formatted it and shared it below for anyone in the future that is looking for this.

To address your specific issue, I think you can fix the problem by putting parenthesis around it, try this: SELECT * FROM WTY_DM.PRODUCT WHERE VIN IN (${VIN type : raw});

Then you have to publish the query and click 'Share as Form'

Documentation here...

Query Forms and Parameters

If you need to re-run a query many times with different column values, manually editing the script on each run can get tedious. An example is running the same query with different states or dates.

To simplify the process, you can create a query form (also called query parameters) by replacing desired constants with the following expression:

${variable_name | eg:example value | default:default_value | help:help text | type:type}

where variable_name becomes the name of the form field, example value shows up in the form field when it is empty, and help text is displayed to the left from the form field.

The type can take values:

string

integer

date

raw

Use type to specify the type of the variable for improved parsing, though you can leave it out. the type: raw tells Alation to take the variable input as-is.

single value

select * from order_history
where customer_id = ${customer_id};

single value with like

select * from order_history
where col LIKE '${string}';

passing multiple values

select * from order_history
where customer_id in (${customer_id  type : raw});

dates

select top 10 * from order_history
where my_date = ${my_date  type : date};

dates multiple values

select top 10 * from order_history
where my_date between (${start_date  type : date}) and (${end_date  type : date});

string values

select * from order_history
where customer = ${customer};

string multiple values

select * from order_history
where customer in ${customer  type : raw};

For example, if your original query read:

SELECT * from order_history where order_date = '2016-01-01';

Your query form could look like:

SELECT * from order_history where order_date = ${my_date | eg:2016-01-01 | type:date | default:2016-02-14 | help:enter order date in YYYY-MM-DD format};

To access the SQL-free form view of your templatized query, choose the Share Query as Form option.

score -1 · Answer 2 · answered Nov 21 '19 at 05:04

We are not sure about your statement especially ${customer_id | type : raw} part. We might need more detail information about customer_id, type and raw for better understanding of your question.

But generally speaking that part should be like a single word, such as 1 , 'A' or 'BBB'. Or 2 or more words with parentheses, such as (1, 2), ('A' , 'B') or ('AAA' , 'BBB').

Here is a sample usage script of IN and result for your reference on AIX Db2 V11.5.

#!/bin/sh

db2 -v "drop db db1"
db2 -v "create db db1"
db2 -v "connect to db1"
db2 -v "create table t1 (c1 int, c2 char(10), c3 int)"
db2 -v "insert into t1 values (1,'AA',1)"
db2 -v "insert into t1 values (1,'AB',2)"
db2 -v "insert into t1 values (2,'BB',3)"
db2 -v "insert into t1 values (2,'AB',4)"

VAR1='A'
VAR2='B'

db2 -v "select * from t1 where c1 in ( 1 , 2 )"
db2 -v "select * from t1 where c1 in 2"
db2 -v "select * from t1 where c2 in '${VAR1}${VAR2}'"
db2 terminate

Here are results of the three select statements:

C1          C2         C3
----------- ---------- -----------
          1 AA                   1
          1 AB                   2
          2 BB                   3
          2 AB                   4

C1          C2         C3
----------- ---------- -----------
          2 BB                   3
          2 AB                   4

C1          C2         C3
----------- ---------- -----------
          1 AB                   2
          2 AB                   4

Hope this helps your understanding.

I don't think you understand the issue, it is specific to a tool called Alation and how it interprets SQL with specific variable syntax into a simple UI for non-SQL people. — Ray, Jan 30 '21 at 00:34

Alation Query Forms - Parameter not working with "IN" operator

2 Answers2