By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Using "*" in subquery

P: n/a
I have a question about using "*" in (sub)queries.

This example query counts the number of records for a certain customer
starting with record "80172" and stops after 10 rows.

SELECT PK_ORDER,PK_CUSTOMER FROM
(SELECT PK_ORDER,PK_CUSTOMER, ROW_NUMBER() OVER() AS rn
FROM CUSTOMER_ORDERS_HD WHERE PK_CUSTOMER='10649'
ORDER BY PK_ORDER DESC) AS T2
WHERE T2.rn <= 80172 FETCH FIRST 10 ROWS ONLY

Quite easy.

Unfortunately I have to rename all the column labels for display routine
(PK_ORDER AS "Order-Nb" and so on).

1st idea
--------
SELECT * FROM
(SELECT PK_ORDER AS "ORDER-NB",PK_CUSTOMER, ROW_NUMBER() OVER()...

Bad idea: the result of "row_number()" gets its own column in the output
(Output is "ORDER-NB, PK_CUSTOMER, T2")

2nd idea
--------
SELECT PK_ORDER AS "ORDER-NB",PK_CUSTOMER FROM
(SELECT *, ROW_NUMBER() OVER() AS rn

Failed because "SELECT *, ROW_NUMBER()" doesn't work.

Any other hints than declaring all rows in both selects?

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 15 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bernd Hohmann wrote:
I have a question about using "*" in (sub)queries.

This example query counts the number of records for a certain customer
starting with record "80172" and stops after 10 rows.

SELECT PK_ORDER,PK_CUSTOMER FROM
(SELECT PK_ORDER,PK_CUSTOMER, ROW_NUMBER() OVER() AS rn
FROM CUSTOMER_ORDERS_HD WHERE PK_CUSTOMER='10649'
ORDER BY PK_ORDER DESC) AS T2
WHERE T2.rn <= 80172 FETCH FIRST 10 ROWS ONLY

Quite easy.

Unfortunately I have to rename all the column labels for display routine
(PK_ORDER AS "Order-Nb" and so on).

1st idea
--------
SELECT * FROM
(SELECT PK_ORDER AS "ORDER-NB",PK_CUSTOMER, ROW_NUMBER() OVER()...

Bad idea: the result of "row_number()" gets its own column in the output
(Output is "ORDER-NB, PK_CUSTOMER, T2")

2nd idea
--------
SELECT PK_ORDER AS "ORDER-NB",PK_CUSTOMER FROM
(SELECT *, ROW_NUMBER() OVER() AS rn

Failed because "SELECT *, ROW_NUMBER()" doesn't work.

Any other hints than declaring all rows in both selects?


Use a correlation name for the table in the inner FROM clause:

SELECT name1, name2
FROM ( SELECT t.*, row_number() over() AS rn
FROM tab1 AS t )
WHERE ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 16 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.