Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-02-2010
11:25 AM

06-02-2010
11:25 AM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2010
12:58 PM

06-03-2010
12:58 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2010
01:37 PM

06-03-2010
01:37 PM

Re: Sel distinct 3 fld1, fld 2??

Dieter, can you please help with what this means?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-03-2010
10:18 PM

06-03-2010
10:18 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-04-2010
11:35 AM

06-04-2010
11:35 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2010
02:42 AM

06-05-2010
02:42 AM

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)

The , (SELECT DISTINCT .... ) B in the FROM clause acts as a table which you then join with table A (A.fld6 = B.fld6)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-10-2010
03:27 AM

06-10-2010
03:27 AM

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.

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