Hi Hamid, Can you to give me what you want to obtain as result to this requette. I believe that in your request you have a problem of conversion of a varchar2 in number.
LYNDA HAOUHACH Ingénieur Systèmes SONATRACH LTH Émail: [EMAIL PROTECTED] > -----Message d'origine----- > De: Hamid Alavi [SMTP:[EMAIL PROTECTED]] > Date: lundi 24 juin 2002 11:23 > À: Multiple recipients of list ORACLE-L > Objet: NEED YOUR OPINION > > List, > > I have a table with the following structure: > > CREATE TABLE SERVICE_CODE_MSC ( > MSC_SERVICE_ID NUMBER (4) NOT NULL, > MSC_SERVICE_CODE VARCHAR2 (30) NOT NULL, > CONSTRAINT MSC_PK > PRIMARY KEY ( MSC_SERVICE_ID, MSC_SERVICE_CODE ) > USING INDEX > TABLESPACE USERS > > Here is some sample of this table: > service_id service_code > ------------- ------------------ > 7 a1000 > 7 a2000 > 30 e1230 > 30 e1234 > 10 12098 > 20 130987 > > > When I run the followinh query on this table I get invalid > number(ORA-01722), then when I drop tha table and reload the data the > query > running OK, Here is the query I am running: > > select count(*) > from (select MSC_SERVICE_CODE,MSC_MTF_SERVICE_ID > from MTF_SERVICE_CODE_MSC > where > substr(MSC_SERVICE_CODE, 1, 1) in > ('9','8','7','6','5','4','3','2','1','0')) a > where > to_number(a.MSC_SERVICE_CODE) between 7 and 30 > Any Idea ? why this hapenning? > > Thanks Allot. > > > > > > Hamid Alavi > Office 818 737-0526 > Cell 818 402-1987 > > > > > > > ======================= Confidentiality Statement ======================= > The information contained in this message and any attachments is > intended only for the use of the individual or entity to which it is > addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL > and exempt from disclosure under applicable law. If you have received > this message in error, you are prohibited from copying, distributing, or > using the information. Please contact the sender immediately by return > e-mail and delete the original message from your system. > ===================== End Confidentiality Statement ===================== > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hamid Alavi > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > 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.com -- Author: SIM/HAOUHACH INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).