Hi everyone.. I need your help to figure out my problem. This is my
original statement before i adding up CASE statement:
SELECT INSTANCE_NAME, GENESIS_PORT,
LTRIM(SYS_CONNECT_BY_PATH(API_NAME || ':' ||
TRIM(TO_CHAR(MAX_TIME,'990.9999'))|| 's','; '), ';') AS MAX_TIME_API
FROM ( SELECT INSTANCE_NAME, GENESIS_PORT, MAX_TIME, API_NAME,
ROW_NUMBER() OVER
(PARTITION BY INSTANCE_NAME ORDER BY MAX_TIME DESC) IM,
COUNT(*) OVER
(PARTITION BY INSTANCE_NAME) CNT
FROM GEN_API_PERFORMANCE
WHERE INSTANCE_NAME IS NOT NULL
AND MAX_TIME > 60
AND STATISTICS_DATE>SYSDATE-1/24 AND
HOSTNAME='10.228.51.41'
AND API_NAME NOT IN
('_getMethods','genesisClearServerPerformance')
)
WHERE LEVEL = CNT
START WITH IM = 1
CONNECT BY PRIOR INSTANCE_NAME = INSTANCE_NAME AND PRIOR IM = IM - 1
This is the result:
INSTANCE_NAME...........GENESIS_PORT................MAX_TIME_API
MatlMgr_QueryLeaded.....50039................
getTranlogForLastActivityCode:62.3567s
MatlMgr_Script..........50016.................setMESParmValue:65.9336s
After i adding up CASE statement into My ORIGINAL statement, its look
like this:
SELECT INSTANCE_NAME, GENESIS_PORT,
(CASE
WHEN MAX_TIME > 60 THEN
LTRIM(SYS_CONNECT_BY_PATH(API_NAME || ':' ||
TRIM(TO_CHAR(MAX_TIME,'990.9999'))|| 's','; '), ';') ELSE 'NULL'
END) AS MAX_TIME_API
FROM ( SELECT INSTANCE_NAME, GENESIS_PORT, MAX_TIME, API_NAME,
ROW_NUMBER() OVER
(PARTITION BY INSTANCE_NAME ORDER BY MAX_TIME DESC) IM,
COUNT(*) OVER
(PARTITION BY INSTANCE_NAME) CNT
FROM GEN_API_PERFORMANCE
WHERE INSTANCE_NAME IS NOT NULL
AND STATISTICS_DATE>SYSDATE-1/24 AND
HOSTNAME='10.228.51.41'
AND API_NAME NOT IN
('_getMethods','genesisClearServerPerformance')
)
WHERE LEVEL = CNT
START WITH IM = 1
CONNECT BY PRIOR INSTANCE_NAME = INSTANCE_NAME AND PRIOR IM = IM - 1
And the result for all MAX_TIME_API is NULL. Result:
INSTANCE_NAME...........GENESIS_PORT................MAX_TIME_API
MatlMgr_QueryLeaded.....50039..........................NULL
MatlMgr_Script..........50016..........................NULL
MatlMgr_ConfigUpdate....50019..........................NULL
MatlMgr_Misc............50031..........................NULL
It supposed both MatlMgr_QueryLeaded and MatlMgr_Script got value for
MAX_TIME_API. Why this happened? I think my sql was wrong.. I need
your help.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"oracleguru" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at http://groups.google.com/group/oracleguru
-~----------~----~----~----~------~----~------~--~---
