ROW_NUMBER inside a view

9 views
Skip to first unread message

Brahim Mohamed

unread,
Feb 21, 2021, 12:28:53 PMFeb 21
to firebird-support
Hello,

I use a selecting view in my  client app and added the row_number function in order to have a sequential field for a pagination records  but I get message : "multiple rows in single select"  I know I can do that in a sp but it is to late to change queries. Is it possible to do that in a view  please ?.
Thanks  
I use Firebirdd 3 and here is my view 

CREATE OR ALTER VIEW VW_TEST(
    RN,
    COMMANDE_ID,
    UNITE_ID,
    CLIENT_ID,
    ANNEE_COMMANDE,
    NUM_COMMANDE,
    DATE_COMMANDE,
    PREFIXE,
    DELAI_LIVRAISON,
    VERROUILLER,
    EMPLOYE_ID)
AS
SELECT
(SELECT ROW_NUMBER() OVER (ORDER BY COMMANDE_ID DESC)  AS RN FROM COMMANDE_CLIENT),
CC.COMMANDE_ID,CC.UNITE_ID,CC.CLIENT_ID, CC.ANNEE_COMMANDE, CC.NUM_COMMANDE, CC.DATE_COMMANDE, CC.PREFIXE, CC.DELAI_LIVRAISON, CC.VERROUILLER,
CC.EMPLOYE_ID

FROM COMMANDE_CLIENT CC



Mark Rotteveel

unread,
Feb 21, 2021, 1:09:20 PMFeb 21
to firebird...@googlegroups.com
On 2021-02-21 18:28, Brahim Mohamed wrote:
> Hello,
>
> I use a selecting view in my client app and added the row_number
> function in order to have a sequential field for a pagination records
> but I get message : "multiple rows in single select" I know I can do
> that in a sp but it is to late to change queries. Is it possible to do
> that in a view please ?.

The error is not surprising, because a sub-query in a select column list
may only produce a single value, and `(SELECT ROW_NUMBER() OVER (ORDER
BY COMMANDE_ID DESC) AS RN FROM COMMANDE_CLIENT)` will produce as many
rows as there are rows in COMMANDE_CLIENT.

It is unclear exactly what you are trying to achieve (or at least, I
don't understand why you introduced that sub-query), but using
`ROW_NUMBER() OVER (ORDER BY COMMANDE_ID DESC) AS RN` instead of the
sub-query will probably do what you want.

Mark

Brahim Mohamed

unread,
Feb 21, 2021, 2:04:04 PMFeb 21
to firebird-support
It was stupid from me, yes indeed using your suggestion instead of the subquery and it works very well thank you so much Mark
Reply all
Reply to author
Forward
0 new messages