Home » SQL & PL/SQL » SQL & PL/SQL » to_number in view (10.2.0.1, Windows 2003)
| to_number in view [message #330646] |
Mon, 30 June 2008 13:04  |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Hi,
I am trying to write a view as below:
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,to_number(qty)
from text
where code in (2,3)
order by itm,sile,qty;
In the above view, the qty value in table 'text' is varchar2 and in the text_vw, eqty value should be converted from varchar to numeric.
Please help, with your suggestions. Thank you
|
|
|
|
|
|
|
|
| Re: to_number in view [message #330650 is a reply to message #330646] |
Mon, 30 June 2008 13:10  |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks for the input. My question is, how can I convert the eqty from string to numeric? The column definition for qty in TEXT is varchar. But in the view, we need it in number.
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,to_number(qty)
from text
where code in (2,3);
Thanks again.
|
|
|
|
|
|
| Re: to_number in view [message #330653 is a reply to message #330646] |
Mon, 30 June 2008 13:18 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
But when I insert a record in TEXT with code=2 the record gets inserted.
insert into text (itm,sile,qty,code) values ('234',4,'arw4',3);
But when I select the view text_vw, it gives me the below error:
select * From text_vw;
ORA-01722:invalid number
Please help.Thanks
|
|
|
|
|
|
| Re: to_number in view [message #330657 is a reply to message #330646] |
Mon, 30 June 2008 13:24 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Sorry about that.
I tried a different insert statement:
insert into text (itm,sile,qty,code) values ('123',2,'820',3);
It gets inserted into text but when I do a select of text_vw, I get the same error:
ORA-01722:invalid number
|
|
|
|
|
|
|
|
|
|
|
|
| Re: to_number in view [message #330666 is a reply to message #330663] |
Mon, 30 June 2008 13:52 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
|
As long as you don't change the datatype of your text-table column, AND you keep inserting records directly into the table, you still run the risk of getting an invalid-number exception in your view.
|
|
|
|
| Re: to_number in view [message #334195 is a reply to message #330646] |
Tue, 15 July 2008 15:16 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Hi all,
For the same view below,
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,to_number(qty)
from text
where code in (2,3);
The application team wants to convert the qty in the view to (-) ve value only when code=3 in table text.
So, for the rows with code=3 and qty=8 in table text, the view should have eqty as -8.
Is it possible to do this using to_number()?
I think I can use DECODE function for this, but since I am using to_number() also, I am not sure. Please help if you have suggestions. THanks.
|
|
|
|
| Re: to_number in view [message #334196 is a reply to message #334195] |
Tue, 15 July 2008 15:29 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| Quote: | think I can use DECODE function for this
|
and you would be correct. Alternatively you may consider the CASE expression.
|
|
|
|
| Re: to_number in view [message #334201 is a reply to message #330646] |
Tue, 15 July 2008 15:49 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks, Pablolee. I tried like below
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,decode(to_number((qty),code,3,sign(qty)))
from text
where code in (2,3);
Here, the qty should be converted to number and for the code=3 in table text ,the view should have qty with '-' number.
I am sorry if it is confusing, I am a new bie on the development side and I did look in google but not able to get it correct.
Any help is greatly appreciated since I will have to get this done shortly. Thank you very much.
|
|
|
|
|
|
|
|
| Re: to_number in view [message #334205 is a reply to message #330646] |
Tue, 15 July 2008 15:58 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,to_number(qty, CASE when code=3 then -qty else qty end) qty
from text
where code in (2,3);
I tried using CASE expression. Getting error message as
'ORA-00932:inconsistent datatypes:expected NUMBER got CHAR'
Any suggestions, please? Thanks
|
|
|
|
| Re: to_number in view [message #334206 is a reply to message #334205] |
Tue, 15 July 2008 16:34 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi,
your to_number code has incorrect arguments. to_number accepts a string formatted number and/or format string. also you've got the wrong syntax of case statement. look at this sample:
select to_number(case 3 when 3 then -3 else 3 end) case
from dual
|
|
|
|
| Re: to_number in view [message #334267 is a reply to message #334206] |
Wed, 16 July 2008 01:02 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Case can be used as sant_new did, as well as you do:
SQL> select empno
2 , case when mod(empno, 2) = 1 then empno else -empno end
3 from emp;
EMPNO CASEWHENMOD(EMPNO,2)=1THENEMPNOELSE-EMPNOEND
---------- --------------------------------------------
7369 7369
7499 7499
7521 7521
7566 -7566
7654 -7654
7698 -7698
7782 -7782
7788 -7788
7839 7839
7844 -7844
7876 -7876
7900 -7900
7902 -7902
7934 -7934
14 rows selected. |
|
|
|
| Re: to_number in view [message #334268 is a reply to message #334204] |
Wed, 16 July 2008 01:04 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Frank wrote on Tue, 15 July 2008 22:52 | Check your parentheses. What arguments does decode have in your example?
What arguments does to_number have?
|
@sant_new: sit back. Think. Keep your hands of the keyboard and take your time to look at your code.
|
|
|
|
|
|
| Re: to_number in view [message #357794 is a reply to message #330646] |
Thu, 06 November 2008 11:20 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Hi all,
I've created the below view and it works fine.
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,to_number(qty)
from text
where code in (2,3);
Currently, text.eqty is of varchar2(10) and the view converts qty to number. But, we would like to have the qty in text_vw as number(10,2). Is it possible to mention the precision and scale for qty in the view??
Thank you for any help.
|
|
|
|
|
|
| Re: to_number in view [message #357815 is a reply to message #330646] |
Thu, 06 November 2008 14:14 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks Michel.
select * from text where code in (2,3);
itm sile qty code
123 2 981 2
I tried this:
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,
case when code = 2
then -1 * cast (qty as number(10,2))
else cast (qty as number(10,2))
end qty
from text
where code in (2,3);
select * from text_vw;
itm sile eqty
123 2 981
The eqty in view still not stored in precision and scale.
I'll appreciate any of your help. Thanks
|
|
|
|
| Re: to_number in view [message #357816 is a reply to message #357815] |
Thu, 06 November 2008 14:20 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
@sant_new,
I believe the following example proves Michel's point:
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 7 00:09:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set feedback on;
SQL> CREATE OR REPLACE VIEW test_view
2 AS
3 SELECT CAST (col_1 AS NUMBER (10, 2)) col_1,
4 CAST (col_2 AS VARCHAR2 (20)) col_2
5 FROM (SELECT 10.23 col_1, 'Check This Out' col_2
6 FROM DUAL);
View created.
SQL> desc test_view;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL_1 NUMBER(10,2)
COL_2 VARCHAR2(20)
Are you concerned about displaying the number with decimal points? If so use TO_CHAR Function
Regards,
Jo
|
|
|
|
| Re: to_number in view [message #358294 is a reply to message #330646] |
Mon, 10 November 2008 10:16 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks Jo. But the application team said they interpreted it wrong to me. They say when they insert values through the application to the test_vw, it shows eqty as float whereas the qty in test table is varchar2(10). So they want the test_vw to show as number(10) instead of float.
desc test;
Name Type
ITM VARCHAR2(10)
SILE NUMBER(2,0)
QTY VARCHAR2(10)
CODE NUMBER(2,0)
desc test_vw;
Name Type
ITM VARCHAR2(10)
SILE NUMBER(2,0)
EQTY NUMBER
We want the test_vw to describe like below:
desc test_vw;
Name Type
ITM VARCHAR2(10)
SILE NUMBER(2,0)
EQTY NUMBER(10,0)[/CODE]
So the values for eqty in test_vw will be numeric like in qty.test table.
I tried the below view, the view still doesnt describe the eqty as number(10,0)
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,
case when code = 2
then -1 * cast (qty as number(10,2))
else cast (qty as number(10,2))
end qty
from text
where code in (2,3);
I would appreciate any help. Thank you very much.
|
|
|
|
|
|
| Re: to_number in view [message #358296 is a reply to message #330646] |
Mon, 10 November 2008 10:53 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
But when I re-create the view as below, when I describe the view I dont see eqty number(10,2). I just see eqty number. Any suggestions?
create or replace force view text_vw (itm,
sile,
eqty,
)
as
select itm,sile,
case when code = 2
then -1 * cast (qty as number(10,2))
else cast (qty as number(10,2))
end qty
from text
where code in (2,3);
desc test_vw;
Name Type
ITM VARCHAR2(10)
SILE NUMBER(2,0)
EQTY NUMBER
But we would want the eqty to be number(10,0) just like sile(2,0).
Thank you for any help.
|
|
|
|
|
|
|
|
| Re: to_number in view [message #358302 is a reply to message #330646] |
Mon, 10 November 2008 11:13 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Here is the code from sql..
SQL> create or replace force view text_vw (itm,
2 sile,
3 eqty,
4 )
5 AS
6 SELECT itm, sile,
7 CASE
8 WHEN code = 2
9 THEN -1 * CAST (qty AS NUMBER(10,0))
10 ELSE CAST (qty AS NUMBER(10,0))
11 END qty
12 FROM text
13 WHERE code IN (2, 3);
View created.
SQL> desc text_vw;
Name Null? Type
----------------------------------------- -------- ----------------------------
ITM VARCHAR2(10)
SILE NUMBER(2,0)
EQTY NUMBER
SQL> desc text;
Name Null? Type
----------------------------------------- -------- ----------------------------
ITM VARCHAR2(10)
SILE NUMBER(2,0)
QTY VARCHAR2(10)
CODE NUMBER(2,0)
Thank you.
|
|
|
|
|
|
|
|
|
|
| Re: to_number in view [message #358318 is a reply to message #330646] |
Mon, 10 November 2008 12:23 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Thanks for the example.
I tried like below:
SQL> create or replace force view text_vw (itm,
2 sile,
3 eqty,
4 )
5 AS
6 SELECT itm, sile,
7 CAST(CASE
8 WHEN code = 2
9 THEN -1 * qty
10 ELSE qty
11 END AS NUMBER(10,0))
12 FROM text
13 WHERE code IN (2, 3);
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
Any suggestions?? Thank you.
|
|
|
|
| Re: to_number in view [message #358319 is a reply to message #330646] |
Mon, 10 November 2008 12:27 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Changed to_number and it worked. THanks everyone.
SQL> create or replace force view text_vw (itm,
2 sile,
3 eqty,
4 )
5 AS
6 SELECT itm, sile,
7 CAST(CASE
8 WHEN code = 2
9 THEN -1 * to_number( qty )
10 ELSE to_number (qty)
11 END AS NUMBER(10,0))
12 FROM text
13 WHERE code IN (2, 3); |
|
|
|
| Re: to_number in view [message #358322 is a reply to message #358318] |
Mon, 10 November 2008 12:30 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
@sant_new,
Your CASE statement consists of both datatypes VARCHAR2 and an (implicitly converted) number datatype. Make both the datatype identical by using either TO_CHAR Function or TO_NUMBER Function.
Hope this helps,
Regards,
Jo
(*** Added: Didn't see OP Figured out the problem himself... )
[Updated on: Mon, 10 November 2008 12:36] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Dec 05 20:24:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
