I would like to join a 'conditioned subquery'. Let's imagine that we have a table with certain data containing an ID, a created TS and some or one value:
data_table (ID, created_ts, value) .... "652351, 2018-04-03 06:11:31.996, 1" and so on.
SELECT dt1.ID
, dt1.created_ts
, (SELECT value FROM data_table dt2 WHERE dt1.ID = dt2.ID ) AS value
FROM data_table dt1
This query would recreate the table. We assume that the IDs are unique. The ID of the outer query is determining the data set of the query for the attribute value.
Now I would like to write the same example if a different form. As a join of two queries.
SELECT dt1.ID
, dt1.created_ts
, dt2.value
FROM data_table dt1
LEFT JOIN ( SELECT ID, value FROM data_table WHERE dt1.ID = data_table.ID
) dt2
As I understand it this query should return the same data or result but it is not working. It throws an error:
SQL-Error [904] [42000]: ORA-00904: "dt1"."ID": invalid ID
What is wrong with the second SQL query?
I know that a query like this
SELECT dt1.ID
, dt1.created_ts
, dt2.value
FROM data_table dt1 LEFT JOIN (SELECT value FROM data_table) dt2
WHERE dt1.ID = dt2.ID
would work but this form will not deliver the same data in a more complex example. Thanks
A woriking example:
WITH data_table AS ( select '101' AS ID
, to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
, 3 AS Value
from dual
UNION ALL
select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7 from dual
UNION ALL
select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual
)
SELECT dt1.ID
, dt1.Created_Date
, (Select dt2.value FROM data_table dt2 WHERE dt2.ID = dt1.ID) AS dt2_Value
, dt3.Value AS dt3_Value
FROM data_table dt1
LEFT JOIN ( Select ID, value FROM data_table ) dt3
ON dt3.ID = dt1.ID;
A working example with ununique IDs:
WITH data_table AS ( select '101' AS ID
, to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
, 3 AS Value
from dual
UNION ALL
select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7 from dual
UNION ALL
select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual
UNION ALL
select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
UNION ALL
select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
SELECT dt1.ID
, dt1.Created_Date
, dt1.Value
, ( Select dt2.value
From data_table dt2
Where dt2.ID = dt1.ID
Order By dt2.Created_Date DESC Fetch First 1 Rows Only ) AS dt2_Value
, dt3.Value AS dt3_Value
, dt4.Value AS dt4_Value
FROM data_table dt1
--
LEFT JOIN ( Select ID, Value
From data_table
Order By data_table.Created_Date DESC Fetch First 1 Rows Only
) dt3
ON dt3.ID = dt1.ID
--
LEFT JOIN ( Select ID, Value
From data_table
WHERE Value < 15
Order By data_table.Created_Date DESC Fetch First 1 Rows Only
) dt4
ON dt4.ID = dt1.ID
;
DT3 will join first record of data_table with the identical IDs. And you can also use conditions to further reduce your records of the subquery. But when you want to use data of the table dt1 to reduce the data of the subquery it will fail with the error message.
WITH data_table AS ( select '101' AS ID
, to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
, 3 AS Value
from dual
UNION ALL
select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7 from dual
UNION ALL
select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual
UNION ALL
select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
UNION ALL
select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
SELECT dt1.ID
, dt1.Created_Date
, dt1.Value
, ( Select dt2.value
From data_table dt2
Where dt2.ID = dt1.ID
Order By dt2.Created_Date DESC Fetch First 1 Rows Only ) AS dt2_Value
, dt5.Value AS dt5_Value
FROM data_table dt1
--
LEFT JOIN ( Select ID, Value
From data_table
WHERE data_table.Value = dt1.Value
Order By data_table.Created_Date DESC Fetch First 1 Rows Only
) dt5
ON dt5.ID = dt1.ID
;
CodePudding user response：
I don't really understand your query, but does this code do what you want it to do?
SELECT dt1.ID, dt1.created_ts, dt2.value
FROM data_table dt1 LEFT JOIN data_table dt2
ON dt1.ID = dt2.ID
CodePudding user response：
Great!! outer apply will do it. Page (LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 ) is providing examples and explanations ...
Outer Apply and Cross Join Latera in combination with my example:
WITH data_table AS ( select '101' AS ID
, to_date('2022-01-01', 'YYYY-MM-DD') AS Created_Date
, 3 AS Value
from dual
UNION ALL
select '102', to_date('2022-02-01', 'YYYY-MM-DD'), 7 from dual
UNION ALL
select '103', to_date('2022-03-01', 'YYYY-MM-DD'), 15 from dual
UNION ALL
select '101', to_date('2022-01-02', 'YYYY-MM-DD'), 30 from dual
UNION ALL
select '102', to_date('2022-02-02', 'YYYY-MM-DD'), 90 from dual
)
SELECT dt1.ID
, dt1.Created_Date
, dt1.Value
, ( Select dt2.value
From data_table dt2
Where dt2.ID = dt1.ID
Order By dt2.Created_Date DESC Fetch First 1 Rows Only ) AS dt2_Value
, dt5.Value AS dt5_Value
, dt6.Value AS dt6_Value
FROM data_table dt1
--
OUTER apply ( Select ID, Value
From data_table
WHERE Value = dt1.Value
Order By data_table.Created_Date DESC Fetch First 1 Rows Only
) dt5
--
cross join lateral ( Select ID, Value
From data_table
WHERE Value = dt1.Value
Order By data_table.Created_Date DESC Fetch First 1 Rows Only
) dt6
;
@William Robertson and @Florin thanks again.