Return-Path: <oracle-l-bounce@freelists.org>
X-Original-To: oracle-l@orafaq.com
Delivered-To: oracle-l@orafaq.com
Received: from puck1183.startdedicated.com (localhost [127.0.0.1])
 by puck1183.startdedicated.com (Postfix) with ESMTP id 9831A19612E1
 for <oracle-l@orafaq.com>; Tue,  9 Sep 2014 22:50:34 +0200 (CEST)
Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180])
 by puck1183.startdedicated.com (Postfix) with ESMTP
 for <oracle-l@orafaq.com>; Tue,  9 Sep 2014 22:50:34 +0200 (CEST)
Received: from localhost (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E7B8C2F6D1;
 Tue,  9 Sep 2014 16:50:33 -0400 (EDT)
Authentication-Results: turing.freelists.org; dkim=fail
 (verification failed; insecure key) header.i=@gmail.com;
 dkim-adsp=none (insecure policy)
X-Virus-Scanned: Debian amavisd-new at turing.freelists.org
Received: from turing.freelists.org ([127.0.0.1])
 by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id TJkYEzFQBbtG; Tue,  9 Sep 2014 16:50:33 -0400 (EDT)
Received: from turing.freelists.org (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DC28E2F660;
 Tue,  9 Sep 2014 16:49:52 -0400 (EDT)
Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2014 16:49:11 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E9F32F625
 for <oracle-l@freelists.org>; Tue,  9 Sep 2014 16:49:11 -0400 (EDT)
Received: from turing.freelists.org ([127.0.0.1])
 by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id vnzBiZQWjV7Z for <oracle-l@freelists.org>;
 Tue,  9 Sep 2014 16:49:11 -0400 (EDT)
Received: from mail-oi0-f46.google.com (mail-oi0-f46.google.com [209.85.218.46])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 420F42F616
 for <oracle-l@freelists.org>; Tue,  9 Sep 2014 16:48:43 -0400 (EDT)
Received: by mail-oi0-f46.google.com with SMTP id g201so3067844oib.19
        for <oracle-l@freelists.org>; Tue, 09 Sep 2014 13:48:43 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20120113;
        h=content-type:mime-version:subject:from:in-reply-to:date:cc
         :message-id:references:to;
        bh=PLGwn8dzI0kG0y3hgXx0Drx5ihtYyF6Tphi2Rf3fNn4=;
        b=jVg+QXbDUthtYESoFPxr50gEWt5x6d8mEA1O8kVp4014l9d7Jc3NHN+OafbX52kFf7
         /aKK3zk2LEsOOypDh+JDg53D08EcPNy9UL5BGx1cAAkkBJ0l8pf7z7NAB9pOkdgkrVKd
         In9fniq9VGHkNNh3UXqKkEqFXRmC8HtQ+Ye600lbo3LUEjvHNG2iaDnQrowZy4vKMz6L
         B/QrtXodcgJr0MP3laGTyhMkpdJCqiV4cr89k7dXKPXJA5Ws+jC5pZrJpq8ppHJ4wm6P
         diZy1/D+tVA0zhky+BgMaDyZ42Kt9X6lZShH2ObPhkXtq6/DT1ke4AfW8RvvTeCNzK/F
         vHHg==
X-Received: by 10.182.66.16 with SMTP id b16mr41281865obt.49.1410295723700;
        Tue, 09 Sep 2014 13:48:43 -0700 (PDT)
Received: from [192.168.29.55] ([12.90.21.202])
        by mx.google.com with ESMTPSA id jo2sm2664773oeb.16.2014.09.09.13.48.42
        for <multiple recipients>
        (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
        Tue, 09 Sep 2014 13:48:42 -0700 (PDT)
Content-Type: multipart/alternative; boundary="Apple-Mail=_56BAE279-7949-44E8-A36C-D96B07CC7E68"
Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\))
Subject: Re: Improving query with implicit type conversion
From: Ryan January <rjanuary@gmail.com>
In-Reply-To: <D7864FA3E7830B428CB2A5A5301B63EE01BC84D665@S7041VA005.soa.soaad.com>
Date: Tue, 9 Sep 2014 15:48:32 -0500
Cc: ORACLE-L <oracle-l@freelists.org>
Message-Id: <6E459EA9-9222-4049-B728-A5FB44025187@gmail.com>
References: <D7864FA3E7830B428CB2A5A5301B63EE01BC84D665@S7041VA005.soa.soaad.com>
To: vxsmimmcp@subaru.com
X-archive-position: 56279
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: rjanuary@gmail.com
Precedence: normal
Reply-To: rjanuary@gmail.com
List-help: <mailto:ecartis@freelists.org?Subject=help>
List-unsubscribe: <oracle-l-request@freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request@freelists.org?Subject=subscribe>
List-owner: <mailto:mark.bobak@proquest.com>
List-post: <mailto:oracle-l@freelists.org>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l
--Apple-Mail=_56BAE279-7949-44E8-A36C-D96B07CC7E68
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
 charset=windows-1252

I apologize for missing this before.  It's always been my experience =
that creating an index based upon predicates from query plans works as =
expected. =20

To directly answer your question,  even though orig_sys_ref may start of =
as a number, you do end up with a string after your string functions.  =
directly casting to a number on either side is allowing the index usage. =
 The other option you have is to somehow supply the bind as character =
data.

Thinking about the larger problem... if the sql does not change, is =
sqlpatch an option?  You may have a quick look at the following page for =
an example. https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a


On Sep 9, 2014, at 3:12 PM, McPeak, Matt <vxsmimmcp@subaru.com> wrote:

> Plan hash value: 379709735                                             =
                       =20
>                                                                        =
                        =20
> =
--------------------------------------------------------------------------=
----------------------
> | Id  | Operation         | Name             | Starts | E-Rows | =
A-Rows |   A-Time   | Buffers |
> =
--------------------------------------------------------------------------=
----------------------
> |   0 | SELECT STATEMENT  |                  |      1 |        |      =
0 |00:00:00.29 |    6809 |
> |*  1 |  TABLE ACCESS FULL| CZ_DEVL_PROJECTS |      1 |     21 |      =
0 |00:00:00.29 |    6809 |
> =
--------------------------------------------------------------------------=
----------------------
>                                                                        =
                        =20
> Predicate Information (identified by operation id):                    =
                       =20
> ---------------------------------------------------                    =
                       =20
>                                                                        =
                        =20
>    1 - =
filter((TO_NUMBER(SUBSTR("ORIG_SYS_REF",INSTR(TRIM("ORIG_SYS_REF"),':',-1,=
1)+1,10      =20
>               0))=3D12345 AND TO_NUMBER("DELETED_FLAG")=3D0))          =
                           =20
>                                                                        =
                        =20
> So, I don=92t know what else to try except for TO_NUMBER().  Any =
thoughts?  I cannot change the query syntax.


--Apple-Mail=_56BAE279-7949-44E8-A36C-D96B07CC7E68
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
 charset=windows-1252

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html =
charset=3Dwindows-1252"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">I =
apologize for missing this before. &nbsp;It's always been my experience =
that creating an index based upon predicates from query plans works as =
expected. &nbsp;<div><br></div><div>To directly answer your question, =
&nbsp;even though orig_sys_ref may start of as a number, you do end up =
with a string after your string functions. &nbsp;directly casting to a =
number on either side is allowing the index usage. &nbsp;The other =
option you have is to somehow supply the bind as character =
data.</div><div><br></div><div>Thinking about the larger problem... if =
the sql does not change, is sqlpatch an option? &nbsp;You may have a =
quick look at the following page for an example.&nbsp;<a =
href=3D"https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a">https:/=
/blogs.oracle.com/optimizer/entry/how_can_i_hint_a</a></div><div><br></div=
><div><br><div><div>On Sep 9, 2014, at 3:12 PM, McPeak, Matt &lt;<a =
href=3D"mailto:vxsmimmcp@subaru.com">vxsmimmcp@subaru.com</a>&gt; =
wrote:</div><br class=3D"Apple-interchange-newline"><blockquote =
type=3D"cite"><div style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; =
font-family: Calibri, sans-serif; font-style: normal; font-variant: =
normal; font-weight: normal; letter-spacing: normal; line-height: =
normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;">Plan hash value: =
379709735&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></div><div=
 style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: =
Calibri, sans-serif; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; =
-webkit-text-stroke-width: =
0px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p=
></div><div style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; =
font-family: Calibri, sans-serif; font-style: normal; font-variant: =
normal; font-weight: normal; letter-spacing: normal; line-height: =
normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier =
New';">-------------------------------------------------------------------=
-----------------------------<o:p></o:p></span></div><div style=3D"margin:=
 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: auto; text-align: =
start; text-indent: 0px; text-transform: none; white-space: normal; =
widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><span =
style=3D"font-size: 9pt; font-family: 'Courier New';">| Id&nbsp; | =
Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | =
Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; | Starts | E-Rows | A-Rows |&nbsp;&nbsp; A-Time&nbsp;&nbsp; | Buffers =
|<o:p></o:p></span></div><div style=3D"margin: 0in 0in 0.0001pt; =
font-size: 11pt; font-family: Calibri, sans-serif; font-style: normal; =
font-variant: normal; font-weight: normal; letter-spacing: normal; =
line-height: normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier =
New';">-------------------------------------------------------------------=
-----------------------------<o:p></o:p></span></div><div style=3D"margin:=
 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: auto; text-align: =
start; text-indent: 0px; text-transform: none; white-space: normal; =
widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><span =
style=3D"font-size: 9pt; font-family: 'Courier New';">|&nbsp;&nbsp; 0 | =
SELECT STATEMENT&nbsp; =
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 =
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |00:00:00.29 |&nbsp;&nbsp;&nbsp; 6809 =
|<o:p></o:p></span></div><div style=3D"margin: 0in 0in 0.0001pt; =
font-size: 11pt; font-family: Calibri, sans-serif; font-style: normal; =
font-variant: normal; font-weight: normal; letter-spacing: normal; =
line-height: normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier New';">|*&nbsp; 1 |&nbsp; TABLE ACCESS FULL| =
CZ_DEVL_PROJECTS |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 =
|&nbsp;&nbsp;&nbsp;&nbsp; 21 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 =
|00:00:00.29 |&nbsp;&nbsp;&nbsp; 6809 |<o:p></o:p></span></div><div =
style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: =
Calibri, sans-serif; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; =
-webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier =
New';">-------------------------------------------------------------------=
-----------------------------<o:p></o:p></span></div><div style=3D"margin:=
 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: auto; text-align: =
start; text-indent: 0px; text-transform: none; white-space: normal; =
widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><span =
style=3D"font-size: 9pt; font-family: 'Courier =
New';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></span></div><div =
style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: =
Calibri, sans-serif; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; =
-webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier New';">Predicate Information (identified by =
operation =
id):&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></span></div><div=
 style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: =
Calibri, sans-serif; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; =
-webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier =
New';">---------------------------------------------------&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></span></div><div style=3D"margin: =
0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: auto; text-align: =
start; text-indent: 0px; text-transform: none; white-space: normal; =
widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><span =
style=3D"font-size: 9pt; font-family: 'Courier =
New';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:=
p></span></div><div style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; =
font-family: Calibri, sans-serif; font-style: normal; font-variant: =
normal; font-weight: normal; letter-spacing: normal; line-height: =
normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier New';">&nbsp;&nbsp;&nbsp;1 - filter((<b><span =
style=3D"color: =
red;">TO_NUMBER(SUBSTR("ORIG_SYS_REF",INSTR(TRIM("ORIG_SYS_REF"),':',-1,1)=
+1,10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></span></b></spa=
n></div><div style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; =
font-family: Calibri, sans-serif; font-style: normal; font-variant: =
normal; font-weight: normal; letter-spacing: normal; line-height: =
normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;"><b><span style=3D"font-size: 9pt; =
font-family: 'Courier New'; color: =
red;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;0))</span></b><span style=3D"font-size: 9pt; =
font-family: 'Courier New';">=3D12345 AND =
TO_NUMBER("DELETED_FLAG")=3D0))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:p></span></div><div =
style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: =
Calibri, sans-serif; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; =
-webkit-text-stroke-width: 0px;"><span style=3D"font-size: 9pt; =
font-family: 'Courier =
New';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<o:p></o:=
p></span></div><div style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; =
font-family: Calibri, sans-serif; font-style: normal; font-variant: =
normal; font-weight: normal; letter-spacing: normal; line-height: =
normal; orphans: auto; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;">So, I don=92t know what else to =
try except for TO_NUMBER().&nbsp; Any thoughts?&nbsp; I cannot change =
the query syntax.</div></blockquote></div><br></div></body></html>=

--Apple-Mail=_56BAE279-7949-44E8-A36C-D96B07CC7E68--
--
http://www.freelists.org/webpage/oracle-l