Search This Blog

Sunday, March 7, 2010

Creating Records in PeopleSoft: SQL views

A SQL view is not a Physical SQL table in the database. It represents the data that is present in different physical SQL tables. Thus no DML operations can be performed on a view.
If you try to create a View with DML operation say INSERT statement and try to build the SQL View, it will generate an Error message "ORA-00928: missing SELECT keyword".

To create a View, click on File --> New and select the Record from the definition list.
Insert the fields that you want to see into the record definition.
Click on record Type Tab and select Record Type as 'SQL View'.
Now when you try to click to open SQL Editor, it will ask you to first save the record definition.
Now its important to make sure whether the view you are creating is a Non-Standard SQL table name or not. Generally, all the records created in the App Designer will be prefixed by default with "PS_". So If you create a record with name 'TEST_VW', it will be created as PS_TEST_VW. If you want the record to deviate from the normal peoplesoft record naming convention , you should specify that name in the 'Non-Standard SQL Table Name:". In simple terms, your own convention.
Also you can observe that the build sequence number is '1' by default. This will show the number of instances that are available for the view.

After saving the record , open SQL editor and type the Select SQL statement.
Note:
1. See that the select columns match the same order and type of the fields mentioned in the record definition.
2.Donot put Semicolon[;] at the end of the SQL statement. It will be treated as an invalid character .

Now build the SQL View with appropriate settings. Below the build editor, shows the status of the build action.
If there are 0 errors and 0 warnings, the view is successfully build.
Hope the info is helpful.
Will share you about 'Dynamic View' in next post. In short, Dynamic view is a SQL that is executed in runtime.

No comments: