Sel distinct 3 fld1, fld 2??

Database
Enthusiast

Sel distinct 3 fld1, fld 2??

Hi all!

I have basic qsns.

1. Sel distinct 3 fld1,fld2,fld3 from table; - Does it sel 3 difft combinations ?
2. Sel 2 fld1, fld2,fld3 from table -- What does this select?

TIA!
6 REPLIES
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

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?
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

Dieter, can you please help with what this means?
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

On your first question

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.
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

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;
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

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)
Enthusiast

Re: Sel distinct 3 fld1, fld 2??

SELECT Statement after comma

(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

is actually a derived table, which u can only use in the same SQL where it is defined.