incrementing a variable in SSRS query (to define row position)
-
Hi
using MSVisual Studio 2008
I'm trying to create an alias column called "row_number", for my where statement.
for some reason the SSRS query is not incrementing.
I have defined the parameter @num with a default value of 0. this works in mysql
This is what I used in the SSRS dataset query , that doesn't work
Please advise how i can get this to work. thanks
SELECT
CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.REFERENCE AS REFERENCE,
CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02,
@num = @num+1 as 'row_number'
from CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS
join CONSIGNMENT_ALL_USER_DEFINES_UNS on CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$ =
CONSIGNMENT_ALL_USER_DEFINES_UNS.OPSREF$$
where CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$="ISL1600697"
HAVING row_number =
CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02
This works in mysql workbench
set @num := 0;
SELECT
CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.REFERENCE AS REFERENCE,
CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02,
@num := @num + 1 as row_number
from CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS
join CONSIGNMENT_ALL_USER_DEFINES_UNS on CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$ =
CONSIGNMENT_ALL_USER_DEFINES_UNS.OPSREF$$
where CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$="ISL1600697"
HAVING row_number =
CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02
Example of SSRS parameter
-
That syntax will not work in SQL Server. To get the row numbers use the ROW_NUMBER() window function.
This then requires an order by which determines the row order to be numbered. This order by should be unique so as to make the row_numbers deterministic each time you run the query.
e.g.
ROW_NUMBER() OVER (ORDER BY REFERENCE) as 'row_number',
or
ROW_NUMBER() OVER (ORDER BY REFERENCE, USER_DATA_NUMERIC_02) as 'row_number',
Also, you cannot use ROW_NUMBER() in a HAVING or WHERE clause, so should you need to filter on it, you must put it in a CTE.
;WITH CTE AS (
SELECT
CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.REFERENCE AS REFERENCE,
CONSIGNMENT_ALL_USER_DEFINES_UNS.USER_DATA_NUMERIC_02,
ROW_NUMBER() OVER (ORDER BY REFERENCE) as 'row_number',
from CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS
join CONSIGNMENT_ALL_USER_DEFINES_UNS on CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$ =
CONSIGNMENT_ALL_USER_DEFINES_UNS.OPSREF$$
where CONSIGNMENT_TRACK_BUY_SUP_REFS_UNS.OPSREF$$="ISL1600697"
)
SELECT * FROM CTE
WHERE 'row_number' = USER_DATA_NUMERIC_02
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply