ROW_NUMBER inside a view

[Brahim Mohamed]

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]

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]

It was stupid from me, yes indeed using your suggestion instead of the subquery and it works very well thank you so much Mark