@ravindrrapaal
A similar query / Calculated Data Item works in my environment. However there are a couple things to consider:
1. Since you have an Aggregate in the Case Statement, when the calculated field is used in a Selection, the criteria will be evaluated in the Having portion of the generated SQL.
2. If you want to use it primarily in Selections at a customer level, You should also include the Campaign_Cd= as an additional Selection criteria to limit the CH rows being considered. And for Select Node that includes both Case stmt plus Campaign_CD = criteria, there is no need for Campaign_Cd in the Case.
Below is from my CI Core log showing the generated query:
Create table WORK.MA0 as
SELECT
( count(*) ) AS DII_1 LABEL=''
FROM
(
SELECT DISTINCT
table0.IndivID AS INDIVID LABEL='Individual ID'
FROM
CDM.CI_CONTACT_HISTORY table1 Inner join CDM.CI_CELL_PACKAGE table2 on table1.CELL_PACKAGE_SK = table2.CELL_PACKAGE_SK Inner join DataMart.INDIVIDUAL_SUMMARY table3 on table3.IndivID = table1.INDIVID Inner join DataMart.INDIVIDUAL table0 on table0.IndivID = table3.IndivID
WHERE
table2.CAMPAIGN_CD = 'CAMP92'
GROUP BY
1
HAVING
( case when table1.CELL_PACKAGE_SK = Max(table1.CELL_PACKAGE_SK) then table2.MARKETING_CELL_CD else 'NotMax' end ) = 'CELL227'
) table4 ;
quit;
When I run this same query in EG, the following Note is included in the Log:
NOTE: The query requires remerging summary statistics back with the original data.
This note means that the sql sent to the database does not include the Having. Instead, variables used in the Having clause are passed to the database as part of the Select's list of retrieved fields and then the resulting retrieved data is evaluated to determine the Having portion. Below shows the Log with options to show sql sent to dbms:
NOTE: The query requires remerging summary statistics back with the original data.
45 1907066468 tkvercn1 0 SQL (2)
OLEDB_6: Executed: on connection 14 46 1907066468 tkvercn1 0 SQL (2)
select table1."CELL_PACKAGE_SK", table0."IndivID" as "INDIVID", table2."CAMPAIGN_CD", table2."MARKETING_CELL_CD" from
CDM."CI_CONTACT_HISTORY" table1 inner join CDM."CI_CELL_PACKAGE" table2 on table1."CELL_PACKAGE_SK" = table2."CELL_PACKAGE_SK"
inner join DataMart."INDIVIDUAL_SUMMARY" table3 on table3."IndivID" = table1."INDIVID" inner join DataMart."INDIVIDUAL" table0 on
table0."IndivID" = table3."IndivID" where table2."CAMPAIGN_CD" = 'CAMP92' 47 1907066468 tkvercn1 0 SQL (2)
48 1907066468 tkvercn1 0 SQL (2)
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data. 49 1907066468 tkvercn1 0 SQL (2)
50 quit;
Your syntax is not supported by the SQL Standard. If I try to run the SQL in SQL Server, I get a message like:
Msg 8121, Level 16, State 1, Line 2
Column 'cidb.cdm.CI_CONTACT_HISTORY.CELL_PACKAGE_SK' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
But SAS Proc SQL does support this syntax. But it requires some additional processing on the compute tier to accomplish it.
Even though SAS Proc SQL does support it, I not sure it will produce the results you want. Since the Max(Cell_Package_SK) is evaluated at the Customer level. The Group by is Customer, so the Max is the Max(Cell_Package_SK) for that customer. But the rows being considered for Max will be filtered by any Where clause, so assuming you include Campaign_CD = criteria, then every Customer in that Campaign will have a Max(Cell_Package_sk) value that matches 1 CH row. (possibly more if RTDM contacts are also available). So the count you receive from a Select node would be the same as just counting Customers that have Campaign_Cd = 'YourCampCd' and Offer_ID = 'Whatever Value you specified for Case='
If you're wanting to use the Calc Field on Export, then the Case statement moves from the Having clause to the Selection field list. In this case, your export settings, controlling the 1-Many relationship between Customer and contact History, will determine if you get 1 row per Customer or many rows per customer. Let's assume you want multiple rows per customer and then will look at rows with values that are not 'NA'. Each customer will have at least 1 row with non 'NA' value. You likely would want to include a Export Refinement to control the 1-Many relationship, like a Select Node with Campaign_Code='CAMP294'
In My system, the export returned:
"INDIVID","MaxCellSKCaseResults","CAMPAIGN_CD"
6,"CELL227","CAMP92"
6,"NA","CAMP92"
7,"CELL221","CAMP92"
7,"NA","CAMP92"
13,"CELL221","CAMP92"
13,"NA","CAMP92"
14,"CELL227","CAMP92"
14,"NA","CAMP92"
15,"CELL229","CAMP92"
15,"NA","CAMP92"
I'd like to understand what you're trying to accomplish with this Calculated field. What results you'd like to achieve. Seems more relevant for Export than Selection.
Possibly the example you're using with Max(Cell_Package_SK) is made up to test a concept against a table that has 1-Many relationship with the customer. And you want to do similar Calc Field against a Transaction Table.
Also, what release of MA are you using. And can you provide a tech support track number? Possibly it provides more info or I can weigh in on the Track relative to my similar use.
Hope this helps,
-Steve
