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

Batch numbers

Highlighted
Ian Wight
Occasional Contributor

Batch numbers

I am trying to include as a filter in a new portlet a field that has a batch number of 2 - the other filters are batch 1 - and parameter 19. I can make the filter appear in the portlet but it will not work, and returns no data when i know there should be some. Do I need to mention the batch number in the where clause of the sql query and, if so, how. At the moment, because there is another field from that table (shorthand rd.), that needs batch 1, the query reads AND rd.batch_number=1. I have tried adding another AND rd.batch_number=2, but that didn't work at all. Would appreciate any advice. Thank you
13 REPLIES
Erik Cole_1
Esteemed Contributor

Re: Batch numbers

Hi Ian,

Sounds like you need to do a self-join on the request details table. See this thread for an example...

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1408010
Ian Wight
Occasional Contributor

Re: Batch numbers

Thanks. I thought I had done this by adding the AND rd.batch_number = 2, but that brought back no results. Thanks for your help, though
Erik Cole_1
Esteemed Contributor

Re: Batch numbers

If you're just using

AND rd.batch_number = 1
AND rd.batch_number = 2

you won't see results because that condition is never true. You need to use a self-join, and refer to the correct table depending on batch:

select a.field1,a.field2
-- self-join, a=batch 1, b=batch 2:
from kcrt_request_details a,
kcrt_request_details b
where a.batch_number = 1
and b.request_id = a.request_id
and b.batch_number = 2
-- batch 1 filters:
and a.parameter1 = 'whatever'
-- batch 2 filters:
and b.parameter1 = 'whatever'
Ian Wight
Occasional Contributor

Re: Batch numbers

Thank you for your help but I still have problems, probably because I am trying to bring some data back from the kcrt_request_details table that is batch 1, and another set that is batch 2. I have tried to follow what you advised but obviously have got it wrong as i get problems with the GROUP BY expression. i have included two bits of code: the first works fine and only includes stuff from batch 1, the second doesn't. thank you very much
Mahen M
Honored Contributor

Re: Batch numbers

Hi,
You have two KCRT_REQUEST_DETAILS Table for self join.

Select rd1.parameter1, rd2.parameter1
FROM kcrt_req_header_details rh,
kcrt_request_types rt,
kcrt_request_details rd1,
kcrt_request_details rd2,
kcrt_requests_v r
WHERE rh.request_type_id = rt.request_type_id
AND rt.request_type_name like '%PPLC%Proj%'

AND rd1.batch_number =1
and rd2.batch_number = 2
AND r.request_id = rd1.request_id
AND r.request_id = rd2.request_id


check up whether the above snippet works.

Regards,
Mahendran M
Mahen M
Honored Contributor

Re: Batch numbers

typo - Should have 2 KCRT_REQUEST_DETAILS Table.

Sample join attached.

Regards,
Mahendran M
Ian Wight
Occasional Contributor

Re: Batch numbers

Thank you very much for your efforts but I am still doing something wrong. I get an error telling me i have not used a GROUP BY expression. i enclose all my code and thank you again for any help you can give
Vibhor Dwivedi
Regular Collector

Re: Batch numbers

Paste your query here ..
Ian Wight
Occasional Contributor

Re: Batch numbers

SQL still saying that the GROUP BY expression is wrong
======================================================

SELECT ROUND(SUM(rd.parameter44),2) TOTAL_CYR, ROUND(SUM(rd.parameter34),2) TOTAL_CYR__NEXT_YEAR,
SUBSTR(r.parameter10,15,3) CCY, rd2.parameter19 BD_LEAD


FROM kcrt_req_header_details r,
kcrt_request_types rt,
kcrt_request_details rd,
kcrt_request_details rd2,
kcrt_requests_v r1


WHERE r.request_type_id = rt.request_type_id
AND rt.request_type_name ='Opportunity'
AND r.batch_number =1
AND rd.batch_number =1
AND rd2.batch_number =2
AND r1.batch_number =1
AND r.request_id = rd.request_id
AND r.request_id =rd2.request_id
AND r1.request_id = rd.request_id
AND r1.request_id =rd2.request_id


--This section comes from the filter fields
AND
(
SUBSTR(rd.parameter9,1,10) >= SUBSTR('[P.FROM_DT]',1,10)
AND
SUBSTR(rd.parameter9,1,10) <= SUBSTR('[P.TO_DT]' ,1,10)
)
AND 1=1
AND r1.status_id IN ([P.STS])
AND '[P.AT_PURS_TEAM]'=r1.parameter10
AND '[P.AT_BD_LEAD]'=rd2.parameter19


--End section from the filter fields
GROUP BY SUBSTR(r.parameter10,15,3)
Vibhor Dwivedi
Regular Collector

Re: Batch numbers

SELECT
ROUND(SUM(rd.parameter44),2) TOTAL_CYR,
ROUND(SUM(rd.parameter34),2) TOTAL_CYR__NEXT_YEAR,
SUBSTR(r.parameter10,15,3) CCY,
rd2.parameter19 BD_LEAD
FROM kcrt_req_header_details r,
kcrt_request_types rt,
kcrt_request_details rd,
kcrt_request_details rd2,
kcrt_requests_v r1
WHERE r.request_type_id = rt.request_type_id
AND rt.request_type_name ='Opportunity'
AND r.batch_number =1
AND rd.batch_number =1
AND rd2.batch_number =2
AND r1.batch_number =1
AND r.request_id = rd.request_id
AND r.request_id =rd2.request_id
AND r1.request_id = rd.request_id
AND r1.request_id =rd2.request_id
AND
(
SUBSTR(rd.parameter9,1,10) >= SUBSTR('[P.FROM_DT]',1,10)
AND
SUBSTR(rd.parameter9,1,10) <= SUBSTR('[P.TO_DT]' ,1,10)
)
AND 1=1
AND r1.status_id IN (1)
AND '[P.AT_PURS_TEAM]'=r1.parameter10
AND '[P.AT_BD_LEAD]'=rd2.parameter19
GROUP BY (SUBSTR(r.parameter10,15,3),rd2.parameter19)


Take care of parameter for token value [P.STS] it is on line 28 "AND r1.status_id IN ([P.STS])"

I hope it will help...
Ian Wight
Occasional Contributor

Re: Batch numbers

I am sorry but I don't understand what you mean here. I think the filters are OK - certainly they are visible in the portlet and all bring back the right values when you try to apply them. It is the query itself that does not work, I think. Thanks for your help, though
Jajcen Harris
Occasional Contributor

Re: Batch numbers

hello Mr.Ian,

It sounds like you need it to be a sub-query.
I had to do the same thing for my portlet. here is an example:

AND INSTR('[P.PICK_WEB_CATEGORY]',substr((select det.parameter1 from kcrt_request_details det where det.request_id =req.request_id and det.batch_number = 2), 1, 5)) > 0

notice how I had to bring only a single result out of a nested sub-query in the substr with batch number 2.

Hope this helps

Ian Wight
Occasional Contributor

Re: Batch numbers

Thank you everyone; you were very helpful. I made it work by following the advice to include the table twice, differenly named.
//Add this to "OnDomLoad" event