Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Use SQL to retrieve Header fields

SOLVED
Go to solution
Highlighted
Greg_Shrout
Collector

Use SQL to retrieve Header fields

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?
9 REPLIES
Mahen M
Honored Contributor
Solution

Re: Use SQL to retrieve Header fields

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.

Regards,
Mahendran M
Mahen M
Honored Contributor

Re: Use SQL to retrieve Header fields

'prodpitg.kcrt_requests_V' view has both the parameter and Visible_parameter.

Regards,
Mahendran M
Greg_Shrout
Collector

Re: Use SQL to retrieve Header fields

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.
Greg_Shrout
Collector

Re: Use SQL to retrieve Header fields

In this attachment, you can see that the data is in fact there, so I must be going after the wrong column or it may be pointing to another table? Any ideas?
Do header values get stored somewhere else?
Erik Cole_1
Esteemed Contributor

Re: Use SQL to retrieve Header fields

Hi Greg,

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.
Greg_Shrout
Collector

Re: Use SQL to retrieve Header fields

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?
Greg_Shrout
Collector

Re: Use SQL to retrieve Header fields

... in order to get at the description or Meaning behind the application code.
Erik Cole_1
Esteemed Contributor

Re: Use SQL to retrieve Header fields

select LOOKUP_CODE, MEANING
from KNTA_LOOKUPS
where LOOKUP_TYPE = 'APPLICATION'
Greg_Shrout
Collector

Re: Use SQL to retrieve Header fields

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,...

Thanks again, everybody!
-Greg
//Add this to "OnDomLoad" event