Re: What's wrong in this qury with distinct?

Try changing the group by to match the column that you specified. What I think should work is :
  Group by lpad(to_char(d.county_code_reg), 3, 0), name

Hope this helps,
Dave Bechtel

Laya Li wrote:

> 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 Sun Feb 24 2002 - 04:15:57 CET