Hi,

From CPMHC, we have a RDB-List rule called -CAGetCampaignsByType. This is being used in an activity:CAGetCampaigns by passing the params from this activity like below. Also, below is the query constructed from this rule. Check if, this helps for you.

SQL from the above RDB-List rule:

select Distinct (NAME) as "Name",

campTable.PXOBJCLASS as "pxObjClass",

(select max( to_date( workTable.DateOfOffer,'yyyyMMdd' ) )

         from pca_work workTable

         where workTable.pxObjClass ='PegaCA-Work-Opportunity'

           and campTable.Name = workTable.CampaignName ) as "MaxDate"

from {Class:Rule-PegaCA-Intent-Campaign} campTable,

     {Class:PegaCA-Work-Opportunity} workTable

where (to_date(campTable.CAMPAIGNSTARTDATE,'yyyymmdd') <= to_date({CAIntentCampaignParam.Today},'yyyymmdd')

       and to_date(campTable.CAMPAIGNENDDATE,'yyyymmdd') >= to_date({CAIntentCampaignParam.Today},'yyyymmdd'))

   and pyClassName in ( {Asis:CAIntentCampaignParam.ClassNameAncestors} )

   {Asis:CAIntentCampaignParam.TypeClause}

   {Asis:CAIntentCampaignParam.ExistingIntents}

   and campTable.Name = workTable.CampaignName(+)

   and workTable.pxObjClass(+) ='PegaCA-Work-Opportunity'

   and ((((select max( to_date( workTable.DateOfOffer,'yyyyMMdd' ) )  + campTable.OfferFrequency

         from pca_work workTable

         where workTable.pxObjClass ='PegaCA-Work-Opportunity'

           and campTable.Name = workTable.CampaignName)  <  to_date({CAIntentCampaignParam.Today},'yyyymmdd' )) 

           or workTable.CampaignName is null)

           or campTable.OfferFrequency = 0)

Thanks,

Ravi Kumar.