CODEWITHSUNDEEP
sqlsql-servert-sql
Mad Scientist
Mad Scientist

Reputation: 917

Use ROW_NUMBER() in WHERE clause in SQL Server

I have this query in Microsoft SQL Server:

SELECT *
FROM
(SELECT 0 run_id,
      0 tran_id,
      0 sort_id,
      ' ' tran_type,
          10 prod_id,
          72 type_id,
          1 value,
            CONVERT(DATETIME ,'2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1 + convert(varchar(8), sw.end_time, 8) event_publication,
          DATEPART (YEAR
                   FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) y,
                  DATEPART (MONTH
                           FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) mo,
                          DATEPART (DAY
                                   FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) d,
                                  datepart (hour from sw.end_time) h,
                                  datepart (minute from sw.end_time) mi,
                                  datepart (second from sw.end_time) s,
                                  0 ms
FROM sys.all_objects ao,
    settlement_win sw,
    prod_def pd
WHERE pd.prod_id = 10
 AND sw.country = pd.country
 AND sw.commodity = pd.commodity
 AND row_number() OVER (ORDER BY (SELECT NULL)) <= CONVERT(DATETIME, '2016-03-18 23:59:00', 102) -CONVERT(DATETIME, '2016-03-18 00:00:00', 102)+1
)AS A WHERE  1=1
AND event_publication >= CONVERT(VARCHAR(19), '2016-03-18 00:00:00', 20)
AND event_publication <= CONVERT(VARCHAR(19), '2016-03-18 23:59:00', 20)
ORDER BY run_id,
     prod_id,
     type_id,
     y,
     mo,
     d,
     h,
     mi,
     s,
     ms,
     sort_id,
     tran_id,
     value;

When I execute it in Microsoft SQL Server Management Studio I get this error:

Msg 4108, Level 15, State 1, Line 27

Windowed functions can only appear in the SELECT or ORDER BY clauses.

because of the line AND row_number() OVER (ORDER BY (SELECT NULL)) <= CONVERT(DATETIME, '2016-03-18 23:59:00', 102) -CONVERT(DATETIME, '2016-03-18 00:00:00', 102)+1 I could not find a workaround which works for my query as well.

Upvotes: 0

Views: 232

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can not use window funtion in where clause but you can check that on next layer in subquery or cte

    SELECT *
    FROM
    (SELECT 0 run_id,
          0 tran_id,
          0 sort_id,
          ' ' tran_type,
              10 prod_id,
              72 type_id,
              1 value,
                CONVERT(DATETIME ,'2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1 + convert(varchar(8), sw.end_time, 8) event_publication,
              DATEPART (YEAR
                       FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) y,
                      DATEPART (MONTH
                               FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) mo,
                              DATEPART (DAY
                                       FROM (CONVERT(DATETIME, '2016-03-18 00:00:00', 102) + row_number() OVER (ORDER BY (SELECT NULL)) -1)) d,
                                      datepart (hour from sw.end_time) h,
                                      datepart (minute from sw.end_time) mi,
                                      datepart (second from sw.end_time) s,
                                      0 ms
   ,row_number() OVER (ORDER BY (SELECT NULL)) as rn
   FROM sys.all_objects ao,
        settlement_win sw,
        prod_def pd
    WHERE pd.prod_id = 10
     AND sw.country = pd.country
     AND sw.commodity = pd.commodity

    )AS A WHERE  1=1
    AND event_publication >= CONVERT(VARCHAR(19), '2016-03-18 00:00:00', 20)
    AND event_publication <= CONVERT(VARCHAR(19), '2016-03-18 23:59:00', 20)
    AND  rn <= CONVERT(DATETIME, '2016-03-18 23:59:00', 102) -CONVERT(DATETIME, '2016-03-18 00:00:00', 102)+1
    ORDER BY run_id,
         prod_id,
         type_id,
         y,
         mo,
         d,
         h,
         mi,
         s,
         ms,
         sort_id,
         tran_id,
         value;

Upvotes: 1

Related Questions