garbagedump

sql | how NOT to avoid multiple identical subqueries in the SELECT clause

weathered's photo
weathered
·

1 min read

running 12c or higher? how to

inline subqueries in SELECT clauses, or simply scalar subqueries, as you can guess by the name, is used to return a single value.

  • but what if, i am having to query a specific table, writing identical subqueries to fetch multiple data, with the same filters?
  • and, what if, i don't want to use the simple solution, join, you know just because?
  • oh and what if, the database is old and does not support APPLY?

this is how you are not supposed to do that, pretty much high maintenance code.

lets say i want to return two NUMBER values with a single inline subquery:

a. create sample tables:

CREATE TABLE TABLE1 AS
SELECT 1 ID, 'TEST' VAL1 FROM DUAL;

CREATE TABLE TABLE2 AS
SELECT 1 ID, 2 VAL2, 3 VAL3 FROM DUAL;

b. create a type of object(number,number):

CREATE OR REPLACE TYPE TMP_TYPE AS OBJECT (V1 NUMBER, V2 NUMBER);

c. profit?

SELECT RV.VAL1, RV.MV.V1 VAL2, RV.MV.V1 VAL3
  FROM (SELECT A.VAL1,
               (SELECT TMP_TYPE(VAL2, VAL3) FROM TABLE2 B WHERE B.ID = A.ID) MV
          FROM TABLE1 A) RV;
/*
VAL1       VAL2       VAL3
---- ---------- ----------
TEST          2          2

1 row selected.
*/
SQLOracle