Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
How can i convert below expression into Set Analysis ?
COUNT
(
DISTINCT
IF(
(
SC_ENABLED_STATE = 1 or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE = 1) OR
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') <= DATE(SC_START_DATE,'MM/DD/YYYY') ) OR
(DATE( SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY')) or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE <> 1 AND DATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)
,ID))
Many Thanks
Sanket
Please see below expression with IF-ELSE which is working. Need to convert this into SET ANALYSIS syntax.
COUNT
(
DISTINCT
IF(
(DATE(PC_FIRST_ACTIVATED_DATE,'MM/DD/YYYY') <= DATE((v_Max_SC_Start_Date),'MM/DD/YYYY')) AND
(
SC_ENABLED_STATE = 1 or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE = 1) OR
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') <= DATE(SC_START_DATE,'MM/DD/YYYY') ) OR
(DATE( SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY')) or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE <> 1 ANDDATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)
,ID))
Hi,
try including And condition expression in every set of Or condition
try below,
Count( DISTINCT {
<SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}
PARTNER_ACCOUNT_ID)
Regards
It is fetching 0 value. Where as IF -- ELSE Statement fetching 291 value which is correct.
Can we have other option to add AND operator.
Many Thanks
Sanket
Hi,
might be other experts can give better solutions for this.
Dear All,
swuehl, jagan, tresesco, sunindia
Please suggest.
Regards,
May be the variable is different:
Count( DISTINCT {
<SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''}, SC_ENABLED_STATE-={1}, SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}>} PARTNER_ACCOUNT_ID)
Thanks Sunny.
Variable is v_EndDate not v_Max_SC_Start_Date.
Many Thanks
Sanket
But the if statement you showed above was comparing it again v_Max_SC_Start_Date
Thanks for pointing out this but we need to use v_EndDate
Sorry for the inconvenience cause !!
Many Thanks
Sanket
Is the issue resolved then?
How can we add PC_ACTIVATED_DATE as AND clause in below set expression
Count( DISTINCT {
<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}