I'm trying to write an SQL Select statement that will retrieve certain data from my requests in the database. I looked up the field I'm after, called Application, with a token of APPLICATION_CODE. This is in the header, but under the Storage Tab I see that it is batch 1, parameter 10. (see attachment). When I do a Select on this I get nothing, yet it has to be stored somewhere because if you go through the UI, virtually every request has an Application associated with it. Any suggestions?
SELECT Parameter_column_number, Prompt, Batch_number, Parameter_token,Display_flag, Enabled_flag FROM PRODPITG.KNTA_PARAMETER_SET_fields_V WHERE context_value='31270' --AND source_parameter_set_id=906 ORDER BY 3, 1
Run the above Query, In the CONTEXT_VALUE please use your Header type ID. and see the results.
The Application Will not have Batch Number, This field is stored in KCRT_REQUESTS.
This is something I did not know - Thanks! Unfortunately the query did not tell me where the data is. For example, if I do: SELECT rh.parameter10 APPLICATION, count(*) VALUE FROM KINTANA.KCRT_STATUSES s, KINTANA.KCRT_REQUESTS r, kintana.kcrt_request_types rt, KINTANA.KCRT_REQ_HEADER_DETAILS rh... this returns a Value of 51 but blank for rh.parameter10 Application. In my attachment, you can see it's there. And as a user, I can pick Application and it shows in the interface, even after the form is closed - so it must be stored somewhere.
In a typical default request header type, the Application field is stored in KCRT_REQUESTS.APPLICATION and thus is also available for lookup in KCRT_REQUESTS_V.APPLICATION and KCRT_REQUESTS_V.APPLICATION_CODE.
From your workbench snapshot it is showing a batch & parameter column, so this means you're doing something custom and have 'moved' that field into the request details section. You should be able to find the data in KCRT_REQUEST_DETAILS.PARAMETER10.
We're making progress. Now, in my Select statement, I'm seeing the code that is stored in the validation: PPM - Application - Enabled. So in my query: select r.application from kcrt_requests r where... I need to cross reference to the validation. Is that easy?
OK, thanks for everyone's help. I learned a lot! Here's what I ended up with to get at the pesky Application code and meaning: SELECT r.application, (select meaning from knta_lookups where lookup_type = 'APPLICATION' and enabled_flag = 'Y' and lookup_code = r.application) APP,... From KINTANA.KCRT_STATUSES s, KINTANA.KCRT_REQUESTS r,...