Hi,
I have problem - to avoid separately computed aggregates calculated on some level and joining them back I want to use windowing functions which are now implemented by many database vendors.
I have tried in AC using expression like
max(@eff_dt , PartitionBy(@col1,@col2,@col3))
which parses well but unfortunately it was converted to something like
max(W0.tsEff_dt , PARTITION BY W0.dCol1 , W0.sCol2 , W0.sCol3)
which of course result in error
and it should be like
max(W0.tsEff_dt) over PARTITION BY W0.dCol1 , W0.sCol2 , W0.sCol3)
is any way to get this ?
regards
Solved! Go to Solution.
Views
Replies
Total Likes
Hello @Klaster ,
I would like to suggest you create your own function and call it in expression.
For e.g. your required syntax in query is " MAX(parm1) OVER (PARTITION BY parm2, parm3,param4) "
Then you can create a custom function:
<?xml version="1.0" encoding='ISO-8859-1' ?>
<!-- ===========================================================================
Additional SQL functions for Adobe Campaign
========================================================================== -->
<package
namespace = "nms"
name = "package-additional-funclist"
label = "Additional functions"
buildVersion= "6.1"
buildNumber = "10000">
<entities schema="xtk:funcList">
<funcList name="myList" namespace="cus">
<group name="date" label="CUS FNC1">
<function name="cusFun1" type="long" args="(<parm1>,<parm2>,<parm3>,<parm4>)" help="Returns the difference between a date and 18 years"
minArgs="4" maxArgs="4" display="Relative maturity of the person born on the date $1">
<providerPart provider="MSSQL" body="MAX($1) OVER (PARTITION BY $2, $3,$4)"/>
</function>
</group>
</funcList>
</entities>
</package>
Then you can cal this function in expression like cusFun1(@eff_dt , @col1,@col2,@col3)
This is just example just to explain you. You need to use it and update it according to you.
You can follow the following documentation to create your own function.
Adding additional SQL functions | Adobe Campaign
Thanks.
Parvesh.
Hello @Klaster ,
I would like to suggest you create your own function and call it in expression.
For e.g. your required syntax in query is " MAX(parm1) OVER (PARTITION BY parm2, parm3,param4) "
Then you can create a custom function:
<?xml version="1.0" encoding='ISO-8859-1' ?>
<!-- ===========================================================================
Additional SQL functions for Adobe Campaign
========================================================================== -->
<package
namespace = "nms"
name = "package-additional-funclist"
label = "Additional functions"
buildVersion= "6.1"
buildNumber = "10000">
<entities schema="xtk:funcList">
<funcList name="myList" namespace="cus">
<group name="date" label="CUS FNC1">
<function name="cusFun1" type="long" args="(<parm1>,<parm2>,<parm3>,<parm4>)" help="Returns the difference between a date and 18 years"
minArgs="4" maxArgs="4" display="Relative maturity of the person born on the date $1">
<providerPart provider="MSSQL" body="MAX($1) OVER (PARTITION BY $2, $3,$4)"/>
</function>
</group>
</funcList>
</entities>
</package>
Then you can cal this function in expression like cusFun1(@eff_dt , @col1,@col2,@col3)
This is just example just to explain you. You need to use it and update it according to you.
You can follow the following documentation to create your own function.
Adding additional SQL functions | Adobe Campaign
Thanks.
Parvesh.
Hi @Parvesh_Parmar ,
This is great,
For absolutely happiness just I need to make this function work on fluent number of parameters in PARTITION BY section
But this approach save a lot of time of compare to computer aggregates separately and joining them with main data set.
Thanks a lot
Hi @Parvesh_Parmar ,
Why I receive the
XTK-170016 You are not authorized to use SQL expressions.
when I create a similar function
You can refer
Please guide
Regards,
Dipendu
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies