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!
Optum
US
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.
JPMC
US
Thank you Ravi Kumar Pisupati for your response! We found the issue and corrected it. It seems we were using single quote for dynamically generated comma separated values. We tried without single quote around {ASIS:commaSeparatedValues} and it seems its working now.
Pegasystems
IN
Note that support for comma separated values is available in report definitions. Any reason why RDB-List is still being used?
JPMC
US
Thank you Rajiv Nistala for the response!
my scenario doesn't allow me to use RD due to multiple reason, with more prominent one - 'Query need under discussion involves two different databases'.
Pegasystems
IN
You mean two different physical databases using DB Links or two different schemas in the same database? If it means to join across different schemas in the same DB, it is already supported by RD - the two different classes can have their respective schemas specified in the Data-Admin-DB-Table instance. If you have different Data-Admin-DB-Name for the classes even though they map to the same DB, the restriction has been removed from 7.1.9 in RD. It is assumed that the primary class Data-Admin-DB-Name instance credentials has the permissions to access the other resources.
JPMC
US
Yes Rajiv Nistala, I am referring to two different DB instances not schema's under same db, we tried using db link way tablename@dblink_name in connect-sql rule but due to different user id/password for application in these 2 DB we could not go that route because there are too many red-tapes to get a new app user id on db side in my organization. So as per my understanding RD is not an option for my scenario. So it seems even if we are on 7.1.9 we can not make this work until we have same app user id in both DBs.
Pegasystems
IN
Thanks Dhirendra Pal Singh for the details. I am still confused how RDB-List is solving your problem. Your entire query should run in one DB connection (at the JDBC level) and that connection will have credentials for only 1 DB (not the other one, since you mention both have different users). If indeed RDB-List is going to use only 1 connection (and thus the user of 1 DB for executing the query), then RD should work fine as well.
Accepted Solution
JPMC
US
Sorry for delayed resonse Rajiv Nistala! Yes, it did resolve my query. What I did is that I retrieves all the values from one Database A using Obj-Browse and then iterated over this list to form the comma separated string in an activity step and then put this value on a page property which I used as a bind variable in the RDB_list rule which runs against database B. I tested by setting more comma separated values with length of more than 5000 characters in a java step and tested and it seems it worked.
Pegasystems
IN
Great to know that it worked.