To homepage To homepage
Solved

Multiple Window frames (Error)

  • 21 July 2023
  • 7 replies
  • 60 views
L
Rank

Hi,

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:
COALESCE(SUM(CASE 
        WHEN [PCD_Prev] in ('0', 'ka_aa') 
            THEN 1         
        WHEN ([PCD] = 'ka_hb' AND [PCD_Prev] = [PCD] ) 
            THEN 1
        ELSE 0 
         END
        ) OVER(partition BY [AdministrationID], [ApplicationID] 
         ORDER BY [AcceptanceID]
        ROWS between unbounded preceding and current row
          ), 0)

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?
 

icon

Best answer by rory.smith 21 July 2023, 16:43

View original

7 replies

L
Rank

The exact error message:

An error occurred during create view. See exception details for the failing object: Create failed for View 'dsa.nLeaseQuotationApplicationLineAttributes_T'.
An exception occurred while executing a Transact-SQL statement or batch.
Windowed functions cannot be used in the context of another windowed function or aggregate.

Details:

Windowed functions cannot be used in the context of another windowed function or aggregate.

rory.smith
Rank
Userlevel 5
Badge +7

Hi @Lorenzo ,

transformations are defined in a transformation view ( <table>_T ) and this will nest the syntax into one expression in your case. You can probably see this if you right-click on your table and do Advanced > Customize Code > Transformation View >  Add .

 

Note that whenever you put a CASE statement in a Custom Transformation in TimeXtender, the God of Conditional Transformations cries a little: any CASE syntax should be replaced by fixed transformations with conditions added.

L
Rank

I appreciate you took your time to respond.
But regarding your remark concerning my usage of the Custom transformation, If you look at my code, not sure how you would use a fixed transformation with a condition in my case (e.g. window functions). If you have a solution for that I would appreciate it when you share that. 

Regarding my question how to work around this, the way I have solved it is the only way?

L
Rank

Hmm nobody is able to answer my previous question? 
@Rory You mentioned I should not use CASE Statements in a custom transformation but you did not answer my question how to work around in the example I provided. If it is not possible please also reply that it is not possible please.

rory.smith
Rank
Userlevel 5
Badge +7

I am not entirely sure what you are functionally trying to do here, but you should rewrite your code to use a subquery with a group by to avoid the nested window function / aggregate or simply cut it into two steps: do the window function for PCD_Prev in a Custom View, materialise it into a table and then add your transformation to that table.

L
Rank

Hi Rory,

The problem regarding the double window function I already solved using two steps, see my first message. I’m referring to your remark that I should not use a case statement in a custom transformation but you did not mention how to solve that when you use e.g. a window function.

Christian Hauggaard
Rank
Userlevel 5
Badge +5

Hi @Lorenzo 

As Rory mentions, the code shows how TimeXtender interprets the 2 custom transformations and shows why the error is occurring (it replaces the parameter field [PCD_Prev] in the second transformation from the statement in the first transformation).

 

I do not think there is a workaround to do this in one step. It seems you have managed to find a 2-step workaround using an additional table. Rory also mentioned another 2-step workaround using a view. A custom step / script action could also potentially be used to update the new field value you are trying to create, although this would also be a 2-step workaround.

Reply


Terms and ConditionsCookie settings