Using "*" in subquery

[Bernd Hohmann]

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

[Knut Stolze]

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