Parameterization of Date/TimeConfig for DB Table Reader custom SQL

[ebarr]

I have a feeling this is operator error, but I want to extract data from Redshift tables using custom date ranges in SQL.

I’ve added a Date&Time Configuration node to the workflow, and connected the port to the DB Table Selector coming out of the Amazon Redshift Connector node. When I run the workflow with hard coded dates, it works just fine.

I created the simple SQL code:

where my objective is to extract data that is dynamic based on the date entered in date/time config.

I’ve tried this with sql_statement populated and not populated.

What I was expecting to happen was that the query would read the varStartDate and pulling everything starting at that point, but it would seem that it’s assuming the varStartDate is coming directly from the table as it returns errors that the particular date column is not in the table.

Also, when I convert the node to a component so that I can change the date - the varStartDate no longer exports:

What am I missing? I want the date in the date config to populate the SQL query where it indicates ‘date’ >= varStartDate (the dynamic date that changes based on my input).

[hmfa]

@ebarr

varStartDate

Hi @ebarr
Try ‘$${SvarDateStart}$$’
Br

[ebarr]

Sorry - I neglected to state I tried that as well -

This is where it indicates that the column doesn’t exist.

This is with varStartDate added in flow variables in sql_statement:

[ebarr]

ps - when i hard code, the query runs just fine:

This returns last 40 months. The problem is that I need to pull out 5 or so tables and want the flexibility to select various date parameters, but don’t want to open up 5 different nodes to manually change each time and I’m not sure what the issue is wiht the flow variable - is it because this is being inserted into a larger SQL code vs. one simple field?

[hmfa]

Hi @ebarr

Remove this variable from DB table selector

and use single cote ‘$${SvarDateStart}$$’ on your variables

Br

[ebarr]

fantastic - thank you. I tried with single quotes, and just about every different way, but apparently didn’t check single quotes AND no flow variable populated. So I had success. The second part of my issue is that I understand I should create a component so that it’s just a double click to set the date. But, you’ll note that the varStartDate is not in the output when I convert to component:

when the node is still a node - you’ll note the varStartDate is being passed through:

is the expected behavior?i

[hmfa]

Hi @ebarr.

Try in the component connecting the Date&Time Configuration to a Merge Variables like this

See this expanation from

Simple Metanodes and Wrapped Metanodes

Br

[ebarr]

appreciate all of the help