Oracle : Join 2 result sets

Question

I'm stuck and I need an advice how to join 2 resultsets into 1

The first resultset has thresholds values for different mountpoints. There is a row for each mountpoint with non-default threshold value, eg "u01" - "u08". The default threshold value for all others mountpoints is in the row with the empty MOUNTPOINT value.

HOSTNAME                       MOUNTPOINT             WARNING_PC CRITICAL_P
------------------------------ ---------------------- ---------- ----------
oracledatabase.domain.local                           20         5        <-- here is default
oracledatabase.domain.local    /opt/nfsshare/u01      20         19
oracledatabase.domain.local    /opt/nfsshare/u02      1.01       1
oracledatabase.domain.local    /opt/nfsshare/u03      1.01       1
oracledatabase.domain.local    /opt/nfsshare/u04      1.01       1
oracledatabase.domain.local    /opt/nfsshare/u05      1.01       1
oracledatabase.domain.local    /opt/nfsshare/u07      1.01       1
oracledatabase.domain.local    /opt/nfsshare/u08      1.01       1

The second resultset have the list of all mountpoints

HOSTNAME                       MOUNTPOINT
------------------------------ ----------------------------------------
oracledatabase.domain.local    /
oracledatabase.domain.local    /boot
oracledatabase.domain.local    /dev
oracledatabase.domain.local    /opt/nfsshare/u01
oracledatabase.domain.local    /opt/nfsshare/u02
oracledatabase.domain.local    /opt/nfsshare/u03
oracledatabase.domain.local    /opt/nfsshare/u04
oracledatabase.domain.local    /opt/nfsshare/u05
oracledatabase.domain.local    /opt/nfsshare/u06
oracledatabase.domain.local    /opt/nfsshare/u07
oracledatabase.domain.local    /opt/nfsshare/u08
oracledatabase.domain.local    /opt/nfsshare/u09
oracledatabase.domain.local    /opt/nfsshare/u10
oracledatabase.domain.local    /opt/nfsshare/u11
oracledatabase.domain.local    /opt/nfsshare/u12
oracledatabase.domain.local    /opt/nfsshare/u13

So, I need to join them in following manner: If a mountpoint has a threshold value in the first resultset then use that value. If there are no threshold value for a mountpoint then use default one

HOSTNAME                       MOUNTPOINT           WARNING_PC CRITICAL_P
------------------------------ -------------------  ---------- ----------
oracledatabase.domain.local    /                    20         5
oracledatabase.domain.local    /boot                20         5
oracledatabase.domain.local    /dev                 20         5
oracledatabase.domain.local    /opt/nfsshare/u01    20         19
oracledatabase.domain.local    /opt/nfsshare/u02    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u03    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u04    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u05    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u06    20         5
oracledatabase.domain.local    /opt/nfsshare/u07    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u08    1.01       1
oracledatabase.domain.local    /opt/nfsshare/u09    20         5
oracledatabase.domain.local    /opt/nfsshare/u10    20         5
oracledatabase.domain.local    /opt/nfsshare/u11    20         5
oracledatabase.domain.local    /opt/nfsshare/u12    20         5
oracledatabase.domain.local    /opt/nfsshare/u13    20         5

Added some hours later. It looks like I've found the solution. But possible there is a better approach

COL HOSTNAME2 FOR A30
COL MOUNTPOINT2 FOR A30 

WITH THRESHOLDS AS (
SELECT TARGET_NAME "HOSTNAME1",
       DECODE(LENGTH(REPLACE(KEY_VALUE,CHR(32))),NULL,'DEFAULT',KEY_VALUE) "MOUNTPOINT1",
       WARNING_THRESHOLD "WARNING_PCT",
       CRITICAL_THRESHOLD "CRITICAL_PCT"
FROM
       SYSMAN.MGMT$METRIC_COLLECTION
WHERE
       TARGET_TYPE = 'host' AND
       METRIC_NAME = 'Filesystems' AND
       METRIC_COLUMN = 'pctAvailable' AND TARGET_NAME='oracledatabase.domain.local'
),
MOUNTPOINTS AS (
SELECT ENTITY_NAME "HOSTNAME2",
       KEY_PART_1  "MOUNTPOINT2"
FROM 
       SYSMAN.GC$METRIC_VALUES_LATEST
WHERE
       METRIC_GROUP_LABEL = 'Filesystems' AND  METRIC_COLUMN_NAME = 'available'
       AND ENTITY_NAME='oracledatabase.domain.local'
)
-- Get list of matching mountpoints
SELECT HOSTNAME2, MOUNTPOINT2, WARNING_PCT, CRITICAL_PCT FROM (
SELECT * FROM MOUNTPOINTS INNER JOIN  THRESHOLDS ON 
MOUNTPOINTS.MOUNTPOINT2=THRESHOLDS.MOUNTPOINT1)
-- Union
UNION ALL
-- Get list mountpoints with default threshold
SELECT HOSTNAME2, MOUNTPOINT2, WARNING_PCT, CRITICAL_PCT FROM
(SELECT * FROM MOUNTPOINTS WHERE MOUNTPOINT2 NOT IN (SELECT MOUNTPOINT1 FROM THRESHOLDS)),
(SELECT * FROM THRESHOLDS WHERE MOUNTPOINT1='DEFAULT') 
ORDER BY 2;

HOSTNAME2                      MOUNTPOINT2                    WARNING_PC CRITICAL_PCT
------------------------------ ------------------------------ ---------- --------------------
oracledatabase.domain.local    /                              20         5
oracledatabase.domain.local    /boot                          20         5
oracledatabase.domain.local    /dev                           20         5
oracledatabase.domain.local    /opt/nfsshare/u01              20         19
oracledatabase.domain.local    /opt/nfsshare/u02              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u03              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u04              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u05              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u06              20         5
oracledatabase.domain.local    /opt/nfsshare/u07              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u08              1.01       1
oracledatabase.domain.local    /opt/nfsshare/u09              20         5
oracledatabase.domain.local    /opt/nfsshare/u10              20         5
oracledatabase.domain.local    /opt/nfsshare/u11              20         5
oracledatabase.domain.local    /opt/nfsshare/u12              20         5
oracledatabase.domain.local    /opt/nfsshare/u13              20         5

16 rows selected.

Details on "rs." comment (just for me for the further reference)

Without COALESCE function it easier to understand how it works.

WITH THRESHOLDS AS (
SELECT TARGET_NAME "HOSTNAME1",
       KEY_VALUE "MOUNTPOINT1",
       WARNING_THRESHOLD "WARNING_PCT",
       CRITICAL_THRESHOLD "CRITICAL_PCT"
FROM
       SYSMAN.MGMT$METRIC_COLLECTION
WHERE
       TARGET_TYPE = 'host' AND
       METRIC_NAME = 'Filesystems' AND
       METRIC_COLUMN = 'pctAvailable' AND TARGET_NAME='oracledatabase.domain.local'
),
MOUNTPOINTS AS (
SELECT ENTITY_NAME "HOSTNAME2",
       KEY_PART_1  "MOUNTPOINT2"
FROM 
       SYSMAN.GC$METRIC_VALUES_LATEST
WHERE
       METRIC_GROUP_LABEL = 'Filesystems' AND  METRIC_COLUMN_NAME = 'available'
       AND ENTITY_NAME='oracledatabase.domain.local'
)
SELECT A.HOSTNAME2, A.MOUNTPOINT2, 
       T1.WARNING_PCT "WRN_THR",
       T2.WARNING_PCT "WRN_DFL", 
       T1.CRITICAL_PCT "CRT_THR", 
       T2.CRITICAL_PCT "CRT_DFL"
FROM MOUNTPOINTS A
LEFT OUTER JOIN THRESHOLDS T1 ON A.MOUNTPOINT2 = T1.MOUNTPOINT1 --match
JOIN THRESHOLDS T2 ON COALESCE(TRIM(T2.MOUNTPOINT1),'DEFAULT') = 'DEFAULT';

HOSTNAME2                      MOUNTPOINT2          WRN_THR WRN_DFL CRT_THR CRT_DFL
------------------------------ -------------------- ------- ------- ------- -------
oracledatabase.domain.local    /opt/nfsshare/u01    20      20      19      5
oracledatabase.domain.local    /opt/nfsshare/u02    1.01    20      1       5
oracledatabase.domain.local    /opt/nfsshare/u03    1.01    20      1       5
oracledatabase.domain.local    /opt/nfsshare/u04    1.01    20      1       5
oracledatabase.domain.local    /opt/nfsshare/u05    1.01    20      1       5
oracledatabase.domain.local    /opt/nfsshare/u07    1.01    20      1       5
oracledatabase.domain.local    /opt/nfsshare/u08    1.01    20      1       5
oracledatabase.domain.local    /boot                        20              5
oracledatabase.domain.local    /opt/nfsshare/u06            20              5
oracledatabase.domain.local    /opt/nfsshare/u13            20              5
oracledatabase.domain.local    /dev                         20              5
oracledatabase.domain.local    /opt/nfsshare/u11            20              5
oracledatabase.domain.local    /opt/nfsshare/u12            20              5
oracledatabase.domain.local    /                            20              5
oracledatabase.domain.local    /opt/nfsshare/u10            20              5
oracledatabase.domain.local    /opt/nfsshare/u09            20              5

16 rows selected.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm#SQLRF00617 COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

score 1 · Answer 1 · answered Oct 30 '18 at 14:42

You can achieve this using Original tables, Joins and no CTEs

For Example, assuming your tables are MOUNTPOINTS and THRESHOLDS:

SELECT A.HOSTNAME, A.MOUNTPOINT, 
COALESCE(T1.WARNING_PCT,T2.WARNING_PCT) WARNING_PCT, 
COALESCE(T1.CRITICAL_PCT, T2.CRITICAL_PCT) CRITICAL_PCT
FROM MOUNTPOINTS A
LEFT OUTER JOIN THRESHOLDS T1 ON A.MOUNTPOINT = T1.MOUNTPOINT --match
JOIN THRESHOLDS T2 ON COALESCE(TRIM(T2.MOUNTPOINT),'DEFAULT') = 'DEFAULT'

Notice that i used TRIM here to change default value if it has space(' ') to empty string which is considered as NULL in oracle.

SQL Fiddle

Oracle : Join 2 result sets

1 Answers1