Skip to main content
To homepage To homepage
Solved

Multiple Window frames (Error)

L
Contributor

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?
 

Best answer by rory.smith

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.

View original
    Did this topic help you find an answer to your question?

    L
    Contributor
    • Lorenzo
    • Contributor
    • July 21, 2023

    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
    TimeXtender Xpert
    Forum|alt.badge.img+7
    • rory.smith
    • TimeXtender Xpert
    • July 21, 2023

    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.

    Christian Hauggaard
    rogier.helmus
    daniel
    3 people like this
    • Christian Hauggaard
      Christian Hauggaard
    • rogier.helmus
      rogier.helmus
    • daniel
      daniel
    L
    Contributor
    • Lorenzo
    • Contributor
    • July 21, 2023

    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
    Contributor
    • Lorenzo
    • Contributor
    • July 25, 2023

    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
    TimeXtender Xpert
    Forum|alt.badge.img+7
    • rory.smith
    • TimeXtender Xpert
    • July 25, 2023

    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
    Contributor
    • Lorenzo
    • Contributor
    • July 25, 2023

    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
    Community Manager
    Forum|alt.badge.img+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


    Most helpful members this week

    Terms and ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings