• 1 reply
  • Latest Post - ‏2013-08-26T00:47:48Z by DonaldN
1 Post

Pinned topic Populating value within state-based record type with the result of a SQL query?

‏2013-08-25T15:59:53Z | clearquest

I've added a field to my default record type and I'd like to display the field to the end user on the main form of the record type.

I want to populate the field with a single value (int) that is result of a fairly complex SQL query.

What is the best way to have CQ run the query dynamically each time the record is viewed by the end user?

Or should I ask the DBA to somehow populate the database column for me using PL/SQL?

I'd like to use CQ if at all possible.  One thing to note is that the SQL query will access other tables in the CQ user database.


Daabase type is Oracle.

  • DonaldN
    287 Posts

    Re: Populating value within state-based record type with the result of a SQL query?


    You probably have to do it in Oracle using PL/SQL, because no scripts will be run when you just "view" a CQ record. But you have to realize that updating a CQ record without using CQ API is not supported by IBM.

    I hope that the particular field does not contain critical value to your business, since  it is very difficult to accurately translate the user "view" action to an event in the application or the database. For example, GetEntity() and LoadEntity() both get a CQ record, and do you consider both APIs as "view"? Also remember that these two APIs can be called by other actions, not just an actual user "view" action. If you are going to do it at the database level, are you going to set the Oracle trigger for every SELECT statement issued to the said table? Depending on how complex the SQL query is, you may or may not see a performance hit.