Writing an sql script to query a calculation view and an attribute view inside a calculation view

[Former Member]

Hi,

I was trying to query a calculation view with sql , and as one of the input parameters i was trying to give a query over an attribute view

So my sql will look like this

SELECT * FROM "MyApp.calculation_views::GET_CHECKLIST_FOR_ACCOUNT"('PLACEHOLDER' = ('$$ACCOUNT_ID$$', '1','$$CHECKLIST_ID$$',' SELECT CHECKLIST_ID FROM "MyApp"."CHECKLIST_PRODUCTS" WHERE PRODUCT_ID = 5'));

So as an input to $$CHECKLIST_ID$$   i wanted to give an id selected from checklist products table where products id is passed as an input

but this is not working, i am getting syntax error at the $$CHECKLIST_ID$$ there i can only pass values like  $$CHECKLIST_ID$$, '1'

Please help me to figure out which  is the correct syntax  i tried putting the sql statement to get the checklist_id in '' but it too didnot work

[henrique_pinto]

I'm not sure you'll be able to do a SELECT subquery directly in the placeholder definition, but you can create another calc view (scripted) as a wrapper to your original calc view and use a scalar variable to pass the parameter over.

Try this as the base code for your second calc view:

var_id VARCHAR(10);
BEGIN

     SELECT CHECKLIST_ID INTO :var_id FROM "MyApp"."CHECKLIST_PRODUCTS" WHERE PRODUCT_ID = 5;

     var_out = SELECT * FROM "MyApp.calculation_views::GET_CHECKLIST_FOR_ACCOUNT"
          (PLACEHOLDER."$$ACOUNT_ID" => 1, PLACEHOLDER."$$CHECKLIST_ID$$" => :var_id);

END

You could even create input parameters in the second Calc View for the Account Id & Product Id (which are currently hard coded in your code below).

Best,

Henrique.

PS: avoid using SELECT * for the var_out in the scripted calc view. It's better, from a code maintenance perspective, to explicitly define the columns you're outputting.

[Former Member]

Thanks a lot Henrique,

Sorry for the delay, i will try it out and post the response here