Select count not working base in variable

[hina_walia]

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.

PLease see attached screenshot

Param set in “pipeline parameter”

[christwr]

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]

hina_walia:

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.

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]

Thanks. It worked