Hi
all,
I'm
writing a package to manipulate a partitioned table for the
duhvelopers and have run into
a
weird query that I can't figure out. I can convert a substring to a number
in a select clause,
but as
soon as I try to use that same number in the where clause, the thing
chokes. Has
Unless my memory is failing (more than likely) I thought you couldn't use
the partition_name like that in the where clause of patitioned tables as it
is a LONG ??
Mine was 8.0.6 but I'm sure this is still the case for at the very least 8i.
I ran into the same problem a while ago.
HTH
The 'where' clause may be applied to many more rows
than appear in the result set (to which the functions
on the selected columns will be applied).
Thus one of the rows that is being encountered does
convert to a valid number.
Basic example follows:
SQL select to_number(x) from
2( select
: Wednesday, September 04, 2002 12:43
PM
Subject: Coercion issue
Hi
all,
I'm
writing a package to manipulate a partitioned table for the
duhvelopers and have run into
a
weird query that I can't figure out. I can convert a substring to a
number in a select clause
D'oh
I was thinking of the high value column.
Apologies
Lee
-Original Message-
Sent: 04 September 2002 18:03
To: Multiple recipients of list ORACLE-L
Unless my memory is failing (more than likely) I thought you couldn't use
the partition_name like that in the where clause of
Under 8.1.5 partition_name is varchar2(30), as most of the names in data
dictionary.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, September 04, 2002 1:03 PM
Unless my memory is failing (more
recipients of list ORACLE-LSubject:
Re: Coercion issue
John,
Do you have other partitions with such names, that 5th and
6th characters are not convertible into numbers?
Like: 'TYPEA1'?
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
John
Sory Igor - I misread who sent the original email.
John,
If the buy_price_pkg.cnv_bpt_to_bp_id function returns a number, this makes
perfect sense. Oracle is attempting to convert the partition_name column to
a number before your instr function gets to do its magic.
Try doing the following:
n_name,5,2))
= buy_price_pkg.cnv_bpt_to_bp_id(5)
This should do it for you.
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
John Weatherman
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 04, 2002 4:59
PM
Subject: RE: Coer
John,
Maybe the buy_price_pkg.cnv_bpt_to_bp_id(5) function is returning a
string? From what you provided, it looks like its returning null or maybe
a blank character. Null shouldn't be a problem, but a blank string
Judging by the position of the * in the error message(?) ,it seems that
Title: RE: Coercion issue
It's because of the order that the conditions are checked in the joining of two comparisons with AND.
Example:
-Original Message-
From: John Weatherman [mailto:[EMAIL PROTECTED]]
Sent: mercredi, 4. septembre 2002 09:44
To: Multiple recipients of list
Title: RE: Coercion issue
It's because of the order in which conditions are evaluated in two expressions joined with AND.
Example:
SQL select v from t ;
V
--
A
1
AA
11
SQL select v from t where to_number (v) 0 and v like '1%' ;
V
--
1
11
SQL select v from t where v
PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, September 04, 2002 1:14 PM
Subject: RE: Coercion issue
Igor,
If the buy_price_pkg.cnv_bpt_to_bp_id function returns a number, this
makes
perfect sense. Oracle is attempting to convert the partition_name
column
to
a number before your
13 matches
Mail list logo
