Query writing is fun but can be complex when we are referring multiple tables/views
which has inlet/Joins /sub-query referred. on the best solution & organized
way of writing such query in Oracle is using WITH Blocks.
Below I'm using simple example to help you get started with WITH-blocks
Tutorial : First block starts with "WITH" keyword
WITH block_name AS ( ), Final statement
includes Select statement with in query or join as needed treating each block as
table. Below are 2 example showing with block usage.
Example : Consider 4 tables/views having needed information blinded
with a connecting factor and say ID. Normal Query States :
SELECT T1.Col1, T1.col2, T2.col3, T2.col4,
T2.col5 FROM Table1 T1 JOIN Table2 T2 ON Col6 = col7
WHERE col6 IN ( SELECT TA.COLA FROM TableA TA JOIN TableB TB ON TA.COLA1 = tb.ColB1 )
Now to have more organized way to get this query executed using WITH Block
CONDITION 01: Using blocks in WHERE clause
WITH BLOCK1 AS (SELECT TA.COLA FROM
TableA TA JOIN TableB TB ON TA.COLA1 = tb.ColB1),
SELECT T1.Col1, T1.col2, T2.col3, T2.col4, T2.col5 FROM Table1 T1 JOIN Table2 T2 ON Col6 = col7 WHERE col6 IN
( --Note:with block added as select statement & can be used at multiple places.
SELECT block1.cola FROM BLOCK1
)
CONDITION 02: JOINING WITH blocks all together.
--Note : block starts from "WITH" statement
WITH BLOCK1
AS
(SELECT TA.COLA FROM TableA TA JOIN
TableB TB ON TA.COLA1 = tb.ColB1
),
--Note: Except last with block all with block end with ,
BLOCK2 AS (
SELECT T1.Col1, T1.col2, T2.col3, T2.col4, T2.col5 FROM Table1
T1 JOIN Table2 T2 ON Col6 = col7
)
--final statement only includes Black name.
SELECT * FROM Block2 B2, Block1 B1 WHERE B2.col6 = b1.COLA
Hope this help's you
----