Stack Exchange
CASE or IF Inside CTE statement
Im trying to increment a column based on the partition of another using CTE, so far i have the following:
DECLARE @start      INT
DECLARE @end        INT
DECLARE @current    INT
DECLARE @contNum    INT

SET     @start      = (SELECT MAX(ContractIssueID) - 20 FROM Contract)
SET     @end        = (SELECT MAX(ContractIssueID) FROM Contract)
SET     @current    = (SELECT MIN(ContractIssueID) FROM Contract)
SET     @contNum    = 0

WHILE @current <= @end
BEGIN
    WITH CTE AS
    (
        SELECT ContractIssueID, JobID, ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY JOBID DESC) AS RN
        FROM Contract
    )
    SELECT * FROM CTE
    WHERE ContractIssueID = @current
    SET @current = @current + 1
    CASE WHEN RN = 1 THEN SET @contNum = @contNum + 1 END
END

Im getting an error at the case statement, and when i try to use IF, it doesnt recognise RN as a column.
Any ideas how i can get around this?
---- EDIT ------------------------------------------
Error i get when running:
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'END'.

----- EDIT -----------------------------------------
PURPOSE:
I have three columns in this table (ContractIssueNumber, JobID AND ContractNumber). The ContractNumber should be a static value where the JobID is the same the same, whilst the contractissuNumber is incremental, like so:
ContractIssueNumber -- JobID -- ContractNumber

1                       100     1
2                       100     1
3                       101     2
4                       102     3
5                       102     3

Initially i just set the ContractNumber to the jobID, but that seems to have caused issues, so i am trying to set them to 1, 2, 3 etc. based on partitioning of the JobId Column.
----- EDIT -----------------------------------------
Thanks for all the help, the final and much more elegant solution is below
WITH CTE AS
(
    SELECT ContractIssueID
        , JobID
        , ContractNumber
        , DENSE_RANK() OVER (ORDER BY JOBID ) AS RN
            FROM Contract
)
UPDATE CTE SET ContractNumber = RN


What specifically is the error?

Nothing very helpful: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'END'.

In plain words, what are you trying to do with that CTE?

I just edited the post, hope that helps.

Your RN is not available inside set statement. RN is only available inside the select, so you need to increment @ContNum inside select using RN, and then set @current
WITH CTE AS
    (
        SELECT ContractIssueID, JobID, ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY JOBID DESC) AS RN
        FROM Contract
    )
    SELECT @contNum = @contNum + CASE WHEN RN = 1 THEN 1 END
    FROM CTE
    WHERE ContractIssueID = @current

    SET @current = @current + 1

I think you want dense_rank instead of row_number as well:
    SELECT ContractIssueID
    , JobID
    , DENSE_RANK() OVER (ORDER BY JOBID DESC) AS RN
        FROM Contract
--        WHERE ContractIssueID = @current skip this when not using the loop


Thanks, i get this error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Ok thanks, is there anyway i can see the results from CTE, as i will be switching this to an UPDATE once im sure it is correct.

@OwainEsau I think dense_rank() will give you what you need instead of @contNum

How does that work? When i changed the CTE part (inside the brackets) it just sets every instance of RN to 1.

@OwainEsau It sounds like you're getting the ID, then searching for the item with that ID, since there is only one, RN will always be 1. Try my 2nd query on its own, without the loop or the cte.

Oh, thats much much simpler than my failed attempt. How do i make that into an update so update Contract SET contractNumber = DENSE_RANK() OVER (ORDER BY JOBID DESC)

Check this,
DECLARE @start      INT
DECLARE @end        INT
DECLARE @current    INT
DECLARE @contNum    INT

SELECT  @start=(MAX(ContractIssueID) - 20),@current=MIN(ContractIssueID) FROM Contract
SET     @end        = @start+20
SET     @contNum    = 0

WHILE @current <= @end
BEGIN
    WITH CTE AS
    (
        SELECT ContractIssueID, JobID, ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY JOBID DESC) AS RN
        FROM Contract
    )
    SELECT @contNum = @contNum + CASE WHEN RN = 1 THEN 1 END
    FROM CTE
    WHERE ContractIssueID = @current
    SET @current = @current + 1

END

BTW,purpose of @contNum  is not clear.
What is the real scenario like in first place ? why you will want to update in loop ?This is the main problem.

The purpose of contNum was to create a new value for the ContractNumber column starting at 1 and incrementing by one each time the job number changed. i managed to fix this with the help from artm (see the last edit to my post)

Try this,
SET @contNum=CASE WHEN RN = 1 then  = @contNum + 1 END


Same issue as with the IF statement:  Invalid column name 'RN'.
