As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
Yes, you must create a master file for the Stored Procedure which is an option when creating a new Synonym using the GUI. At least in v8.09. I don't remember this in earlier versions.
Often our Call Center workers want to run a Client's Dashboard as if they are one of our Client Users. To see the Dashboard exactly the way the Client does. To do this, the Call Center worker runs a report, selects (drills down) on the Client User ID, which calls a fex with the stored procedure, passing both the Client User ID and the Call Center's WebFOCUS UserName. The SP, CLONEs the Client User security codes (cags) under the Call Center workers Dashboard profile and returns a code for either success or error.
The SP looks just like any other TABLE FILE statement, it's just the parameters must have an @ char as a prefix.
The drill down scenario if the same. Below we are passing a field value to a parameter with the same name;
DRILLMENUITEM='Select to replace your security profile',
The variables, passed to the stored procedure will look like the following;
TABLE FILE TEST
BY LOWEST @RETURN_VALUE
BY LOWEST @USERID
WHERE ( @IBIMR_USER EQ '&IBIMR_user.EVAL' ) AND ( @USERID EQ &USERID.EVAL );
The Master File will look like the following; FILENAME=TEST, SUFFIX=SQLMSS , $ SEGMENT=INPUT, SEGTYPE=S0, $ FIELDNAME=@IBIMR_USER, ALIAS=P0001, USAGE=A100V, ACTUAL=A100V, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ FIELDNAME=@USERID, ALIAS=P0002, USAGE=I11, ACTUAL=I4, MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $ SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $ FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $This message has been edited. Last edited by: Don Garland,
In my version below, I left in, but commented out, WAZ's code so that you could see that it may be possible for this to work. Hopefully, someone will read through this pots and and offer up some greater insight and a solution to passing the array.
Hope this helps a little and I'm looking forward to more input on this.
Here is the FEX;
-DEFAULTH &COUNTRY = '_FOC_NULL'
TABLE FILE CAR
-*PRINT COMPUTE FILTER_LIST/A100 = '''' || COUNTRY || ''',';
BY HIGHEST 1 COUNTRY
ON TABLE HOLD AS A_FILTER
TABLE FILE TST_PR_SELECT_COUNTRY_INF
-*WHERE @COUNTRY IN (
WHERE @COUNTRY EQ '&COUNTRY'
I modified your SQL Function a bit so that it would accept a single value I have a CAR table in our SQL database. It's handy for testing things like this.
ALTER PROCEDURE [dbo].[PR_Select_Country_Inf]
where country in (@country)
Yes, This is the way I used to create functions/procedures and pass the variable in comma separated format.
But here we have more than 50 functions; All functions have parameters whose data types are user defined table types which are created by our client. So I need to pass the parameter in the form of table.
Because I can't ask them to change the parameter's datatype and update the query in all the functions as they are calling those functions from different platform.
I hope I have given you enough information on this. Let me know if you need more information.
Where is the data that will be used to create the parameter table? If it is on the same database as the stored procedure, I'm wondering if you could use SAME_DB and create the parameter table as a #temporary table on the DBMS?
The on table statement would look something like the following;
ON TABLE HOLD AS PTABLE FORMAT SAME_DB PERSISTENCE GLOBAL_TEMPORARY