RE: [Q] SQLPLUS help on distinct
Title: RE: [Q] SQLPLUS help on distinct order by (rpad(employee_id,6,' ')) Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: mike mon [mailto:[EMAIL PROTECTED]] Sent: Friday, August 29, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: [Q] SQLPLUS help on distinct I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed _expression_ __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: [Q] SQLPLUS help on distinct
Select distinct employee From (select rpad(employee_id,6,' ')) ||';'||ssn as employee from EMP order by employee_id) Jack -Original Message- Sent: Friday, August 29, 2003 4:45 PM To: Multiple recipients of list ORACLE-L I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] SQLPLUS help on distinct
Mike, Add a GROUP BY command: select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP group by (rpad(employee_id,6,' ')) ||';'|| ssn Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, August 29, 2003 10:45 AM To: Multiple recipients of list ORACLE-L I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] SQLPLUS help on distinct
Title: RE: [Q] SQLPLUS help on distinct Mike, Try one of these: select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP order by (rpad(employee_id,6,' ')) ||';'|| ssn; or select distinct employee_id, (rpad(employee_id,6,' ')) ||';'|| ssn from EMP order by employee_id; Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: mike mon [SMTP:[EMAIL PROTECTED] I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed _expression_
RE: [Q] SQLPLUS help on distinct
2003 06:44:40 I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed expression Methinks that the problem is with the ORDER BY. The DISTINCT will sort, anyway. Just make sure that your expression will sort the way you want (ie that you will not have 1234 before 23 because of number-to-char conversions - careful formatting can take care of that, just a matter of aliging properly). HTH Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Q] SQLPLUS help on distinct
GROUP BY does not necessarily guarantee a sort. eg. you still need an ORDER BY On Fri, 2003-08-29 at 07:59, Mercadante, Thomas F wrote: Mike, Add a GROUP BY command: select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP group by (rpad(employee_id,6,' ')) ||';'|| ssn Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, August 29, 2003 10:45 AM To: Multiple recipients of list ORACLE-L I have problem to use distinct with Function on select statement. Can anyone give me a hint? Thanks. SQL select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP 2 order by employee_id; select distinct (rpad(employee_id,6,' ')) ||';'|| ssn from EMP * ERROR at line 1: ORA-01791: not a SELECTed expression __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
