Using Dynamic View

Dynamic View:
'View' is nothing but a static sql that pulls data from atleast one record.
'Dynamic View' enables to pass values dynamically ( at runtime ) to the where clause.
Example:
Lets think of a page which has country and state fields.
RECA.COUNTRY -->RECORD.FIELD
RECA.STATE -->RECORD.FIELD

The state field prompt should show the list of states for the country selected in the Country field prompt.
That means the value of the state field depends on the value of the country field.
In this case , we can use dynamic view for the state field ( STATE ).
PS_STATE_DVW -- This should contain STATE as a key field and listbox item and leave the SQL definition as blank and Save.

Setting Record field properties in App designer.

Now add this Dynamic view as prompt to the record field STATE .

Until now , we have only defined that the STATE record will use the PS_STATE_DVW as Dynamic view. But we have not injected the SQL to this dynamic view to pull the required data.

Below is the code, which builds the SQL and attaches to the SQL Editor of the Dynamic view.

Now that the code is ready , we need to know where exactly to place this code? Since the user selects the 'Country value' from the country field , in the COUNTRY field , field change event we can place the below code.

/** Peoplecode Begin **/
/** This is the SQL string which is used to pull the states for a given country **/
&str = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = (:1) ";
/** ExpandSQLBinds will expand the &str and inserts the values into bind param **/
/** &sql_text_defn = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = 'IND'" assuming 'IND' is selected on the page. **/
&sql_text_defn = ExpandSQLBinds(&str, RECA.COUNTRY.value);
At this stage the SQL string is ready with its dynamic value (country).
Now, we need to pass this SQL string has to be injected to the Dynamic view SQL Text definition.
/** Passing the SQL string to the Dynamic view **/
RECA.STATE.sqltext = &sql_text_defn;

/*** Peoplecode End ***/
In the PIA, now once we select 'IND' for country field and select on STATE prompt, all states for given country will be listed.
This completes the usage of Dynamic View.