Creating SQL View and Dynamic View Select Statements

If you are creating a SQL view or dynamic view record definition, you must enter a SQL view Select statement to indicate which field values you want to join from which tables. The only difference between the standard view and dynamic view is that the dynamic view is not defined as a view to the database; it is stored on the client and run as a Select at runtime. Dynamic views avoid some constraints on views on some platforms.

Field or Control	Description
Non-Standard SQL Table Name	Override the standard convention of prefixing each record name with PS_.
Build Sequence No (build sequence number)	Set the order in which the view is to be created. The default value is 1 when the record or view is initially created. Views that must be created first can be set to 0, while views that you want created last can be set to any number from 1 to 99. The build sequence number is stored with the other details of the record or view in the database.
Click to open SQL Editor	The view text is saved when the record is saved by selecting File, and then Save. The record must be saved first, before you open the SQL Editor.

As a general guideline, the SQL used for dynamic views should be as simple as possible because the runtime system appends WHERE clauses to the SQL definition to add key field lookup criteria and does not parse the dynamic view SQL definition to find correlation names. For example, we do not recommend the following format:

SELECT...FROM(SELECT * FROM...WHERE...)

Rather than having a WHERE clause in the subquery, one approach would be to create a static view that the dynamic view references. For example, that static view might be:

...
ABC_PRD_INS_VW as

SELECT B.SETID AS SETID 
 , A.BO_ID_CUST AS BO_ID_CUST 
 , A.INST_PROD_ID AS INST_PROD_ID 
 , B.PRODUCT_ID AS PRODUCT_ID 
 , B.DESCR AS DESCR 
 , A.SERIAL_ID AS SERIAL_ID 
 , A.INSTALLED_DATE AS INSTALLED_DATE 
  FROM PS_RF_INST_PROD A 
  , PS_PROD_ITEM B 
  , PS_RF_INST_PROD_ST C 
  , PS_PROD_PGRP_LNK D 
 WHERE A.SETID = B.SETID 
   AND A.PRODUCT_ID = B.PRODUCT_ID 
   AND A.SETID = C.SETID 
   AND A.INST_PROD_ID = C.INST_PROD_ID 
   AND C.INST_PROD_STATUS <> 'UNI' 
   AND A.SETID = D.SETID 
   AND A.PRODUCT_ID = D.PRODUCT_ID 
   AND D.PRODUCT_GROUP = '00'

Which is referenced by the dynamic view:

 SELECT SETID 
 ,BO_ID_CUST 
 ,INST_PROD_ID 
 ,PRODUCT_ID 
 ,DESCR 
 ,SERIAL_ID 
 ,INSTALLED_DATE 
  FROM PS_ABC_PRD_INS_VW 
 WHERE SETID = :1 
   AND BO_ID_CUST = :2 
   AND INST_PROD_ID = :3

Note:

The order of the columns in the Select statement must be identical to the field order in the corresponding record definition. Also, only certain types of meta-SQL statements can be used in view text.

Note:

Related language records should not use dynamic views but should be limited to physical views and records.