I have added a new field “PCD_Prev” to my table and populated this field using the LAG function (Custom transformation) Like so:
LAG([PCD], 1, '0') OVER (Partition BY [AdministrationID], [ApplicationID] ORDER BY [AcceptanceID] )
Now I try to use this field in a case statement of another field, again using Custom transformation, like so:
WHEN [PCD_Prev] in ('0', 'ka_aa')
WHEN ([PCD] = 'ka_hb' AND [PCD_Prev] = [PCD] )
) OVER(partition BY [AdministrationID], [ApplicationID]
ORDER BY [AcceptanceID]
ROWS between unbounded preceding and current row
I get an error that I can't use a window frame in another window frame although I don't do it in the same transformation..
Is this standard behavior? Do you have a solution for this?
In SSMS I add the new field in the FROM clause that is adding at as an additional field to my existing table in the FROM clause and then use this field in the select, as described above.
I have now created an additional table to do this in two steps but is this the correct and only way to get this done?
Best answer by rory.smithView original