how to run a select query from _SYS_BIC with a parameter

[RezaRehman]

Hello Experts,

I'm trying to run a select query from within the B1 client from a column view residing in _SYS_BIC. The query runs fine on it's own, but when i put an input parameter with [%0], it throws an error. Here's the query i'm running:

SELECT *
FROM "_SYS_BIC"."emco.production.case/RejectionByProcess" T0
WHERE T0."ItemCode" = [%0]

Does SAP support filters on such queries? 

Thanks

SAP Business One, version for SAP HANA 

[EunSeok_Bang · Accepted solution]

Hello, 

You need to add a commented line to bind the [%0] with UDT field. 

In your case, add a new UDT, for example, @UDQ_RejectionByProcess and add an UDF, for exmple, U_ItemCode on it, with alphanumeric type.

then add the following in front of your UDQ.

/*SELECT FROM "@UDQ_RejectionByProcess" T0 WHERE T0."U_ItemCode" = [%0];*/

Regards
EunSeok

[RezaRehman · Accepted solution]

Hi EonSeok,

Thanks for this tip. It worked.

I have a follow up question:

I tried to use the query in a dashboard but i get the error "Cannot parse parameter or the query". I want to have an input parameter as the filter. 

Any tips? 

Thanks

[EunSeok_Bang · Accepted solution]

Hello, this approach applied to UDQ when using a calculation view or procedure. In case of the pervasive dashboard in B1 HANA, please use the filter and parameter in the pervasive dashboard designer. refer to the how-to-guide for more details - https://help.sap.com/doc/d07db3419ead4e7c821ddc07c601287d/10.0/en-US/How_to_Work_with_Pervasive_Anal... .