Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

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

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

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

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

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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.