Skip to main content
To homepage To homepage

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(LPCD], 1, '0') OVER (Partition BY AdministrationID], DApplicationID] 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 rAdministrationID], DApplicationID] 
         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?
 

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.

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.

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?

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.

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.

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.

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