[SOLVED] spss modeler sql declare a variable for date

spss modeler sql declare a variable for date

I would like to do a SQL query in SPSS Modeler.

I need to use some variables for dates.

SELECT
   CASE
      WHEN cust.LAST_VALID_ORDER > to_date('07.01.2018', 'mm.dd.yyyy') 
         THEN '6M'
FROM 
    T1

When I use something like

DECLARE and SET

in a query it doesn't work.

Could you please help how to do that using SQL or any other approach?

I was trying to find any info but there's nothing.

Solution

Based on the question, I am not entirely sure what you are looking to achieve, so please feel free to clarify, if my answer does not address what you are looking to do.

SPSS Modeler allows you to specify parameters that can be used throughout a stream in way that is similar to how the DECLARE and SET work in SQL.

You can specify the stream parameters on the Parameters tab of the stream properties (File > Stream Properties ...). In the Database source node, you can reference the stream parameters by using the syntax '$P-parameter_name', where the parameter_name is the name of the stream parameter.

For example:

SELECT *
  FROM  [dbo].[REF_Holidays]
  WHERE [holidayDate] >= CONVERT(date, '$P-cut_off_date')

This will use the value of the stream parameter named cut_off_date as part of the WHERE-clause when querying the database.

You can find some more information on using parameters in a SQL query at https://www.ibm.com/support/knowledgecenter/en/SS3RA7_15.0.0/com.ibm.spss.modeler.help/dbsource_stream_params.htm