On Mar 15, 11:04 pm, Jack <[email protected]> wrote:
> 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.
Why did you remove the AND MAX_TIME > 60 condition from your inlline
view when you added the CASE statement? You're now returning ALL of
the records from that table which meet your remaining conditions
regardless of the MAX_TIME value. And your CASE statement sets the
MAX_TIME_API value to NULL for any MAX_TIME less than 60. A CASE
statement won't restrict your result set, it merely changes what is
displayed based upon whether the condition evaluates to TRUE or
FALSE. I see no reason to have changed your WHERE clause to eliminate
the AND MAX_TIME > 60 condition; doing so, in conjunction witih your
CASE statement logic, has produced the results you now see.
You should change your WHERE clause back to its original form. You
might then see the results you expect.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" 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/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
