Forum Discussion

hina_walia's avatar
hina_walia
New Contributor
6 years ago

Select count not working base in variable

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 ...
tstack's avatar
tstack
Former Employee
6 years ago

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.