cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Select count not working base in variable

hina_walia
New Contributor

Hi,
I want to pass the filename from parameter(pipeline-parameter) and check if that file is already loaded or not. If not loaded, then we go ahead to load it otherwise we do nothing.
Actually i have parent job and then this is child job , so pipeline parameter is from parent, it works fine without this file check step, so with file check step below is the issue:-
Below is the sql in snowflake execute
SELECT count(*) as cnt FROM FDR_SOURCE.KRONOS_LABORtest WHERE SOURCE_FILENAME = _Name
If i pass some fixed value, instead of _Name, then pipeline work fine. if i use param (_Name), it throws below error.
sql_with_activate_sql_tab

sql_with_inactive_sql_tab
PLease see attached screenshot
filename_param_error

Param set in โ€œpipeline parameterโ€
param

3 REPLIES 3

christwr
Contributor III

I think you need to keep the expression button (=) selected on the SQL statement field and construct the query string, like:

"SELECT count(*) as cnt FROM FDR_SOURCE.KRONOS_LABORtest WHERE SOURCE_FILENAME = " + _Name

tstack
Former Employee

In order to access values from input documents (or other expression language variables) in a SQL statement, you need to use a JSON-Path. For example, if there was a document going into this snap, you would reference a property in it using $name. In the case of pipeline parameters though, itโ€™s a little different. You need to use $.eval(<expr>) to evaluate an expression and then you put the pipeline parameter reference in there. So, you would write:

SELECT count(*) as cnt
    FROM FDR_SOURCE.KRONOS_LABORtest
    WHERE SOURCE_FILENAME = $.eval(_Name)

Note that this approach is the safest since it is using bound variables, so there is no possibility of a SQL injection vulnerability.

hina_walia
New Contributor

Thanks. It worked