Using "*" in subquery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bernd Hohmann - 15 Nov 2005 20:49 GMT

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

Signature

"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

Reply to this Message

Knut Stolze - 16 Nov 2005 10:58 GMT

> I have a question about using "*" in (sub)queries.
>
[quoted text clipped - 28 lines]
>
> 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 ...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message