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

From: Laya Li <lli_at_sos.state.tx.us>
Date: 13 Feb 2002 14:36:48 -0800
Message-ID: <6fb217d.0202131436.b4da7c2_at_posting.google.com>


Oh, can use d.name instead od name. It is in table d.

Thanks,

Laya

damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message news:<3C6AA7C2.99924F14_at_ci.seattle.wa.us>...
> Don't know on a quick glance other than to wonder exactly how you are
> sorting on name as "name" does not appear to be an actual field.
>
> Daniel Morgan
>
>
>
> 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 Wed Feb 13 2002 - 23:36:48 CET

Original text of this message