Home » SQL & PL/SQL » SQL & PL/SQL » IMP: URGENT : try this query on 10g ( it works in 9i )
IMP: URGENT : try this query on 10g ( it works in 9i ) [message #219868] Fri, 16 February 2007 08:12 Go to next message
enter
Messages: 2
Registered: February 2007
Junior Member
// This is as sample table i am using //

CREATE TABLE PE_DOC_STOCK_CUST_DTLS
(
STOCK_DTLS_SEQ NUMBER NOT NULL,
STOCK_CUST_DTL_SEQ NUMBER NOT NULL,
CUSTOMER_CODE VARCHAR2(10 BYTE) NOT NULL,
FROM_DOC_CUST VARCHAR2(15 BYTE),
TO_DOC_CUST VARCHAR2(15 BYTE),
CUST_RECEIPT_DT DATE,
TOTAL_DOCS NUMBER,
ADDUID VARCHAR2(5 BYTE) NOT NULL,
ADDUIDTIME VARCHAR2(20 BYTE) NOT NULL,
EDITUID VARCHAR2(5 BYTE),
EDITUIDTIME VARCHAR2(20 BYTE),
CNFMUID VARCHAR2(5 BYTE),
CNFMUIDTIME VARCHAR2(20 BYTE),
STATUS VARCHAR2(1 BYTE) NOT NULL
)


///// Values/////

INSERT INTO PE_DOC_STOCK_CUST_DTLS ( STOCK_DTLS_SEQ, STOCK_CUST_DTL_SEQ, CUSTOMER_CODE,
FROM_DOC_CUST, TO_DOC_CUST, CUST_RECEIPT_DT, TOTAL_DOCS, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME,
CNFMUID, CNFMUIDTIME, STATUS ) VALUES (
4461, 575, 'H033H0100', 'L85616', 'L85624', TO_Date( '12/24/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 9, 'VENKE', '200512242121592VENKE', NULL, NULL, NULL, NULL, 'U');

--------------------------------------------------------
INSERT INTO PE_DOC_STOCK_CUST_DTLS ( STOCK_DTLS_SEQ, STOCK_CUST_DTL_SEQ, CUSTOMER_CODE,
FROM_DOC_CUST, TO_DOC_CUST, CUST_RECEIPT_DT, TOTAL_DOCS, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME,
CNFMUID, CNFMUIDTIME, STATUS ) VALUES (
472, 451, 'G056G0301', '51501', '51600', TO_Date( '09/04/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 100, 'KAYUM', '200209041210287KAYUM', NULL, NULL, NULL, NULL, 'U');

COMMIT;
/////////END OF VALUES //////

SELECT * FROM PE_DOC_STOCK_CUST_DTLS
WHERE
(
ASCII(SUBSTR(UPPER(NVL(FROM_DOC_CUST,'A')),1,1)) BETWEEN 48 AND 57
and TO_NUMBER('1204000') BETWEEN to_number(FROM_DOC_CUST) AND to_number(TO_DOC_CUST)
)
AND CUSTOMER_CODE != 'C533C0100'



------PLS help the above query returns me error in oracle 10g but in oracle 9i it works fine . ITS very urgent.....thanks


Re: IMP: URGENT : try this query on 10g ( it works in 9i ) [message #219871 is a reply to message #219868] Fri, 16 February 2007 08:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And the error it returns is?

Re: IMP: URGENT : try this query on 10g ( it works in 9i ) [message #219874 is a reply to message #219868] Fri, 16 February 2007 08:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
From_doc_cust and to_doc_cust are not numbers, so the to_number will fail.
Probably in earlier cases (e.g. with version 9i) the other where-clause filtered out these values before reaching this erroneous part
Re: IMP: URGENT : try this query on 10g ( it works in 9i ) [message #219875 is a reply to message #219871] Fri, 16 February 2007 08:44 Go to previous messageGo to next message
enter
Messages: 2
Registered: February 2007
Junior Member
----Invalid number type------


also tried this but same error

SELECT TO_NUMBER(A.FROM_DOC_CUST),TO_NUMBER(A.TO_DOC_CUST),A.CUSTOMER_CODE FROM
(
SELECT B.FROM_DOC_CUST,B.TO_DOC_CUST,B.CUSTOMER_CODE FROM PE_DOC_STOCK_CUST_DTLS B
WHERE
ASCII(suBSTR( UPPER(NVL(B.FROM_DOC_CUST,'A')),1,1)) NOT BETWEEN 65 AND 90
) A

WHERE
--TO_NUMBER('1204000') BETWEEN to_number(NVL(A.FROM_DOC_CUST,0)) AND to_number(NVL(A.TO_DOC_CUST,0))
--1204000 BETWEEN to_number(NVL(A.FROM_DOC_CUST,'a')) AND to_number(NVL(A.TO_DOC_CUST,'a'))
51501 BETWEEN to_number(a.FROM_DOC_CUST) AND to_number(a.TO_DOC_CUST)
AND A.CUSTOMER_CODE != 'C533C0100'

[Updated on: Fri, 16 February 2007 08:48]

Report message to a moderator

Re: IMP: URGENT : try this query on 10g ( it works in 9i ) [message #219917 is a reply to message #219868] Fri, 16 February 2007 12:55 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suspect non-numeric within FROM_DOC_CUST.PE_DOC_STOCK_CUST_DTLS


select '['||translate(FROM_DOC_CUST,'0123456789',' ')||']'
from PE_DOC_STOCK_CUST_DTLS
where length(replace(translate(FROM_DOC_CUST,'0123456789',' '),' ')) > 0;

The query above should return only rows where FROM_DOC_CUST is non-numeric.
Previous Topic: Normalize / Denormalize
Next Topic: Oracle 9i Catalog and Schema version
Goto Forum:
  


Current Time: Mon Feb 10 04:23:52 CST 2025