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 Go to next message
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 #330647 is a reply to message #330646] Mon, 30 June 2008 13:06 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
What is the question?
Re: to_number in view [message #330648 is a reply to message #330646] Mon, 30 June 2008 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"order by" is useless in a view definition.
If user want to order the result, it uses "order by" when querying the view.

Regards
Michel
Re: to_number in view [message #330650 is a reply to message #330646] Mon, 30 June 2008 13:10 Go to previous messageGo to next message
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 #330651 is a reply to message #330650] Mon, 30 June 2008 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_NUMBER is a good way.
Have you any problem?

Regards
Michel
Re: to_number in view [message #330653 is a reply to message #330646] Mon, 30 June 2008 13:18 Go to previous messageGo to next message
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 #330655 is a reply to message #330653] Mon, 30 June 2008 13:20 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And how would you convert arw4 to a number?
Re: to_number in view [message #330657 is a reply to message #330646] Mon, 30 June 2008 13:24 Go to previous messageGo to next message
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 #330658 is a reply to message #330646] Mon, 30 June 2008 13:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I get the same error
If you do not or did not DELETE the rows with characters in qty,
you will continue to get errors

SELECT * FROM TEXT;
Re: to_number in view [message #330660 is a reply to message #330657] Mon, 30 June 2008 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put a test case that we can reproduce.
Test case includes create table and insert statements.
You also have to tell what are your NLS settings, query v$nls_parameters.

Regards
Michel
Re: to_number in view [message #330662 is a reply to message #330658] Mon, 30 June 2008 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Mon, 30 June 2008 20:28
> If you do not or did not DELETE the rows with characters in qty,
you will continue to get errors

Good remark, I can't think it was possible but yes it is. Wink

Regards
Michel

Re: to_number in view [message #330663 is a reply to message #330646] Mon, 30 June 2008 13:32 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
I deleted those records and it works now.Thanks, Anacedent.
Re: to_number in view [message #330666 is a reply to message #330663] Mon, 30 June 2008 13:52 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
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 #334202 is a reply to message #330646] Tue, 15 July 2008 15:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: to_number in view [message #334204 is a reply to message #334201] Tue, 15 July 2008 15:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check your parentheses. What arguments does decode have in your example?
What arguments does to_number have?
Re: to_number in view [message #334205 is a reply to message #330646] Tue, 15 July 2008 15:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #334629 is a reply to message #334267] Thu, 17 July 2008 07:57 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
thanks Frank!
Re: to_number in view [message #357794 is a reply to message #330646] Thu, 06 November 2008 11:20 Go to previous messageGo to next message
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 #357797 is a reply to message #357794] Thu, 06 November 2008 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CAST.

Regards
Michel
Re: to_number in view [message #357815 is a reply to message #330646] Thu, 06 November 2008 14:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #358295 is a reply to message #358294] Mon, 10 November 2008 10:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
WYSIWYG (What You See is What You Get).

Regards

Raj
Re: to_number in view [message #358296 is a reply to message #330646] Mon, 10 November 2008 10:53 Go to previous messageGo to next message
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 #358298 is a reply to message #330646] Mon, 10 November 2008 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
try casting the case statement as a whole.
Re: to_number in view [message #358299 is a reply to message #358296] Mon, 10 November 2008 11:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why don't you do a copy & paste from your sqlplus session? The code you have posted will not work.

Regards

Raj
Re: to_number in view [message #358302 is a reply to message #330646] Mon, 10 November 2008 11:13 Go to previous messageGo to next message
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 #358308 is a reply to message #358302] Mon, 10 November 2008 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CAST the CASE not the columns.

Regards
Michel
Re: to_number in view [message #358314 is a reply to message #330646] Mon, 10 November 2008 12:08 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Michael, I'm not sure how to cast the case. Can you give me an example, please?

Thanks.
Re: to_number in view [message #358315 is a reply to message #358314] Mon, 10 November 2008 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace view v as 
  2  select cast(case dummy when 'x' then 1 else 0 end as number(10,2)) col from dual;

View created.

SQL> desc v
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       NUMBER(10,2)

Regards
Michel
Re: to_number in view [message #358318 is a reply to message #330646] Mon, 10 November 2008 12:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: CREATE OR REPLACE FUNCTION
Next Topic: copy view to table with conditions
Goto Forum:
  


Current Time: Sun Dec 11 06:05:37 CST 2016

Total time taken to generate the page: 0.05030 seconds