CODEWITHSUNDEEP
sqloracle-databasejoin
That Robin
That Robin

Reputation: 85

Join Oracle tables on an exact match, and a closest match

I am trying to join two tables of performance metrics, system stats and memory usage. Entries in these tables come in on differing time schedules. I need to join the tables by finding the exact match for the System_Name in both tables, and the closest for WRITETIME. Write time uses the systems own idea of time and is NOT a standard Oracle timestamp.

I can select the closest timestamp from one table with something like:

select "Unix_Memory"."WRITETIME", ABS ('1140408134015004' - "Unix_Memory"."WRITETIME") 
  as Diff from "Unix_Memory" 
  where "Unix_Memory"."WRITETIME" > '1140408104015004' order by Diff;

The constants there will be parameterised in my script.

However when I try to expand this into my larger query:

select "System"."System_Name", "System"."WRITETIME" as SysStamp,
from "System" 
join "Unix_Memory"  on "System"."System_Name" = "Unix_Memory"."System_Name" 
and "Unix_Memory"."WRITETIME" = ( 
select Stamp from (
select "Unix_Memory"."WRITETIME" as Stamp, 
ABS ( "System"."WRITETIME" - "Unix_Memory"."WRITETIME") as Diff 
  from "Unix_Memory" where "Unix_Memory"."WRITETIME" > '1140408104015004' and rownum = 1 order by Diff
  )
)
WHERE "System"."System_Name" in ('this','that', 'more')
and   "System"."WRITETIME"  > '1140408124015004';

I get:

Error at Command Line:38 Column:72
Error report:
SQL Error: ORA-00904: "System"."WRITETIME": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I have tried a few variations, but I am not getting any closer.

Upvotes: 0

Views: 434

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Unfortunately the column names are only known in the next nesting level. So System.writetime would be known in select Stamp from ..., but no more in select "Unix_Memory"."WRITETIME" as Stamp ...

Anyhow, you would select a rather random stamp anyhow, the first Unix_Memory"."WRITETIME" > '1140408104015004' found to be precise, because rownum = 1 gets executed before order by. You will have to re-write your statement completely.

EDIT: Here is one possibility to re-write the statement using MIN/MAX KEEP:

select 
  s.system_name, 
  s.writetime as sysstamp,
  min(um.id) keep (dense_rank first order by abs(s.writetime - um.writetime)) as closest_um_id
from system sys 
join unix_memory um  on s.system_name = um.system_name 
where s.system_name in ('this','that', 'more')
and s.writetime > '1140408124015004'
and um.writetime > '1140408104015004'
group by s.system_name, s.writetime
order by s.system_name, s.writetime;

If you need more than just the ID of unix_memory then surround this with another select:

select   
  sy.system_name, 
  sy.sysstamp,
  mem.*
from
(
  select 
    s.system_name, 
    s.writetime as sysstamp,
    min(um.id) keep (dense_rank first order by abs(s.writetime - um.writetime)) as closest_um_id
  from system sys 
  join unix_memory um  on s.system_name = um.system_name 
  where s.system_name in ('this','that', 'more')
  and s.writetime > '1140408124015004'
  and um.writetime > '1140408104015004'
  group by s.system_name, s.writetime
) sy
join unix_memory mem on mem.id = sy.closest_um_id
order by sy.system_name, sy.sysstamp;

Upvotes: 1

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

You must state the System table in the inner Select as well.

select "System"."System_Name", "System"."WRITETIME" as SysStamp,
from "System" 
join "Unix_Memory"  on "System"."System_Name" = "Unix_Memory"."System_Name" 
and "Unix_Memory"."WRITETIME" = ( 
select Stamp from (
select "Unix_Memory"."WRITETIME" as Stamp, 
ABS ( "System"."WRITETIME" - "Unix_Memory"."WRITETIME") as Diff 
  from "Unix_Memory" 
  -- THE NEXT LINE IS MISSING IN YOUR CODE
  INNER JOIN "System" ON "System.System_Name" = "Unix_Memory"."System_Name"
                     and "System"."WRITETIME"  > '1140408124015004'
  -- end of missing
  where "Unix_Memory"."WRITETIME" > '1140408104015004' and rownum = 1 order by Diff
  )
)
WHERE "System"."System_Name" in ('this','that', 'more')
and   "System"."WRITETIME"  > '1140408124015004';

Upvotes: 1

Related Questions