another solution?
Barbara Boehmer, January 14, 2002 - 6:45 pm UTC
What about negative numbers? And, how about this?:
create or replace function numbers_only
(p_num in varchar2)
return number deterministic
as
x number;
begin
x := to_number (p_num);
return x;
exception
when others then return null;
end numbers_only;
/
select *
from t
where tmptype = 'N'
and numbers_only (tmpvalue) is null
/
January 14, 2002 - 8:25 pm UTC
sure, add a dash into the replace function and turn the dash into a zero. would hope that the example would lead to a real implementation...
Your approach would work but would
a) call the plsql function for every row -- I wanted to avoid that when possible, hence the decode trick (its faster to not call the plsql when possible)
b) record tmpvalue values that are NULL as not being a number -- but they are a number. Adding the is not null as you do below corrects that.
additional
Barbara Boehmer, January 14, 2002 - 6:53 pm UTC
It just dawned on me that there might be null values in the tmpvalue column, so if you are looking for text strings, not just non-numbers, revise that to:
select count(*)
from t
where tmptype = 'N'
and numbers_only (tmpvalue) is null
and tmpvalue is not null
/
issues
Barbara Boehmer, January 14, 2002 - 7:07 pm UTC
I see two issues:
1. Unless we use a function to evaluate every value, are we really sure whether it is or is not a number? In addition to negative numbers, what about things like 1+2. Is that legitimate? Should we count that as 3? Maybe it depends on the data?
2. Which is faster, one deterministic function, or decode and replace and translate and sometimes a function, and how do we tell? Could that, once again, depend on the data?
January 14, 2002 - 8:28 pm UTC
1) the decode trick shorts circuts us from having to call PLSQL when the string is DEFINITELY not a number -- we call the function for everything that MIGHT be a number. We would not count 1+2 as a number (we could using
execute immediate 'select ' || p_str || ' from dual' into l_number
instead of just assigning the string to a number but that would be even slower...
2) deterministic doesn't even come into play here, it is not relevant. it affects nothing.
The decode( replace(translate())....., my_to_number() ) will be faster if it can short circut having to call my_to_number() a couple of time (decode, replace, translate are pretty fast). It will depend on the data, but since we know some of the data is bad-- we'll short circut a couple of times so it probably is worth the effort.
deterministic
Barbara Boehmer, January 14, 2002 - 10:42 pm UTC
Tom,
You say, "deterministic doesn't even come into play here, it is not relevant. it affects nothing."?
My Oracle Advanced PL/SQL Student Guide page 2-28 (kit produced 2/23/01 BOM Updated 7/20/00) says, "The hint DETERMINISTIC helps the optimizer avoid redundant function calls." Further up on the page, it says, "Means the SQL may choose to use a cached copy of the return result rather than actually invoking the function."
So,if there are many of the same value, wouldn't the function potentially only be invoked once per value, using cached results for repeat values, thereby running faster?
I jumped in on this one because several of us have been bouncing around various ideas about validation of numbers, dates, and so forth, with and without functions, with and without deterministic, on the OTN forums, the PL/SQL pipelines, and Orafans. I am hoping to get detailed clarification, so that, in the future, I can just provide a link to this as a final reference. I think most of us, at least on the OTN forums, regard you as the ultimate authority.
Also, I know you said that the combination of decode and replace and translate with an occasional function call is faster than multiple function calls, but will you please provide some more detail as to why or better yet some proof? I tried SET AUTOTRACE ON and running your function and mine and saw no difference, but it was on a very small sample of data. I didn't try TKPROF.
Thanks,
Barbara
April 01, 2008 - 2:32 pm UTC
There is NO way the optimizer will be able to avoid redundant calls here as there are no redudant calls to be removed.
It would not reuse the values for called plsql within the same result set.
So, here is the complete example:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( tmpvalue varchar2(10), tmptype varchar2(1) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 1234567890, 'N' from all_objects;
17130 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 'abcdefghij', 'N' from all_objects;
17130 rows created.
<b>so, I've made half the rows numbers, half the rows not numbers...</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package my_state
2 as
3 cnt number;
4 end;
5 /
Package created.
<b>that'll be so we can count how many times our function was invoked...</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function non_deterministic( p_num in varchar2 ) return number
2 as
3 x number;
num_val_error exception;
pragma exception_init( num_val_error, -6502 );
4 begin
5 my_state.cnt := my_state.cnt+1;
6 x := to_number( p_num );
7 return 1;
8 exception
9 when num_val_error then return 0;
10 end;
11 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function is_deterministic( p_num in varchar2 ) return number
2 DETERMINISTIC
3 as
4 x number;
num_val_error exception;
pragma exception_init( num_val_error, -6502 );
5 begin
6 my_state.cnt := my_state.cnt+1;
7 x := to_number( p_num );
8 return 1;
9 exception
10 when num_val_error then return 0;
11 end;
12 /
Function created.
<b>and those are the two functions.... Now, we'll test the decode vs no decode and deterministic vs non deterministic</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
<b>using CBO -- just in case this deterministic option would be a function of it</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
2 from t
3 where tmptype = 'N'
4 and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
5 NULL, non_deterministic(tmpvalue), 0 ) = 0
6 /
COUNT(*)
----------
17130
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
17130
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
2 from t
3 where tmptype = 'N'
4 and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
5 NULL, is_deterministic(tmpvalue), 0 ) = 0
6 /
COUNT(*)
----------
17130
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
17130
PL/SQL procedure successfully completed.
<b>so, that shows that both types of functions were called 17,130 times -- there is no short circut there.
</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
2 from t
3 where tmptype = 'N'
4 and non_deterministic(tmpvalue) = 0
5 /
COUNT(*)
----------
17130
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
34260
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_state.cnt := 0
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
2 from t
3 where tmptype = 'N'
4 and is_deterministic(tmpvalue) = 0
5 /
COUNT(*)
----------
17130
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( my_state.cnt );
34260
PL/SQL procedure successfully completed.
<b>and again -- both were called the same number of times... Now for the tkprof</b>
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
NULL, non_deterministic(tmpvalue), 0 ) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.26 1.26 0 89 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.26 1.26 0 89 5 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
NULL, is_deterministic(tmpvalue), 0 ) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.29 1.29 0 89 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.30 1.29 0 89 5 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and non_deterministic(tmpvalue) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.16 5.16 0 89 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.16 5.17 0 89 5 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and is_deterministic(tmpvalue) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.13 5.13 0 89 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.13 5.13 0 89 5 1
<b>here, bypassing calling PLSQL when possible made a huge difference, huge difference. Now, the cool thing is -- looking forward to 9i, the numbers on the same exact box were:</b>
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
NULL, non_deterministic(tmpvalue), 0 ) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.71 0.72 0 96 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.71 0.80 0 96 3 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and decode( replace(translate(tmpvalue,'+-1234567890.','0000000000000'),'0',''),
NULL, is_deterministic(tmpvalue), 0 ) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.73 0.73 0 96 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.74 0.73 0 96 3 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and non_deterministic(tmpvalue) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.03 1.04 0 96 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.05 1.08 0 96 3 1
********************************************************************************
select count(*)
from t
where tmptype = 'N'
and is_deterministic(tmpvalue) = 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.01 1.00 0 96 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.02 1.01 0 96 3 1
<b>so, there is still a marked improvement by short circuting the call to PLSQL but it is not nearly as much AND in fact, the query that doesn't short circut in 9i actually runs faster then the 8i query showing the speed up in 9i related to calling PLSQL from SQL (it is much reduced in 9i)</b>
Deterministic is mostly useful with function based indexes....
additional validation scenario
Barbara Boehmer, January 14, 2002 - 11:22 pm UTC
Tom,
It occurred to me that which method is preferred might depend on the scenario and how you want to handle it. In the original post above, Alan apparently wants to identify the invalid data, the character strings in the supposed numeric field. I don't know if he then has plans for some data correction or what.
I have a slightly different approach when transferring data from old systems where the data wasn't validated to new ones where it is validated. If the data in the old table is invalid, I typically just want a null value inserted into the corresponding column in the new table and don't plan to try to clean up the old data. For example, if C is found in an age column that should be numeric, I don't plan to try to figure out what they meant to input when they typed C. So, I typically use something like (simplified for demonstration purposes):
insert into new_table (number_column)
select numbers_only (varchar2_column)
from old_table;
Would this be the best approach under these circumstances, or would you recommend something else?:
Thanks again,
Barbara
January 15, 2002 - 8:57 am UTC
sure -- that works and works cleanly. You can use the decode/replace/translate trick to filter even faster as shown above.
Nice trick
Alan, January 15, 2002 - 5:00 am UTC
An additional problem I have is that the numbers aren't necessarily using the same decimal format. For example some are entered using british nls settings and others german. This results in a mixture of number types such as 1,2399.34, 12399.34, or 12399,34. So I think I'll definitately need a user function in there somewhere (or do you think extra replace would do the trick).
My only goal is to identify which rows do not contain valid numbers, so I can flag them.
January 15, 2002 - 9:31 am UTC
you would add +- and , to the list in the replace (add three 0's as well) and then code my_to_number to try various formats when converting the number....
Excellent!
Barbara Boehmer, January 15, 2002 - 7:24 pm UTC
Tom,
Thank you very much for providing the explanation and proof in the form of a detailed example including TKPROF. It was exactly what I was looking for. Anytime this subject comes up, I can now just post a response using your method with decode, replace, translate, (including + and - just in case) and your function (without deterministic) and provide a link to this for the details.
Thanks,
Barbara
Great
Alan, January 16, 2002 - 5:36 am UTC
Thanks for the help Tom. Fantastic as always.
Optimizer And Deterministic Function
Mike, January 18, 2002 - 2:26 pm UTC
Tom,
In your example, the function is referenced only once for each row, it makes sense the optimizer doesn't do anything to it, just like you said, "there are no redudant calls to be removed". Also the function modifies the value of cnt in my_state package, it probably should be called every time it's referenced. However, in the following example, I thought the optimizer should be able to remove some redudant calls, but it didn't.
SQL> create table t as select 1 n from all_objects;
Table created.
SQL> create or replace function f1 return number
2 as
3 i number;
4 begin
5 for i in 1..100 loop
6 null;
7 end loop;
8 return 1;
9* end;
SQL> /
Function created.
SQL> l
1 create or replace function f2 return number deterministic
2 as
3 i number;
4 begin
5 for i in 1..100 loop
6 null;
7 end loop;
8 return 1;
9* end;
SQL> /
Function created.
SQL> analyze table t compute statistics for table for all columns;
Table analyzed.
SQL> alter session set timed_statistics = true;
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> select max(f1), count(f1) from t where f1 = 1;
MAX(F1) COUNT(F1)
---------- ----------
1 25043
SQL> select max(f1), count(1) from t where f1 = 1;
MAX(F1) COUNT(1)
---------- ----------
1 25043
SQL> select max(f2), count(f2) from t where f2 = 1;
MAX(F2) COUNT(F2)
---------- ----------
1 25043
SQL> select max(f2), count(1) from t where f2 = 1;
MAX(F2) COUNT(1)
---------- ----------
1 25043
SQL> select count(*) from t where f1 = 1;
COUNT(*)
----------
25043
SQL> select count(*) from t where f2 = 1;
COUNT(*)
----------
25043
Now look at the trace file:
***************************************
select max(f1), count(f1)
from
t where f1 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 6.45 6.48 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 6.53 6.56 0 42 12 1
********************************************************************************
select max(f1), count(1)
from
t where f1 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.29 3.31 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.36 3.38 0 42 12 1
********************************************************************************
select max(f2), count(f2)
from
t where f2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 6.52 6.55 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 6.59 6.62 0 42 12 1
********************************************************************************
select max(f2), count(1)
from
t where f2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.48 3.50 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.55 3.57 0 42 12 1
********************************************************************************
select count(*)
from
t where f1 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.09 0.09 0 42 12 1
********************************************************************************
select count(*)
from
t where f2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 42 12 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.09 0.09 0 42 12 1
It looks like the deterministic key word doesn't make any difference. If I have count(f1) and max(f1) in the select statement, it's going to call function twice. It's also interesting that the optimizer only evalute f1 = 1 once for the whole table instead of once for each row even when f1 is not a deterministic function. When I run the following query twice, it gives different results.
SQL> l
1 select * from
2 ( select dbms_random.random r from t )
3 where r > 0
4* and rownum < 10
SQL> /
no rows selected
SQL> /
R
----------
-911661048
-664769205
150161117
-397720797
-864258392
-1.555E+09
-845515666
-1.723E+09
765505919
9 rows selected.
Thanks,
Mike
January 19, 2002 - 10:14 am UTC
That is sort of what I said -- it doesn't factor it out in these cases, you can HELP it to do that.
DETERMINISTIC, so far, it mostly useful with function based indexes (where it is MANDATORY).
There are cases where it will factor it out on its own in the where clause, consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package state_pkg
2 as
3 cnt number;
4 end;
5 /
Package created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function f return number
2 as
3 begin
4 state_pkg.cnt := state_pkg.cnt+1;
5 return 1;
6 end;
7 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select rownum r from all_objects;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();
R
----------
1
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
17137
PL/SQL procedure successfully completed.
<b>so, there is called it once per row apparently, sort of as we expected by now...</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = (select f() from dual);
R
----------
1
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
1
PL/SQL procedure successfully completed.
<b>Ahh, interesting, by hiding it in a subquery, it did it once per QUERY. But, you'll discover this doesn't always work as we'll see in a moment...</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t, (select f() x from dual) where r = x;
R X
---------- ----------
1 1
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
17138
PL/SQL procedure successfully completed.
<b>You see, if we turned the "where r = (subquery)" into a join (can happen) we are back to once/row. What we have to do then is involve rownum or some other thing (group by, order by) in the subquery to make us materialize the subquery and use that instead, so:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t, (select f() x from dual where rownum=1) where r = x;
R X
---------- ----------
1 1
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
1
PL/SQL procedure successfully completed.
<b>Now, we are back to one. This all changes however, if we index R:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(r);
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();
R
----------
1
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
2
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
<b> now the optimizer evaluated F and used that value to do an INDEX RANGE scan... Less rows to work with. We can see it does this only one time (it calls the function 2 times for some reason but only does that once) by updating some rows:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec state_pkg.cnt := 0;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where r = f();
R
----------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
19 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( state_pkg.cnt );
2
PL/SQL procedure successfully completed.
<b>and seeing that the function was still only called 2 times...</b>
What I like to do is:
A) if the function is in fact returning a constant like yours -- USE A BIND VARIABLE or if you cannot bind -- use an application context and SYS_CONTEXT. They are appropriate for constants. Bind variables = BEST, sys_context = 2cnd best, plsql function = way way back in 3rd.
B) use the (subquery) with rownum trick whenever possible
C) postpone calling functions in the select list until the very last possible moment to ensure they are called as infrequently as possible, eg: instead of coding:
select f(x), .... from .....
I would:
select f(x), ....
from ( select ROWNUM, ..... from .... )
where the inner query is simply the original query with the functions removed and rownum added (so no view merging takes place)
What if without REPLACE
Irina, February 04, 2002 - 3:56 am UTC
translate(l_str,'x0123456789','x') ;
translate(l_str,chr(0)||'0123456789',chr(0)) - delete all digits, replace is not needed
How is this possible
Kumar, June 19, 2002 - 5:24 pm UTC
select
((to_number(b.ARRANGE_FEE_TXT))),b.tr_id
from
tr_deal_source b,
tr_fac_currency d ,
deal_source c
where c.deal_id = b.deal_id
and b.tr_id = d.tr_id
and c.data_source_id = b.data_source_id
and c.data_source_id = 13
and c.deal_currency_id = d.currency_id
and b.ARRANGE_FEE_TXT not in ('N/A','TBD','NA')
and b.ARRANGE_FEE_TXT is not null
Why i am getting Invalid Number Error even filtering
Varchar value from the table for that column?
Only possble varchar value in that column is N/A,TBD,NA
(Note:I checked Data in the Table)
June 19, 2002 - 9:50 pm UTC
well, without the table defs themselves -- it is not possible to answer.
You assume it is ont he to_number(arrange_fee_text), but it might not be.
Re-How is this possible
Kumar, June 20, 2002 - 8:44 am UTC
I am sure the error i am getting bcos of to_number function in the select.If i remove the to_number function from the select then it retrieves without any error.
workaround :
Using Decode,replace & Translate in to_number function solves this.But my question is even after filtering the varchar value in where clause why to_number function is not working?
Tabel def:
TR_DEAL_SOURCE:
deal_id Varchar2(10)
tr_id Number(10,0)
arrange_fee_tx Varchar2(25)
DEAL_SOURCE
deal_id Varchar2(10)
data_source_id Number(2,0)
deal_currency_id Varchar2(4)
June 20, 2002 - 11:54 am UTC
you are assuming some "order of operation" -- an order of operation which is -- in sql -- explicitly not defined. Can you supply a couple (2 or 3) rows of data with which to reproduce this issue?
Response-How this is possible
Kumar, June 20, 2002 - 1:05 pm UTC
June 20, 2002 - 1:18 pm UTC
it isn't a bug in the link you referred me to. They were 100% dependent on the order of operation in a sql predicate and there IS NO DEFINED ORDER OF OPERATION in a sql predicate.
The only bug in that link is in the query posed by the orginal poster.
If you give me some rows, I'll explain to you what's happening. This is the old "if it is a number, store it in a number column, if it is a date, put it in a date column" problem. Give me a small test case and I'll be glad to tell you whats going on.
Now i know how is this happening
Kumar, June 21, 2002 - 1:49 pm UTC
Sorry tom.
The problem is with the data.one of the value is (1,2345).
Thanks for your suggestion!
Now i know how is this happening
Kumar, June 21, 2002 - 2:02 pm UTC
Sorry tom.
The problem is with the data.one of the value is (1,2345).
Thanks for your suggestion!
Add another Where
A reader, July 19, 2002 - 2:30 pm UTC
Tom
I agree with your solution totally.
But the following query just retrieves all the values. What if we need to query a specific tmpvalue such as
Where tmpvalue=123.4. If I plug this where clause in the following query it again gives me a number invalid error?
select * from t where tmptype = 'N'
and decode( replace(translate(tmpvalue,'1234567890.','00000000000'),'0',''),
NULL, my_to_number(tmpvalue), 0 ) = 0;
I appreciate all the help
Thanks
July 19, 2002 - 5:14 pm UTC
add where tmpvalue = '123.4' then. compare a string to a string.
order by number in varchar2 columns
Sean, November 02, 2003 - 9:24 pm UTC
create table t(c varchar2(20));
insert into t
values('101St');
insert into t
values('11CT');
insert into t
values('10 Street');
I need the result ordered by the number and ignore the char or space after the number as shown bellow:
11CT
12 Street
101St
Thanks so much for your help.
Sean
November 03, 2003 - 7:10 am UTC
ops$tkyte@ORA920> select c, to_number(substr( c, 1, length(c)-length(ltrim(translate(c,'01234567890','0000000000'),'0')))) cn
2 from t
3 order by to_number(substr( c, 1, length(c)-length(ltrim(translate(c,'01234567890','0000000000'),'0'))))
4 /
C CN
-------------------- ----------
10 Street 10
11CT 11
101St 101
hello world
ops$tkyte@ORA920>
Split a VARCHAR2
M S Reddy, November 25, 2003 - 4:20 pm UTC
Tom,
I have a small issue.consider this example.
TAB1
c1
-------------------------------
123 345 567 789 980 234 543 567
1234567 456789
i want to write a query and select something like this
c1
-------------------------------
123
345
567
789
980
234
543
567
1234567
456789
make one record into many records by spilting using space.
can this be done in one single SQL without looping?
my requirement is to spilt and then join that with someother table.
Thanks,
M S Reddy
November 25, 2003 - 4:34 pm UTC
it could, if there were some reasonable upper limit on the number of tokens, but it would be really obscure.
a pipeline function is what you want to investigate here.
ops$tkyte@ORA920PC> create or replace type myTable as table of varchar2(25)
2 /
Type created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace function str2tbl( p_str in varchar2 ) return myTable
2 pipelined
3 as
4 l_str long default p_str || ' ';
5 l_n number;
6 begin
7 loop
8 l_n := instr( l_str, ' ' );
9 exit when (nvl(l_n,0) = 0);
10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
11 l_str := ltrim( substr( l_str, l_n+1 ) );
12 end loop;
13 return;
14 end;
15 /
Function created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table t ( id int, str varchar2(2000) );
Table created.
ops$tkyte@ORA920PC> insert into t values ( 1, '123 345 567 789 980 234 543 567 1234567 456789');
1 row created.
ops$tkyte@ORA920PC> insert into t values ( 2, '32123 3455 5567' );
1 row created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from t;
ID STR
---------- ------------------------------
1 123 345 567 789 980 234 543 56
7 1234567 456789
2 32123 3455 5567
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select t.id, t2.column_value
2 from t, TABLE(str2tbl(t.str)) t2
3 /
ID COLUMN_VALUE
---------- -------------------------
1 123
1 345
1 567
1 789
1 980
1 234
1 543
1 567
1 1234567
1 456789
2 32123
2 3455
2 5567
13 rows selected.
Thanks!!
M S Reddy, November 26, 2003 - 9:30 am UTC
Tom,
thanks a lot .that helped me a lot.
M S Reddy
for ver 8.1.7
Pal, January 08, 2004 - 8:50 pm UTC
Hi Tom,
My problem is similar to above but I have Ora 8.1.7
and it seems the pipeline works only for ver 9
Also I have max 5 delimited data per row as shown
TABLE1
c1
-------------------------------
123,345,567,789,980
111,222,333
i want to write a query and select something like this
c1
-------------------------------
123
345
567
789
980
111
222
333
Thanks alot in advance,
Pal
January 09, 2004 - 8:11 am UTC
either of the following, probably #2 is best but as always -- test test test
ops$tkyte@ORA817DEV> create table t ( a int, x varchar2(40) );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t values ( 1, '123,345,567,789,980' );
1 row created.
ops$tkyte@ORA817DEV> insert into t values ( 2, '111,222,333' );
1 row created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select a, substr(x, instr(x,',',1,rr.r)+1, instr(x,',',1,rr.r+1)-instr(x,',',1,rr.r)-1 ) x
2 from
3 (select a, ','||x||',' x, length(x)-length(replace(x,',',''))+1 cnt
4 from t ) t,
5 (select rownum r
6 from all_objects
7 where rownum <= 50) rr
8 where rr.r <= t.cnt
9 order by 1, 2
10 /
A X
---------- ------------------------------------------
1 123
1 345
1 567
1 789
1 980
2 111
2 222
2 333
8 rows selected.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace type myTable as table of varchar2(25)
2 /
Type created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2tbl( p_str in varchar2 ) return myTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTable := myTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data(l_data.count) := ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
12 l_str := ltrim( substr( l_str, l_n+1 ) );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select a, column_value
2 from t, TABLE( cast(str2tbl(x) as myTable) ) x
3 /
A COLUMN_VALUE
---------- -------------------------
1 123
1 345
1 567
1 789
1 980
2 111
2 222
2 333
8 rows selected.
German Decimal notation
Yogesh, January 12, 2004 - 3:50 am UTC
In German, decimal notation is contrary to the British one. They use decimal points as separators for thousands and decimal comma as separator for the non-integer part of the number.
If we define a datatype as number and we need to handle above stuff, what approach should be used ?
March 04, 2008 - 7:16 am UTC
no problems, database sort of "does that"
decimals and commas are just formats... they are not really "stored" with the number..
consider:
ops$tkyte@ORA9IR2> alter session set NLS_NUMERIC_CHARACTERS='.,';
Session altered.
<b>thats american (and others...)</b>
ops$tkyte@ORA9IR2> select 1,2 , '1,2',
2 to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
3 from t;
1 2 '1, TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- --- ------------ ------------
1 2 1,2 123,456.789 123,456.789
<b>if you use G and D, you get the group separator and decimal point -- here they are the "same"</b>
ops$tkyte@ORA9IR2> select 1,2 , to_number( '1,2' ),
2 to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
3 from t;
select 1,2 , to_number( '1,2' ),
*
ERROR at line 1:
ORA-01722: invalid number
<b>and 1,2 is not a number in this case...</b>
ops$tkyte@ORA9IR2> insert into t values ( 1.2 );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 1,2 );
insert into t values ( 1,2 )
*
ERROR at line 1:
ORA-00913: too many values
<b>this is to demonstrate a certain ambiguity that'll crop up</b>
ops$tkyte@ORA9IR2> insert into t values ( '1,2' );
insert into t values ( '1,2' )
*
ERROR at line 1:
ORA-01722: invalid number
ops$tkyte@ORA9IR2> rollback;
Rollback complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set NLS_NUMERIC_CHARACTERS=',.';
Session altered.
<b>this is your format..</b>
ops$tkyte@ORA9IR2> select 1,2 , '1,2',
2 to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
3 from t;
1 2 '1, TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- --- ------------ ------------
1 2 1,2 123.456,789 123,456.789
<b>see the difference -- ., insetead of ,.</b>
ops$tkyte@ORA9IR2> select 1,2 , to_number( '1,2' ),
2 to_char( x, '999g999d999' ), to_char(x, '999,999.999' )
3 from t;
1 2 TO_NUMBER('1,2') TO_CHAR(X,'9 TO_CHAR(X,'9
---------- ---------- ---------------- ------------ ------------
1 2 1,2 123.456,789 123,456.789
<b>but see how 1,2 are two COLUMNS, not 1,2 the number -- you need to "string" it... that is the ambiguity there</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1.2 );
1 row created.
<b>(I'm sitting next to a guy that wrote the NLS course) according to him, the parser has . and comma hard coded - so that is really "1,2" but...</b>
ops$tkyte@ORA9IR2> insert into t values ( 1,2 );
insert into t values ( 1,2 )
*
ERROR at line 1:
ORA-00913: too many values
<b>1,2 is two columns again so ...</b>
ops$tkyte@ORA9IR2> insert into t values ( '1,2' );
1 row created.
<b>that goes there...</b>
It is all about the formats (and the sql parser which must tokenize things -- hence the hard coded "." and "," meaning on parse)
Great Explaination
Yogesh, January 21, 2004 - 3:14 am UTC
Thanks for the explaination tom, I can even set NLS_NUMERIC_CHARACTERS at instance level, right ? But is there any way by which I can refer 1,2 as 1.2 without using to_char ?
January 21, 2004 - 6:45 am UTC
you can set things at the instance level HOWEVER if the client sets any NLS_* parameter, they will all default for that client -- so, in practicality, no, you cannot really set things at the instance level (and expect them to be in place by default).
a login trigger can help you.
consider this:
insert into t values ( 1,2 )
select 1,2 from dual;
is that inserting one or two columns into T? See the ambiguity -- you have to use "to_number" on a string if you want to use literals with ","'s in it like that.
Number-in-a-VARCHAR2: Any Holes in This Approach?
Mac, January 21, 2004 - 10:02 am UTC
This function seems to work. Anybody see any potential problem?
CREATE OR REPLACE FUNCTION is_numeric
(p_string IN VARCHAR2)
RETURN VARCHAR2
IS
l_nv_err EXCEPTION;
l_number NUMBER;
l_result VARCHAR2(1) := 'N';
PRAGMA EXCEPTION_INIT(l_nv_err, -6502);
BEGIN
BEGIN
l_number := TO_NUMBER(p_string);
l_result := 'Y';
EXCEPTION
WHEN invalid_number THEN
l_result := 'N';
WHEN l_nv_err THEN
l_result := 'N';
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Error in is_numeric: '||SQLERRM);
END;
--
RETURN(l_result);
END;
/
SQL> select*from dual where is_numeric('12345')='Y';
D
-
X
SQL> select*from dual where is_numeric(12345)='Y';
D
-
X
SQL> select*from dual where is_numeric('1234A')='Y';
no rows selected
January 21, 2004 - 3:03 pm UTC
it works, much in the same fashion as above...
I just assumed ANY error converting the string to a number meant 'not a number'
String To Number Conversion
Denise, February 09, 2004 - 9:49 am UTC
Hi Tom
We have an Address Table with the Zip Column as a Varchar2(10).
We also have a package that does matching records with the
first 5 digits of the zip code(zip column).
We noticed that when the first digit of the zip code was
zero(0)...it was being trimmed off (see below):
SQL> select zip from address where idnumber=1234;
ZIP
----------
06826-4204
SQL> declare
2 zipc number(5);
3 begin
4 select substr(zip,1,5) into zipc
5 from address
6 where idnumber=1234;
7
8 dbms_output.put_line(zipc);
9 end;
10 /
6826
************************************************************
I tried several scenarios trying to convert the zip column
to a number then assigning the value to a number variable
(zipc)...keep getting the ora-01722 error.
SQL> declare
2 zipc varchar2(5);
3 begin
4 select to_number(substr(zip,1,5)) into zipc
5 from address
6 where idnumber=1234;
7 dbms_output.put_line(zipc);
8 end;
9 /
6826
PL/SQL procedure successfully completed.
SQL>
1 declare
2 zipc number(5);
3 begin
4 select to_number(substr(zip,1,5)) into zipc
5 from address
6 where idnumber=1234;
7 dbms_output.put_line(zipc);
8* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4
SQL> declare
2 zipc number(5);
3 begin
4 select to_number(zip,'99999') into zipc
5 from address
6 where idnumber=1234;
7 dbms_output.put_line(zipc);
8 end;
9 /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4
************************************************************
perhaps this is the wrong approach to solving the issue
of why leading zero(s) are being trimmed off.
why isn't the to_number function working here?
you mention replace and deterministic..are those functions
that can be applied here?
thanks!
February 09, 2004 - 9:58 am UTC
it is not getting "stripped off"
NUMBERS (in any sense of the word) do not have leading zeroes. When you get a check does it read
$000,000,000,000,000,123.21
or does it read
$123.21
leading zeroes on number types "don't make sense".
Because you convert this into a number, the zeroes (which make sense in a character string) "go away"
So, my question to you -- why are you trying to put this string data (it must be string data since you want the string unmodified here) into a number in the first place????
point well taken....
Denise, February 09, 2004 - 10:15 am UTC
the problem we are encountering is that records that are
being imported(and run through this package) are not being
matched correctly because of the leading zero issue.
When we run the unmatched reports we are seeing:
John Doe 123 Main Street 06842-XXXX (in the system)
John Doe 123 Main Street 6842-XXXX(from the import file)
We are matching on last name, line1 and zip(first 5 characters) and John Doe does not get included in the matching because the leading zero is not there(for want of a better description). We have verfied this through the
reports.
My logic in trying to convert the string to a number was
based on a zero being numerically valid unless you
explicity insert code to remove leading zero's.
This is not a package I wrote it's part of the Software
Application.
February 09, 2004 - 10:31 am UTC
lpad it
lpad( zip, '0', 10 )
thanks Tom!...works perfectly!!!!
Denise, February 09, 2004 - 10:50 am UTC
SQL> declare
2 zipc varchar2(5);
3 begin
4 select lpad(zip,5,'0') into zipc
5 from address
6 where idnumber=1234;
7 dbms_output.put_line(zipc);
8 end;
9 /
06826
PL/SQL procedure successfully completed.
Nice
Steve, February 25, 2004 - 1:00 am UTC
Hi Tom,
Is it possible in Oracle to get the maximum positive number
and maximum negative number?Are there any constants like
POSITIVE_INFINITY and NEGATIVE_INFINITY in Oracle?I wish to know this.Please do reply.
Thanks in advance.
February 25, 2004 - 9:03 am UTC
other than the documented limits, no, there is no "constant". you could certainly create a package with constants in it for your own use.
OK
Siva, March 26, 2004 - 7:55 am UTC
Hi Tom,
Oracle documentation says "Positive numbers can take a max.
value of 9.999...*10 raised to 125 and for negative numbers
as 1.0*10 raised to -130".Is there any way in Oracle to
retrieve such values?I think Mr.Steve was referring like this.*I mean,Any programmatic way to retrieve such values Please do reply.
Bye!
March 26, 2004 - 9:30 am UTC
sure, just select them?
ops$tkyte@ORA9IR1> select 9.999 * power(10,125) from dual;
9.999*POWER(10,125)
-------------------
9.999E+125
ops$tkyte@ORA9IR1> select power(10,-130) from dual;
POWER(10,-130)
--------------
1.000E-130
but they are NOT +- infinity, they are "really numbers"
Looking for an approach to reduce the overhead of function call
Matt, March 28, 2004 - 6:00 pm UTC
I have some data with a field varchar2(10). I need to query this data and generate a computed value based on this field. There may be many of these fields in the result set with the same value. I am looking for ways of reducing the overhead of the PL/SQL calls.
My options appear to be:
i) Remove the function and do it all in SQL
ii) Reduce the number of function calls
iii) Cache values calculated by the function to reduce the computation required for duplicate field values.
iv) Something I haven't thought of.
So,
i) I implemented the algorithm in SQL and it ran significantly slower.
ii) I can't remove any PL/SQL calls since each row needs to have the computed value calculated (a function call)
iii) I am considering generating a package to store those values already calculated. The function would need to carry out a cache lookup prior to doing any of the computation. This should reduce some of the required work.
iv) I am looking for alternative approaches, hence this post.
Can you please comment on the above? In particular I am looking for alternative approaches that I have not considered.
THanks,
March 28, 2004 - 6:46 pm UTC
i) that i don't believe -- show me the before and after please! somethings up there.
Here arethe two implementations...
Matt, March 28, 2004 - 10:28 pm UTC
--
drop table test_data
/
create table test_data (id varchar2(10), id_check number(1))
/
insert into test_data values ('5600012357',9)
/
insert into test_data values ('5500000278',4)
/
insert into test_data values ('5500003074',5)
/
insert into test_data values ('5500008129',2)
/
insert into test_data values ('5500012357',8)
/
insert into test_data values ('5500023478',0)
/
insert into test_data values ('5500047359',4)
/
insert into test_data values ('5500067253',5)
/
insert into test_data values ('5500079467',6)
/
insert into test_data values ('5500089000',8)
/
insert into test_data values ('5500099352',6)
/
insert into test_data values ('5500102781',5)
/
insert into test_data values ('5500139654',8)
/
insert into test_data values ('5500200000',4)
/
insert into test_data values ('5500289367',3)
/
insert into test_data values ('5500346583',7)
/
insert into test_data values ('5600000278',2)
/
insert into test_data values ('5600003074',3)
/
insert into test_data values ('5600008129',0)
/
insert into test_data values ('5600012357',9)
/
insert into test_data values ('5600023478',8)
/
insert into test_data values ('5600047359',2)
/
insert into test_data values ('5600067253',3)
/
insert into test_data values ('5600079467',4)
/
insert into test_data values ('5600089000',6)
/
insert into test_data values ('5600099352',4)
/
insert into test_data values ('5600102781',3)
/
insert into test_data values ('5600139654',6)
/
insert into test_data values ('5600200000',2)
/
insert into test_data values ('5600289367',1)
/
insert into test_data values ('5600346583',5)
/
commit
/
create or replace function gen_check(p_id in varchar2) return pls_integer
deterministic is
-- l_result pls_integer;
i pls_integer :=0;
d pls_integer :=0;
l_buffer varchar2 (50) := '';
l_val pls_integer :=0;
l_multiply boolean := true;
l_check pls_integer := 0;
begin
if p_id is not null then
l_buffer := UPPER(p_id);
i := length(p_id);
l_multiply := TRUE;
WHILE i > 0 LOOP
d := ascii(substr(l_buffer,i,1));
IF (l_multiply = TRUE) THEN
d := d*2;
END IF;
l_multiply := NOT l_multiply;
WHILE d > 0 LOOP
--dbms_output.PUT_LINE('d='||d);
--dbms_output.PUT_LINE('mod(d,10)='||MOD(d,10));
l_val := l_val + MOD(d,10);
--dbms_output.PUT_LINE('val='||val);
d := floor(d / 10);
END LOOP;
i := i - 1;
END loop;
--dbms_output.PUT_LINE('val='||val);
l_check := MOD((10 - MOD(l_val,10)),10);
/*
select mod((10 - mod(sum(padded_val_ch),10)),10) into l_check
from ( select sum(substr(padded_val,length(padded_val)- row_num + 1,1)) as padded_val_ch
-- ,pos
-- ,doubled_val
from ( select pos
-- ,chr
-- ,ascii_val
,doubled_val
,lpad(doubled_val,5,'0') as padded_val
,row_number() over (partition by pos,doubled_val order by doubled_val ) row_num
from ( select rownum as pos
-- ,substr(p_id,length(p_id) - rownum + 1,1) as chr
-- ,ascii(substr(p_id,length(p_id) - rownum + 1,1)) as ascii_val
-- ,case when mod(rownum,2) = 1 then 'Y'
-- else 'N' end to_double
,case when mod(rownum,2) = 1 then 2*ascii(substr(p_id,length(p_id)- rownum + 1,1))
else ascii(substr(p_id,length(p_id)- rownum + 1,1)) end as doubled_val
from table(vtable(length(p_id))) )
,table(vtable(5)) )
group by pos, doubled_val );
*/
else
null;
end if;
return l_check;
-- return l_result;
end gen_check;
/
create type array
as table of number
/
create or replace function vtable (n in number default null)
return array
pipelined
as
begin
for i in 1.. nvl(n,999999999)
loop
pipe row(i);
end loop;
return;
end;
/
create or replace function gen_check_sql(p_id in varchar2) return pls_integer
deterministic is
l_check pls_integer := 0;
begin
if p_id is not null then
select mod((10 - mod(sum(padded_val_ch),10)),10) into l_check
from ( select sum(substr(padded_val,length(padded_val)- row_num + 1,1)) as padded_val_ch
-- ,pos
-- ,doubled_val
from ( select pos
-- ,chr
-- ,ascii_val
,doubled_val
,lpad(doubled_val,5,'0') as padded_val
,row_number() over (partition by pos,doubled_val order by doubled_val ) row_num
from ( select rownum as pos
-- ,substr(p_id,length(p_id) - rownum + 1,1) as chr
-- ,ascii(substr(p_id,length(p_id) - rownum + 1,1)) as ascii_val
-- ,case when mod(rownum,2) = 1 then 'Y'
-- else 'N' end to_double
,case when mod(rownum,2) = 1 then 2*ascii(substr(p_id,length(p_id)- rownum + 1,1))
else ascii(substr(p_id,length(p_id)- rownum + 1,1)) end as doubled_val
from table(vtable(length(p_id))) )
,table(vtable(5)) )
group by pos, doubled_val );
else
null;
end if;
return l_check;
-- return l_result;
end gen_check_sql;
/
select t.id
,t.id_check tbl_val
,gen_check(t.id) calc_val
,case when t.id_check = gen_check(t.id) then 'SUCCESS'
else 'FAILURE'
end
from test_data t
/
select t.id
,t.id_check tbl_val
,gen_check(t.id) calc_val
,case when t.id_check = gen_check_sql(t.id) then 'SUCCESS'
else 'FAILURE'
end
from test_data t
/
Over the small data set provided the two implementations are comparable. When run against 500,000 rows the SQL implementation was significantly slower - I don't have that data to hand.
My conclusion was that the SQL implementation was more CPU expensive since it has to loop more times for each "add up all the digits" computation (as SQL does not know when it has processed all the digits that it loops through)
At the point where I realised the PL/SQL routine was faster I stopped looking at incorporating the SQL in the larger query. As I expected that this result would carry through to an 'entirely in SQL approach'.
I now expect you will tell me that the all in SQL will be the fastest implementation.
March 29, 2004 - 9:39 am UTC
Now I understand -- you never ever did a pure SQL implementation. You just have two plsql functions :)
Ok, assuming we can assume a max length on the input (i chose 10 from your example), we can take a function like this:
ops$tkyte@ORA9IR2> @mystat "CPU used by this session"
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> define S="&1"
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
NAME VALUE
------------------------------ ----------
CPU used by this session 3764
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select id, id_check, gen_check(id) plsql
2 from test_data
3 /
10031 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_DATA'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
0 physical reads
0 redo size
262962 bytes sent via SQL*Net to client
7847 bytes received via SQL*Net from client
670 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10031 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 3764 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
NAME V DIFF
------------------------------ ---------- ----------
CPU used by this session 3846 82
<b>running in about 0.82 cpu seconds for 10,000 records and use SQL like this</b>
ops$tkyte@ORA9IR2> select id, id_check, mod((10-mod(x,10)),10) sql
2 from (
3 select substr(c1,1,1)+substr(c1,2,1)+substr(c1,3,1)+
4 substr(c2,1,1)+substr(c2,2,1)+substr(c2,3,1)+
5 substr(c3,1,1)+substr(c3,2,1)+substr(c3,3,1)+
6 substr(c4,1,1)+substr(c4,2,1)+substr(c4,3,1)+
7 substr(c5,1,1)+substr(c5,2,1)+substr(c5,3,1)+
8 substr(c6,1,1)+substr(c6,2,1)+substr(c6,3,1)+
9 substr(c7,1,1)+substr(c7,2,1)+substr(c7,3,1)+
10 substr(c8,1,1)+substr(c8,2,1)+substr(c8,3,1)+
11 substr(c9,1,1)+substr(c9,2,1)+substr(c9,3,1)+
12 substr(c10,1,1)+substr(c10,2,1)+substr(c10,3,1) x,
13 id, id_check
14 from (
15 select decode( mult, 2, c1, 2*c1 ) c1,
16 decode( mult, 2, 2*c2, c2 ) c2,
17 decode( mult, 2, c3, 2*c3 ) c3,
18 decode( mult, 2, 2*c4, c4 ) c4,
19 decode( mult, 2, c5, 2*c5 ) c5,
20 decode( mult, 2, 2*c6, c6 ) c6,
21 decode( mult, 2, c7, 2*c7 ) c7,
22 decode( mult, 2, 2*c8, c8 ) c8,
23 decode( mult, 2, c8, 2*c8 ) c9,
24 decode( mult, 2,2*c10, c10 ) c10,
25 id, id_check
26 from (
27 select ascii(substr(x,1,1)) c1,
28 ascii(substr(x,2,1)) c2,
29 ascii(substr(x,3,1)) c3,
30 ascii(substr(x,4,1)) c4,
31 ascii(substr(x,5,1)) c5,
32 ascii(substr(x,6,1)) c6,
33 ascii(substr(x,7,1)) c7,
34 ascii(substr(x,8,1)) c8,
35 ascii(substr(x,9,1)) c9,
36 ascii(substr(x,10,1)) c10,
37 decode( mod(length(x),2), 0, 2, 1 ) mult,
38 id, id_check
39 from ( select id, id_check, upper(id) x from test_data )
40 )
41 )
42 )
43 /
10031 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_DATA'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
695 consistent gets
0 physical reads
0 redo size
268444 bytes sent via SQL*Net to client
7847 bytes received via SQL*Net from client
670 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10031 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 3846 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('CPU used by this session')||'%'
NAME V DIFF
------------------------------ ---------- ----------
CPU used by this session 3856 10
<b>running consistently 8 times faster....</b>
If you need more than 10, the "algorithm" should be pretty visible there. I just diagnosed what your code did and "flatlined" it.
Great!
Matt, March 29, 2004 - 5:11 pm UTC
Thanks Tom.
I think that if I had continued on and generated a full SQL version I wouldn't have "flatlined" the SQL quite like you have (and may therefore have ended up with a less efficient implementation).
I think it is a rather neat solution really, almost like a procedural SQL implementation.
Thanks again.
Just for completeness..
Matt, March 29, 2004 - 9:59 pm UTC
The final query.
Fixed:
- a minor typo in the "mult" part
- addition of nvl (as substr appears to return null when looking past the end of the string).
Thanks again.
select id, id_check, mod((10-mod(x,10)),10) sql
from (
select nvl(substr(c1,1,1),0)+nvl(substr(c1,2,1),0)+nvl(substr(c1,3,1),0)+
nvl(substr(c2,1,1),0)+nvl(substr(c2,2,1),0)+nvl(substr(c2,3,1),0)+
nvl(substr(c3,1,1),0)+nvl(substr(c3,2,1),0)+nvl(substr(c3,3,1),0)+
nvl(substr(c4,1,1),0)+nvl(substr(c4,2,1),0)+nvl(substr(c4,3,1),0)+
nvl(substr(c5,1,1),0)+nvl(substr(c5,2,1),0)+nvl(substr(c5,3,1),0)+
nvl(substr(c6,1,1),0)+nvl(substr(c6,2,1),0)+nvl(substr(c6,3,1),0)+
nvl(substr(c7,1,1),0)+nvl(substr(c7,2,1),0)+nvl(substr(c7,3,1),0)+
nvl(substr(c8,1,1),0)+nvl(substr(c8,2,1),0)+nvl(substr(c8,3,1),0)+
nvl(substr(c9,1,1),0)+nvl(substr(c9,2,1),0)+nvl(substr(c9,3,1),0)+
nvl(substr(c10,1,1),0)+nvl(substr(c10,2,1),0)+nvl(substr(c10,3,1),0) x,
id, id_check
from (
select decode( mult, 2, c1, 2*c1 ) c1,
decode( mult, 2, 2*c2, c2 ) c2,
decode( mult, 2, c3, 2*c3 ) c3,
decode( mult, 2, 2*c4, c4 ) c4,
decode( mult, 2, c5, 2*c5 ) c5,
decode( mult, 2, 2*c6, c6 ) c6,
decode( mult, 2, c7, 2*c7 ) c7,
decode( mult, 2, 2*c8, c8 ) c8,
decode( mult, 2, c9, 2*c8 ) c9,
decode( mult, 2,2*c10, c10 ) c10,
id, id_check
from (
select ascii(substr(x,1,1)) c1,
ascii(substr(x,2,1)) c2,
ascii(substr(x,3,1)) c3,
ascii(substr(x,4,1)) c4,
ascii(substr(x,5,1)) c5,
ascii(substr(x,6,1)) c6,
ascii(substr(x,7,1)) c7,
ascii(substr(x,8,1)) c8,
ascii(substr(x,9,1)) c9,
ascii(substr(x,10,1)) c10,
decode( mod(length(x),2), 0, 2, 1 ) mult,
id, id_check
from ( select id, id_check, upper(id) x from test_data )
)
)
)
how to validate
A reader, May 26, 2004 - 12:06 pm UTC
how to validate and see if a number value is negative.
May 26, 2004 - 3:33 pm UTC
check (x < 0 and x is not null)
on the column in the create table statement, or in an alter statment.
assuming you meant a database column of course, in code it is just
if ( x >= 0 or x is null )
then
error.
my question was specific to a select
A reader, May 26, 2004 - 9:42 pm UTC
I have to identify negative numbers and apped a '-' the value. I have to to do this from within a SELECT and not pl/sql code.
How can we do it in sql select ?
May 27, 2004 - 8:55 am UTC
if the number is negative, it'll just happen. try it.
or give me a concrete example to work with and say "this is what we have, this is what I get, this is what i want"
Short circuit
A reader, December 06, 2004 - 5:56 pm UTC
SQL> create table t(i varchar2(10));
Table created.
SQL> insert into t values (1);
1 row created.
SQL> insert into t values(2);
1 row created.
SQL> insert into t values ('3a');
1 row created.
SQL> select * from t;
I
----------
1
2
3a
SQL> select decode(replace(translate(tmpvalue,'1234567890.','00000000000'),'0',''),null,to_number(
2
SQL> select decode(replace(translate(i,'1234567890.','00000000000'),'0',''),null,to_number(i),i) from t;
ERROR:
ORA-01722: invalid number
no rows selected
Why does this error out? I thought that DECODE short circuits i.e. it calls the to_number() only when 'i' contains all digits and .?
Thanks
December 07, 2004 - 9:53 am UTC
that decode( f(x), NULL, to_number(i), i )
is identical to
decode( f(x), NULL, to_number(i), to_number(i) )
the first return value dictates the overloaded decode function that will be used. your first return value was a NUMBER, hence you are using the decode that returns a NUMBER, hence all of the return values are implicitly to_number'ed.
DECODE()
A reader, December 07, 2004 - 10:12 am UTC
Ah, I see then I guess the following will work
SQL> create table t(i varchar2(10));
Table created.
SQL> insert into t values ('1');
1 row created.
SQL> insert into t values ('2.00')
2 /
1 row created.
SQL> insert into t values ('3a');
1 row created.
SQL> insert into t values ('100.0000');
1 row created.
SQL> select case when replace(translate(i,'0123456789. ','000000000000'),'0','') is null then to_char(to_number(i)) else i end
2 from t;
CASEWHENREPLACE(TRANSLATE(I,'0123456789.
----------------------------------------
1
2
3a
100
In other words, if it "looks" like a number, convert it to a number and to_char() it otherwise return the "bad" string.
Thanks!
pruning?!
Gabe, January 06, 2005 - 9:27 am UTC
Since there is no order of operation for the sql predicates Â… for your original test case and answer shouldnÂ’t we do something like Â…
select iv.*
from (select rownum, t.*
from t
where <all the other predicates>
) iv
where decode(replace(translate(tmpvalue,'1234567890.'
,'00000000000'),'0','')
,null, my_to_number(tmpvalue),0 ) = 0
;
short-circuiting the my_to_number() function is a neat trick Â… shouldnÂ’t we also make sure to prune as many rows as possible before applying that numeric checking predicate?
January 06, 2005 - 11:02 am UTC
not in general, no -- that rownum could/would cause "big temp area usage".
if <all the other predicates> filtered out a trivial amount of rows, perhaps -- but in "general", no -- i would not do that.
Some gaps in my understanding ...
Gabe, January 06, 2005 - 11:24 am UTC
<quote>if <all the other predicates> filtered out a trivial amount of rows, perhaps</quote>
I would've thought exactly the opposite ... if the inline predicates would've pruned the majority of rows, the rownum impact would've been relatively modest. Isn't it the rownum count the last thing to happen in the processing of the inline view?
January 06, 2005 - 11:48 am UTC
should have been more clear, sorry:
if the <all the other predicates> filtered in such a fashion that only a trivial amount of rows were output
if the predicates were very very selective.
I can see how my statement could easily be confused.
One more comment ...
Gabe, January 06, 2005 - 2:47 pm UTC
Ok Â… that makes sense Â… I was about to send a test case showing an order of magnitude difference in elapsed time.
I found that predicating "where my_to_number(tmpvalue) = 0" is faster than your approach as long as short-circuiting happens for less than 30% of the records. In other words, if the majority of rows (+70%) are in fact (candidate) numbers than just applying the function to the whole data set is faster. Of course, my_to_number() would have to be changed to do "to_number(p_num,'999999999.99')" in order to eliminate false positives for strings _appearing_ to be numbers in scientific notation i.e. '2e3'. The _cleaner_ the data the less appealing that decode/replace/translate/to_number implementation seems to be.
Integer value in varchar2 field
sonali, May 24, 2005 - 9:46 am UTC
I have following query -
select SET_REG_VALUE
from mwebsettings
where SET_REG_VALUE =3;
SET_REG_VALUE is varchar2(100)
In 9i on Unix machine it gives me error
ERROR:
ORA-01722: invalid number
In 9i on windows machine it works.. Why doesn't it work on unix server ?
I thought you can compare integer value in varchar2 field without quotes, or was I wrong ?
Thanks
May 24, 2005 - 1:00 pm UTC
because you don't have a number in that field and you are comparing it to a number and a string compared to a number is really
to_number(string) = number
it won't work on windows either if you insert into mwebsettings (set_reg_value) values ( 'hello world' );
compare STRINGS to STRINGS
numbers to numbers
and dates to dates and use no other compares.
first_load_ti
Joachim Seekopp, May 24, 2005 - 1:40 pm UTC
thank you.
by the way,why is first_load_time in v$sqlarea
a varchar2(57) and not a date ?
May 24, 2005 - 1:52 pm UTC
no idea, we'd have to ask the person that did it :)
String translation
Yiming, January 31, 2006 - 1:19 pm UTC
Hi, tom,
Since I knew this web site. I have learned a lot. Right now I have a difficulty to get the query that a little similar to Mr, Pal. The data in the column is x.x-x.x format. x= 1 to 10. I want to get the query which format likes x.x, x,x, x.x,... I have 10g. The example like:
table t
Column1
--------------
10.5-10.10
I want to get query like
Column1
-------------------------------
10.5,10.6,10.7,10.8,10.9,10.10
Thank you for your help
January 31, 2006 - 3:40 pm UTC
well, I would say it is not similar at all. (they wanted to turn a comma delimited string into a set, you want to reformat a single string into some other single string)
Looks like it would be easiest for you to write a plsql function that takes that string as input, parses it, formats the output string and returns it.
A solution
Michel Cadot, February 01, 2006 - 2:41 am UTC
Hi,
Here's one way to do it.
SQL> select * from t;
COL
--------------------
10.5-10.10
1 row selected.
SQL> with
2 a as (
3 select col,
4 to_number(substr(col,1,instr(col,'.')-1)) valb,
5 to_number(substr(col,instr(col,'.')+1,
6 instr(col,'-')-instr(col,'.')-1)) val1,
7 to_number(substr(col,instr(col,'.',1,2)+1)) val2
8 from t
9 )
10 select col,
11 max(substr(sys_connect_by_path(val,','),2)) val
12 from ( select col,
13 to_char(valb)||'.'||to_char(val1+rn) val,
14 row_number () over (partition by col order by rn) curr,
15 row_number () over (partition by col order by rn)-1 prev
16 from ( select col, valb, val1, rownum-1 rn
17 from dual, a
18 connect by level <= a.val2-a.val1+1 )
19 )
20 connect by prior curr = prev and prior col = col
21 start with curr = 1
22 group by col
23 order by col
24 /
COL VAL
-------------------- ---------------------------------------
10.5-10.10 10.5,10.6,10.7,10.8,10.9,10.10
1 row selected.
Regards
Michel
Further more complex
Yiming, February 01, 2006 - 5:08 pm UTC
Thanks tom and micheal for your help.
If the query more complicated like these two formats mixed together like x.x, x.x-x.x, x.x-x.x, x.x , then translate to x.x,x.x,x.x,x.x,x.x...
example:
col
---------------------
9.9, 8.1-8.3, 7.2-7.5, 2.6
I want like
col
----------------------
9.9,8.1,8.2,8.3,7.2,7.3,7.4,7.5,2.6
Thanks in advance
February 02, 2006 - 4:31 am UTC
I will reiterate that to me this looks like you want to write a piece of code, in a function, using plsql, that reformats this. It would be the most intuitive, understandable and maintainable approach.
We can do a neat query to do this in sql (simple variation on the above query), but I think a clean function in this case would be better.
yiming, February 02, 2006 - 1:11 pm UTC
Thanks Tom
I will try to write a function. I think this is a more complicated function.
yiming
Reformat
Yiming, February 06, 2006 - 10:56 am UTC
Hi, Tom,
With your clue, I build a function.
scott@TEST1> CREATE OR REPLACE FUNCTION REFORMAT( P_STMT IN VARCHAR2) RETURN VARCHAR2
2 AUTHID CURRENT_USER AS
3 TYPE C_REF_CUR IS REF CURSOR;
4 V_STR VARCHAR2(4000);
5 V_COLVAL VARCHAR2(4000);
6 C C_REF_CUR;
7 L NUMBER;
8 V_ROW NUMBER;
9 V_COL1 NUMBER;
10 V_COL2 NUMBER;
11 V_COL_GAP NUMBER;
12 BEGIN
13 OPEN C FOR P_STMT;
14 LOOP
15 FETCH C INTO V_COLVAL;
16 EXIT WHEN C %NOTFOUND;
17 V_ROW :=TO_NUMBER(SUBSTR(V_COLVAL, 1, INSTR(V_COLVAL, '.',1,1) - 1));
18 V_COL1 :=TO_NUMBER(SUBSTR(V_COLVAL,
19 INSTR(V_COLVAL,'.',1,1)+1,(INSTR(V_COLVAL, '-',1,1)) -
20 (INSTR(V_COLVAL,'.',1,1)+1) ));
21 V_COL2 :=TO_NUMBER(SUBSTR(V_COLVAL, INSTR(V_COLVAL,'.',1,2)+1));
22 V_COL_GAP := V_COL2 - V_COL1;
23 V_STR:= V_STR || ',' || TO_CHAR(V_ROW) || '.' || TO_CHAR(V_COL1);
24 FOR I IN 1..V_COL_GAP LOOP
25 V_COL1 := V_COL1 + 1;
26 V_STR:= V_STR || ',' || TO_CHAR(V_ROW) || '.' || TO_CHAR(V_COL1) ;
27 END LOOP;
28 END LOOP;
29 CLOSE C;
30 RETURN SUBSTR(V_STR,2);
31
32 END;
33 /
Function created.
scott@TEST1> CREATE TABLE T (ID NUMBER, COL VARCHAR2(200));
Table created.
scott@TEST1> DROP TABLE T;
Table dropped.
scott@TEST1>
scott@TEST1> CREATE TABLE T (ID NUMBER, COL VARCHAR2(200));
Table created.
scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(1, '10.1-10.5');
1 row created.
scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(2, '2.1-2.10');
1 row created.
scott@TEST1>
scott@TEST1> INSERT INTO T VALUES(3, '5.5-5.7');
1 row created.
scott@TEST1>
scott@TEST1> COMMIT;
Commit complete.
scott@TEST1> set line 5000
scott@TEST1> SELECT * FROM T;
ID COL
---------- ----------------------------
1 10.1-10.5
2 2.1-2.10
3 5.5-5.7
1 SELECT DISTINCT a.id,
2 reformat('SELECT col FROM t WHERE id = ' || '''' || a.id || ''''
3 || ' ORDER BY id') AS t1
4 FROM t a
5* ORDER BY ID
scott@TEST1> /
ID T1
---------- ------------------------------------------------------------------
1 10.1,10.2,10.3,10.4,10.5
2 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,2.10
3 5.5,5.6,5.7
thanks tom,
yiming
Sorry
Michel Cadot, February 08, 2006 - 2:56 am UTC
Hi Yiming, Tom,
I can't resist to a SQL puzzle more than a week. :)
SQL> select * from t;
ID COL
---------- ------------------------------
1 10.5-10.10
2 2.1-2.10
3 5.5-5.7
4 4.3
5 9.9, 8.1-8.3, 7.2-7.5, 2.6
5 rows selected.
SQL> col val format a50
SQL> with
2 a as (
3 select id, rn,
4 translate(substr(col, decode(rn,1,1,instr(col, ',', 1, rn-1)+1),
5 instr(col, ',', 1, rn)
6 -decode(rn,1,0,instr(col, ',', 1, rn-1))-1),
7 'a, ','a')
8 val0
9 from ( select id, col,
10 row_number() over (partition by id order by rownum) rn
11 from dual, (select id, col||',' col from t) t
12 connect by prior id = id
13 and prior dbms_random.value is not null
14 and level <=
15 length(t.col)-length(replace(t.col,',','')) )
16 ),
17 b as (
18 select id, rn rn0,
19 to_number(substr(val0,1,instr(val0,'.')-1)) valb,
20 to_number(substr(val0,instr(val0,'.')+1,
21 decode(instr(val0,'-'),0,length(val0)+1,
22 instr(val0,'-'))
23 -instr(val0,'.')-1)) val1,
24 to_number(substr(val0,
25 greatest(instr(val0,'.',1,2),instr(val0,'.',1,1))
26 +1)) val2
27 from a
28 )
29 select id, max(substr(sys_connect_by_path(val,','),2)) val
30 from ( select id,
31 to_char(valb)||'.'||to_char(val1+rn) val,
32 row_number () over (partition by id order by rn0, rn) curr,
33 row_number () over (partition by id order by rn0, rn)-1 prev
34 from ( select id, rn0, valb, val1,
35 row_number ()
36 over (partition by id, rn0 order by rownum) - 1 rn
37 from dual, b
38 connect by prior id = id and prior rn0 = rn0
39 and prior dbms_random.value is not null
40 and level <= b.val2-b.val1+1 )
41 )
42 connect by prior curr = prev and prior id = id
43 start with curr = 1
44 group by id
45 order by id
46 /
ID VAL
---------- -------------------------------------------------
1 10.5,10.6,10.7,10.8,10.9,10.10
2 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,2.10
3 5.5,5.6,5.7
4 4.3
5 9.9,8.1,8.2,8.3,7.2,7.3,7.4,7.5,2.6
5 rows selected.
Yes, it is not maintainable but it is fun.
Regards
Michel
Thanks
Yiming Li, March 02, 2006 - 3:01 pm UTC
Thanks, Michel, you are smart too.
yiming
Evaluate arithmetic stored in VARCHAR2
Ronald, August 07, 2006 - 7:28 am UTC
Hi Tom,
Could we evaluate an arithmetic expressions stored as string (varchar2) ?
That is the problem I faced today. I have formula (to calculate output needed by user)
that often changed (based on user experiment), so to be hardcoded in application is wrong thing, I think.
The idea, I create a table that store the formula so when user propose to change
they can do by self (using API I provide) without modifying the original code.
create table t ( expr varchar2(100) );
insert into t values ('(10-2*4.3)/2');
insert into t values ('13+(5.6-0.5*2.1)/1.2');
insert into t values ('6^2-3.1');
select * from t;
EXPR
--------------------
(10-2*4.3)/2-1
13+(5.6-0.5*2.1)/1.2
6^2-3.1
The result I expected from that table is arithmetic operation applied to
that value. In real condition I use some variables express by a letter (A..Z).
But here for simplified the question I remove that variable to all constant.
Result I am expected for the query is :
select evaluate(expr) from t;
EVALUATE(EXPR)
--------------------
0.7
16.7916666666667
32.9
Could you help me to write PL/SQL for the evalute function or you have another
better technique to solve the problem ?
The operator we used (separate by comma) is +,-,*,/,(,),^
Thanks,
Ronald
August 07, 2006 - 8:14 am UTC
Many Thanks Tom
Ronald, August 07, 2006 - 11:24 am UTC
Great !!! Is what I expected.
A reader, March 02, 2008 - 6:25 am UTC
How to generate a series for alphanumeric strings?
drop table t purge;
create table t (name varchar2(30));
insert into t values('001-010');
insert into t values('002-100');
insert into t values('1-10');
insert into t values('2-100');
insert into t values('01A-10A');
insert into t values('A01-A10');
insert into t values('B1-B50');
insert into t values('01-100');
insert into t values('v-z');
insert into t values('G-L');
for '001-010', the series will be 001, 002, 003, 004, 005, .. 010.
March 02, 2008 - 10:46 am UTC
you write code
or you use a sequence and forget about having anything resembling "gap free"
A reader, March 03, 2008 - 11:22 pm UTC
Yes, i am trying to right the code. but the scenario here is the user will enter the range and the series has to be generated. But the user may enter in any format say, number followed by characters or all characters or all numbers or number followed by a char followed by a number.But everything gets stored as a character string. can it be done in a sequence? if so, please tell me how?
March 04, 2008 - 7:17 am UTC
you will write code.
just write a stored procedure - there is nothing automagical here, you will write code.
how to separae digits and letters
Ahmed, April 01, 2008 - 7:27 am UTC
hi Tom,
How are you?
In our database we have an old field for book pages, e.g 300p , meaning that the book has 300 pages, and some fdata like: {300 pages}, an so on.
The new build, I implement the table as tow fileds to pages of the book, One for numbers , and the other for types. eg. field1 = 300, and field2 = p.
And the users enter some about 1 million book in the old format, and want from me to separate the data automatically.
can you help me please.
Ahmed.
April 01, 2008 - 9:38 am UTC
ops$tkyte%ORA10GR2> select x,
2 replace(translate(x,'0123456789','0'),'0','') not_digits,
3 replace( translate( x, replace(translate(x,'0123456789','0'),'0',''), 'x'), 'x', '' ) digits
4 from (select '300p' x from dual)
5 /
X NOT_ DIGI
---- ---- ----
300p p 300
1 row selected.
the digits function can be simplified if you know the domain of alpha characters you will hit - I did not know any character beyond "p" so I punted and used the data itself to find the characters involved.
Validating strings using Regular Expressions
John Gasch, April 01, 2008 - 3:50 pm UTC
Getting back to the root topic of this thread, I'm rather perplexed that nobody has suggested doing numeric string validation using regular expressions.
A solution to the original poster's problem would be:
select *
from t
where tmptype = 'N'
and not regexp_like(tmpvalue,
'^(+|-)?((\d+\.\d*$)|(\d+$)|(\d*.\d+$))' );
The regexp would match variations of numbers, such as:
0
0.
0.0
.0
each optionally preceded by one + or - sign character, with no embedded blanks or any other character. So the query will return all rows labeled 'N' that have a value that doesn't look like a number.
I find regular expressions to be an extremely powerful tool in my toolbox. They are documented here:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm Regular expressions could potentially be applicable to a number of the other problems encapsulated in this discussion thread.
April 01, 2008 - 9:11 pm UTC
, version 8.1.7
see the original subject - from years and years and many versions ago.
I myself do not like regular expressions, really. If there was a single definition of them - maybe - but there are way too many variants of them.
REGEXP
John Gasch, April 01, 2008 - 10:33 pm UTC
Fair enough. They can be cryptic. Hopefully by now their implementation has matured, at least in Oracle's built-in REGEXP functions.
When I started writing this review, your reply to my previous post included the observation that I hadn't considered all forms of numeric literals. (It appears now that you rescinded that remark, even though it was valid.) So here's a version that's more robust just for completeness...
select *
from t
where tmptype = 'N'
and not regexp_like(tmpvalue,
'^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$' );
(I admit that I neglected a few backslashes in the prior version.)
REGEXP
John Gasch, April 01, 2008 - 11:09 pm UTC
Oops, I needed a trailing 'i' in the REGEXP_LIKE arg list to make it case insensitive so it picks up numbers like '1e0'.
select *
from t
where tmptype = 'N'
and not regexp_like(tmpvalue,
'^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$','i');Sorry. I think I'm finished now.
following up
lou, July 30, 2013 - 7:35 pm UTC
Tom,
how can I the pipelined above to do the following:
TABLE STRUCTURE
ID, CATEGORY ..... 6 columns ....
1 STUDENT, CLASS, BUILDING,PRINCIPAL
ID COLUMN_VALUE column3 column4 ...col6
---------- -------------------------
1 STUDENT
1 CLASS
1 BUILDING
1 PRINCIPAL