Skip to Content
Apr 30, 2020 at 12:58 PM

Extract data from IBP | Select ALL if input parameter is empty or NULL


Hello All,

I am extracting data from a calculation scenario of IBP from CPI-DS task. As you may know, we need to be careful while extracting data from IBP because a bad select query can affect the performance. Imagine I would like to query the table using where condition on 'CUSTOMERID'. This customer ID will be given as input to the CPI-DS task through a global variable $CUSTOMERID. This works fine if the business user gives a customer ID as input. The where condition is pushed down to IBP but if the business user does not give an input to this parameter, cpi-ds should still extract all the data from IBP whether customerid is null or customerid is not null. How can I achieve this in CPI-DS?

Ofcourse I can initialise a flag $inputgiven = 0 when $customerid is empty in pre-load script and use the following where condition.

(0 = $inputgiven or ibp.customerid = $customerid)

But such a condition will not pushdown ibp.customerid = $customerid to IBP, this will affect the performance because IBP has to query all data and send it to CPI-DS and then CPI-DS shall apply the where filter on the dataset.

Creating multiple dataflows is not an option because I may have many other where conditions and I cannot create dataflows for each and every combinations of those filters. It just not maitainable.

Any ideas?