Search code examples
sqlamazon-redshiftwindow-functions

How to pass in variable in over clause

I am trying to pass in a variable into an over clause.

Initially I had it hard coded to something like over (partition by deviceid order by readdate rows between current row and 2 following)

Is there a way to do something like over (partition by deviceid order by readdate rows between current row and continuousexcessivehours following)

or something like over (partition by deviceid order by readdate rows between current row and ISNULL(continuousexcessivehours, 2) following)

Error I get: Error: Amazon Invalid operation: syntax error at or near "continuousexcessivehours"

Thanks for the help.

Solution

  • Some technologies that execute parameterized SQL statements allow parameters at some specific points in the SQL statement ONLY.

    For example, DB2 does not allow parameters to limit the returned rows: while this parameterized JDBC statement is valid:

    select * from t where status = ? order by amount fetch first 10 rows only

    ...this one is not:

    select * from t where status = ? order by amount fetch first ? rows only

    My guess is you are facing a similar limitation of the engine, or the driver.

    The best workaround I've found so far is to inject the problematic parameters as a string, concatenating it to the SQL statement. For example, in Java you could do:

    // parameters

int status = 3;
int maxRows = 10;

// execution

PreparedStatement ps = conn.prepareStatement(
  "select * from t where status = ? order by amount fetch first " 
  + maxRows
  + " rows only"
);
ps.setInt(1, status);
ResultSet rs = ps.executeQuery();

    Both parameters are used, but in different ways:

    1. status is included as a traditional JDBC parameter.
    2. maxRows is injected as a plain String (SQL Injection).

    In your case, you can use the second strategy for the parameter continuousexcessivehours.

    Many ORMs offer solutions to accomplish this. For example MyBatis offers #{status} for the first case, and ${maxRows} for the second one. Did you spot the difference?

    Finally, a last word of advice: be careful with SQL injection. Only perform string injection for a parameter if you are sure your know the origin of its value, and it's not coming from an unknown source.