OraFAQ Forum: SQL & PL/SQL » Find the max value of a filed

Home » SQL & PL/SQL » SQL & PL/SQL » Find the max value of a filed

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find the max value of a filed [message #7085]

Tue, 20 May 2003 10:34

Lance Pris
Messages: 40
Registered: January 2002

Member

I need to find the max value of a specific field.

My situation is as follows I have 2 fields that need to be concatenated and merged into a single field.

For example field A is a char(255) and field B is a char(255) and they need to go into field C that is a char(400). I would like to be able to determine the max size of data in either field to determine weather or not the char(400) is the proper size.

Thanks Lance

Report message to a moderator

Re: Find the max value of a filed [message #7086 is a reply to message #7085]

Tue, 20 May 2003 11:12

Lance Pris
Messages: 40
Registered: January 2002

Member

this is the code I am tyring but getting invalid column error on the len

SELECT Max(Len(PC_SOLUTION_ID)) AS max_len
FROM pt_solution
ORDER BY Max(Len(PC_SOLUTION_ID)) DESC;

Report message to a moderator

Re: Find the max value of a filed [message #7087 is a reply to message #7086]

Tue, 20 May 2003 11:17

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Use LENGTH instead of len.

SQL> SELECT LENGTH('four') FROM DUAL;
  
LENGTH('FOUR')
--------------
             4
  
SQL>

Report message to a moderator

Re: Find the max value of a filed [message #7100 is a reply to message #7086]

Wed, 21 May 2003 02:02

Martin Chadderton
Messages: 35
Registered: May 2003

Member

Also, in that situation, you don't need the ORDER BY clause, MAX will only return a single row for a given group (the whole resultset in your case)

Regards

Report message to a moderator

Re: Find the max value of a filed [message #7105 is a reply to message #7086]

Wed, 21 May 2003 06:50

Naveen
Messages: 75
Registered: June 2001

Member

Hi,

I am not sure you can find it that way. Char datatype fills the remaining space with blank characters. so even if you insert a field having only four characters it will return the length as 400 as you have declared it like that. See the code. If i got the your requirement wrong, kindly let me know.

SVRMGR> create table test_char(no number(5), name char(100));
Statement processed.
SVRMGR> insert into test_char values(1,'naveen');
1 row processed.
SVRMGR> insert into test_char values(2,'pramod');
1 row processed.
SVRMGR> insert into test_char values(3,'geetha');
1 row processed.
SVRMGR> insert into test_char values(4,'ashutosh');
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> select length(name) from test_char;
LENGTH(NAM
----------
100
100
100
100
4 rows selected.

Hope the following code helps.

SVRMGR> select length(rtrim(name)) from test_char;
LENGTH(RTR
----------
6
6
6
8
4 rows selected.

Regards---Naveen.

Report message to a moderator

Previous Topic:	ORA-01861: literal does not match format string -- Error
Next Topic:	Read from an Excel file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:13:01 CDT 2024