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  |
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 #219875 is a reply to message #219871] |
Fri, 16 February 2007 08:44   |
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  |
 |
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.
|
|
|
Goto Forum:
Current Time: Mon Feb 10 04:23:52 CST 2025
|