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)
)
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;