Question
How do we pass comma separated values to RDB_list
Hi All - I am having trouble passing dynamically generated comma separated values in an activity to an IN CLAUSE of where clause of RDB-List rule. I am storing it in a property and then using the property reference in RDB rule using ASIS keyword but instead od considering it a comma separated values inside "IN CLAUSE" it is treating it as single string. How do I tell system to consider it as comma separated values and not a single string?
We tried creating a Oracle function but could not make it work for all scenario - this function works well till length of comma separated values is less than 4000 characters (max limit of varchar2). We tried to make function parameter of TYPE CLOB but it still gives ORA-01704: string literal too long.
you may ask that why am I not using the inner query to feed in all comma separated values to main query - The reason we are unable to use inner query is because the inner query get values from one database and main query runs against another database.
Any help would be highly appreciated. Thanks!
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.