One more to add to the queries: INSERT INTO table1 (fld1, fld2, fld3, fld4) SELECT DISTINCT T.fld1, T.fld2, 'EVT', CAST(T.fldn AS VARCHAR(40)) FROM ( SELECT A.fld2, A.fldn, B.fld1 FROM table2 A , (SELECT DISTINCT T.fld1, S.fld5 FROM table3 T INNER JOIN USER_AML.EPC_LGC_SCHD S ON T.fld1 = S.fld1 AND S.fld5 IN (2,3) WHERE T.fld3='EVT' AND T.fld6 IS NULL ) B WHERE A.fld5 = B.fld5 ) T;
How can we have a comma between the 2 select statemnets for table 2 and table3?
1. sel distinct 3 fld1,... the 3 does not specify the number of distinct columns but is itself a column in the projection. It's like selecting a static 3 and aliasing it as fld1. Thus its only a distinct combination of fld2, fld3 because the fld1 isn't even there in the select clause. 2. It would output: 2, fld2, fld3, where 2 is a constant number and fld2 and fld3 will be iterated for all the rows in your table.
Thanks monisiqbal! But why would we want a constant like this? I mean what situations can we use this?
One more query:- What would be the result of this? I have never seen a comma in between two SELECT statemtents. INSERT INTO table1 (fld1, fld2, fld3, fld4) SELECT DISTINCT T.fld1, T.fld2, 'EVT', CAST(T.fld5 AS VARCHAR(40)) FROM ( SELECT A.fld2, A.fld5, B.fld1 FROM table2 A , (SELECT DISTINCT T.fld1, S.fld6 FROM table3 T INNER INNER JOIN table4 S ON T.fld1= S.fld1 AND S.fld6 IN (2,3) WHERE T.fld3='EVT' AND T.GRP_CD IS NULL ) B WHERE A.fld6 = B.fld6 ) T;
Constants can be used in many ways. e.g. use a sel 1, ... in the seed statement of a WITH RECURSIVE query and increment it in the recursive clause. Also, sel null as fld1 can also be useful in some situations.
The , (SELECT DISTINCT .... ) B in the FROM clause acts as a table which you then join with table A (A.fld6 = B.fld6)