sqloracle-databasegreatest-n-per-group

oracle, how to use outer table column in joined subquery


give tables below i need to get all record from NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 and for each row get from NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 with

AuditData <= NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008.AuditData
and ID_ELEMENTO_008 = ID_007

using join cannot do it since code will be that and inner condition is not allowed

select * from 

NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008
INNER JOIN
  (SELECT * FROM NAT_DASH_AUD_ELEMENTO_WORKFLOW_007
  WHERE 
    NAT_DASH_AUD_ELEMENTO_WORKFLOW_007.AuditData <= NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008.AuditData 
    AND ID_007 = ID_ELEMENTO_008
  ORDER BY NAT_DASH_AUD_ELEMENTO_WORKFLOW_007."AuditData" DESC
  fetch next 1 rows only)
ON ID_007 = ID_ELEMENTO_008

with subquery in select i'm forced to select one column only

is there a sql standard way to do it without messing up with object or record for that simple requirement?

tables:

CREATE TABLE "NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008" 
   (    "AuditId" NUMBER(9,0),
    "AuditAction" VARCHAR2(250 BYTE) ,
    "AuditUser" VARCHAR2(250 BYTE) ,
    "AuditData" TIMESTAMP (6), 
    "ID_008" NUMBER(12,0), 
    "ID_LINGUA_008" NUMBER(9,0), 
    "ID_ELEMENTO_008" NUMBER(9,0), 
    "NOTE_008" VARCHAR2(4000 CHAR)
)

and

CREATE TABLE "NAT_DASH_AUD_ELEMENTO_WORKFLOW_007" 
(   "AuditId" NUMBER(9,0) ,
    "AuditAction" VARCHAR2(250 BYTE) ,
    "AuditUser" VARCHAR2(250 BYTE),
    "AuditData" TIMESTAMP (6), 
    "ID_007" NUMBER(12,0), 
    "ID_STATO_ORIGINE_007" NUMBER(9,0), 
    "ID_STATO_DESTINAZIONE_007" NUMBER(9,0), 
    "NOTA_OBBLIGATORIA_007" NUMBER(1,0), 
    "ALLEGATO_OBBLIGATORIO_007" NUMBER(1,0), 
    "FINALE_007" NUMBER(1,0), 
    "ID_WORKFLOW_007" NUMBER(9,1)
)

Solution

  • From Oracle 12, you can use a LATERAL join:

    SELECT *
    FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 n8
           CROSS JOIN LATERAL (
             SELECT *
             FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 n7
             WHERE  n7."AuditData" <= n8."AuditData"
             AND    n7.ID_007      =  n8.ID_ELEMENTO_008
             ORDER BY n7."AuditData" DESC
             FETCH FIRST ROW ONLY
           )
    

    or, in earlier versions, use the ROW_NUMBER analytic function:

    SELECT *
    FROM   (
      SELECT n7.*,
             n8.*,
             ROW_NUMBER() OVER (
               PARTITION BY n7.ID_007
               ORDER     BY n7."AuditData" DESC
             ) AS rn
      FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 n8
             INNER JOIN NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 n7
             ON     n7."AuditData" <= n8."AuditData"
                AND n7.ID_007      =  n8.ID_ELEMENTO_008
    )
    WHERE  rn = 1;