What's wrong in this qury with distinct?

From: Laya Li <lli_at_sos.state.tx.us>
Date: 13 Feb 2002 06:37:57 -0800
Message-ID: <6fb217d.0202130637.4fef5892_at_posting.google.com>

[Quoted] I got problem when I use distinct in my query, any help is appreciated.

My original query works with distinct as follows:  select distinct d.cert_num, d.county_code_reg, d.last_name||', '|| d.first_name||' '|| d.middle_name||' '|| d.suffix name,

      substr(d.ssn,1,3) || '-' ||       substr(d.ssn,4,2) || 
      '-' || substr(d.ssn,6,4),
      ltrim(d.street) ||  ', ' || ltrim(d.city) || ', ' 
|| 'TX' || ' ' ||  d.zip  address,
      d.dob_date, d.dod_date, d.date_created, d.county_code, o.status

from deceased_persons d, offline_voters o where (d.county_code_reg = o.county_code and d.cert_num = o.cert_num and (o.status = 'S' or o.status = 'V'))
AND d.county_code_reg is not null
and d.last_name is not null
order by d.county_code_reg, name

But when I edit above code for a fixed format report, it got problem. My changed query is as follows:
set feedback off
set term off
set heading off
set pagesize 0
set show off
set echo off
set verify off
set linesize 262
spool deceased_offline_voters_long.txt
select distinct rpad(d.cert_num, 8, ' ')

||lpad(to_char(d.county_code_reg), 3, 0)
||rpad(nvl(d.last_name, ' '), 35)
||rpad(nvl(d.first_name, ' '), 20)
||rpad(nvl(d.middle_name, ' '), 20)
||rpad(nvl(d.suffix, ' '), 4)
||substr(d.ssn,1,3) || '-' || substr(d.ssn,4,2) || '-' ||
substr(d.ssn,6,4)

||rpad(nvl(d.street, ' '), 100)
||rpad(nvl(d.city, ' '), 25)
||'TX'
||rpad(nvl(d.zip, ' '), 9)
||rpad(nvl(to_char(d.dob_date, 'yyyymmdd'), ' '), 8)
||rpad(nvl(to_char(d.dod_date, 'yyyymmdd'), ' '), 8)
||rpad(nvl(to_char(d.date_created, 'yyyymmdd'), ' '), 8)
||lpad(to_char(d.county_code), 3, 0)
||rpad(nvl(o.status, ' '), 1)
from deceased_persons d, offline_voters o where (d.county_code_reg = o.county_code and d.cert_num = o.cert_num and (o.status = 'S' or o.status = 'V'))
AND d.county_code_reg is not null
and d.last_name is not null
order by d.county_code_reg, name;
spool off

Running above query get error message as follows:

order by d.county_code_reg, name

         *
ERROR at line 22:
ORA-01791: not a SELECTed expression

In above changed query, if I don't use distinct(other keep the same), it works.
Looks like distinct cannot be used with sql plus format. Received on Wed Feb 13 2002 - 15:37:57 CET