Oracle SQL - Define table names for later usage?

Question

I wanted to know if there is a way, in SQL Oracle, to do some range-definition (like in Excel). For example:

DEFINE TABLE1 = SELECT FIELD1, FIELD2, FIELD3 FROM [SCHEMA].[TABLE0][WHERE/GROUP BY/HAVING/ORDER BY/...];

DEFINE TABLE2 = SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 [WHERE/GROUP BY/HAVING/ORDER BY/...];

DEFINE TABLE3 = SELECT FIELD1, FIELD2, FIELD3 FROM TABLE2 LEFT JOIN TABLE1 ON [CONDITIONS];

SELECT * FROM TABLE3;

Thanks a lot in advance.

Have you considered common table expressions (the WITH clause)? — Steve
@Steve, I only understand the following logic: "WITH TABLE1 AS [Table Structure], TABLE2 AS [Table Structure] SELECT * FROM TABLE1 JOIN TABLE2;" & that's it. I'm not sure if there is a more complex logic behind this clause though... — Felipe Vidal Otero
It's a bit difficult to give a meaningful example with only the abstract data you posted, but a with-clause can contain any kind of select query (including the use of group-by and where clauses). The logic can be as complex as you need it to be - you can also chain the table references, so the contents of one CTE can refer to an earlier one (i.e. TABLE2 could select from or join onto TABLE1, in your example, with both being defined inside the with-clause). — Steve

sstan sstan · Accepted Answer · 2017-12-30T02:02:43

Based on your examples, it sounds like you want to create views:

CREATE VIEW TABLE1 AS
SELECT FIELD1, FIELD2, FIELD3
FROM [SCHEMA].[TABLE0][WHERE/GROUP BY/HAVING/...];

CREATE VIEW TABLE2 AS
SELECT FIELD1, FIELD2, FIELD3
FROM TABLE1 [WHERE/GROUP BY/HAVING/...];

CREATE VIEW TABLE3 AS
SELECT FIELD1, FIELD2, FIELD3
FROM TABLE2
LEFT JOIN TABLE1 ON [CONDITIONS];

SELECT * FROM TABLE3;

Oracle SQL - Define table names for later usage?

2 Answers