ORA-01791 Pl-Sql error

Question

hi guy i have a query that give me the followin error:

ORA-01791: not a SELECTed expression   

this is the select expresison , please can you tell me why ?

declare

 freqLettura varchar2(64);
 billingcy  varchar2(64);

begin
freqLettura := null;
billingcy := null;

for rec in ( select distinct(fn_get_facilityid(z.uidfacility) ) as a, 1 as b 
            from facilityhistory z, 
            locality l , 
            plant p , 
            ztmp_sam_tb_sdv zsdv , 
            ztmp_sam_tb_plantcode zplant , 
            sam_tb_ca_pdr sam,  
            meterhistory mh, 
            meter m , 
            meterclass mc 
            where 
                Z.UIDLOCALITY = L.UIDLOCALITY and  
                p.UIDPLANT = L.UIDPLANT  and 
                z.uidaccount = zsdv.uidaccount and 
                p.plantcode = zplant.plantcode and   
                sam.uidfacility = z.uidfacility and
                z.stoptime is null and
                sam.status = 'U' and
                mh.uidfacility = z.uidfacility and 
                mh.uidmeter = m.uidmeter and 
                m.uidmeterclass = mc.uidmeterclass and 
                (billingcy is null or p.UIDBILLINGCYCLE = billingcy )
            AND
            (
                (
                (freqLettura = 'G') AND   ( mh.corrmeterid is not null and mh.stoptime is null and mc.maxflowmeter >= SAM_FN_GET_PARAMETER_FLOAT('MAXFLOWMET_DETT_GIORN'))
                )
                OR
                (
                nvl(freqLettura,'nullo') <> 'G' AND (freqLettura is null or sam.readfrequency = freqLettura)
                )
            ) and ROWNUM = 1 order by sam.stoptime, sam.uidsamtbpdr desc ) loop
begin
    insert into ztmp_sam_tb_elab_pdr (facilityid, uidbatchrequest) VALUES  (rec.a, rec.b);
exception
     when dup_val_on_index then
        null;
  end;
end loop;    

end;

Answer 1

Whenever you get an Oracle error message you don't understand, the first thing to do is look up the meaning. One way is simply to Google it. In this case the full description found in Oracle9i Database Error Messages is:

ORA-01791 not a SELECTed expression

Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

(Oddly this error message isn't documented in the 10G or 11G manuals, despite still being raised!)

This matches the statement you have written, which is a SELECT DISTINCT query where you are trying to order the results by a column that you did not select.

If you think about it, what you are asking for doesn't make sense: by selecting DISTINCT values that do not include sam.stoptime (for example) you may be consolidating many rows with different values for sam.stoptime, so which one would govern the ordering?

Also, as Noel's answer points out, there is no reason to have an ORDER BY clause in this code anyway, so the solution is simply to remove it.

Answer 2

If you are using DISTINCT in your SELECT query, then your ORDER BY clause should contain only those columns that your selecting. In this case sam.stoptime, sam.uidsamtbpdr are not there in SELECT statement. You can remove the ORDER BY clause, as it is not doing anything useful in your example.