Update subrequest and limit rownum

Question

I wrote this query:

UPDATE artpxvtemagtemp a
    SET
    origineprix = 'CEN',
    pxvente = (
        SELECT
            ap.pxvtrpublic
        FROM
            artpxvte   ap
            JOIN article    ar ON ar.idarticle = ap.idarticle
        WHERE
            ap.idarticle = a.idarticle
            AND a.unite = ap.unite
            AND ap.isdegressif = 0
            AND ap.idtarifvte = 'T00002'
            AND ap.datedebut <= pdateappli
            AND ( ap.datefin IS NULL OR pdateappli < ( ap.datefin + 1 ) )
    );

But sometimes the sub-query gives more than one result, so the update fails. I want to order the result and select the first result of the sub-query to make the update. So I do :

UPDATE artpxvtemagtemp a
SET
    origineprix = 'CEN',
    pxvente = (
        SELECT
            pxvtrpublic
        FROM 
        (
        SELECT
            ap.pxvtrpublic
        FROM
            artpxvte   ap
            JOIN article    ar ON ar.idarticle = ap.idarticle
        WHERE
            ap.idarticle = a.idarticle
            AND a.unite = ap.unite
            AND ap.isdegressif = 0
            AND ap.idtarifvte = 'T00002'
            AND ap.datedebut <= pdateappli
            AND ( ap.datefin IS NULL
                    OR pdateappli < ( ap.datefin + 1 ) )
        ORDER BY 
            CASE WHEN ap.datefin IS NULL THEN 0 ELSE 1 END ASC,
            ap.datefin DESC,
            ap.datedebut DESC
        )
        where rownnum <= 1
    );

It does not work because the alias a is lost, so I have to add an additional sub-query just for the rownnum.

Erreur SQL : ORA-00904: "A"."UNITE" : identificateur non valide 00904. 00000 - "%s: invalid identifier"

In Oracle 12C, I don't need a sub-query to fetch the first Nth rows, but I have an old version of Oracle so I can't use this.

How can I make the alias a known?

Answer 1

Use aggregation instead:

pxvente = (select max(ap.pxvtrpublic) keep (dense rank first
                                            order by case when ap.datefin is null then 0 else 1 end,
                                                     ap.datefin desc,
                                                     ap.datedebut desc
                                           )
            from . . .