Select count not working base in variable

Question

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 · Answer

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 · Answer

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(&lt;expr&gt;) 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 · Answer

Thanks. It worked

Forum Discussion

Select count not working base in variable

Recent Discussions

Pagination and nextCursor in header

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?