Home » SQL & PL/SQL » SQL & PL/SQL » SELECT qry
SELECT qry [message #220503] Wed, 21 February 2007 00:24 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Dear pals,

I have a table as follows

create table mst_org
(
org_id number,
org_name varchar2(20),
org_desc varchar2(2000)
)

insert into mst_org values(1,'Oracle','Oracle supply a range of software to manage, share and protect data and information.')
insert into mst_org values(2,'SQL Server','Visit this portal for all the latest news and articles to help you evaluate Microsoft SQL Server 2005 for your data management and analysis needs')
insert into mst_org values(3,'Sybase','Sybase Corp')

I need to display the output as follows. i.e suppose if my org_desc column value length greater than 30 then is should display three ... in the output as follows.
I have tried this using cursor i.e i processes row by row and displayed as follows. But is there a way which can be done using a single SELECT stmt with CASE stmt.


select * from mst_org

1 Oracle Oracle supply a range of so...
2 SQL Server Visit this portal for all t...
3 Sybase Sybase Corp





Any Suggestions is appreciated.

Regards,
franky


Re: SELECT qry [message #220508 is a reply to message #220503] Wed, 21 February 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
After 150 posts, this cannot come as a surprise:
At least try it yourself and post your query/outcome.
Re: SELECT qry [message #220570 is a reply to message #220508] Wed, 21 February 2007 05:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a hint, try using CASE on the LENGTH of the string.
Re: SELECT qry [message #221144 is a reply to message #220570] Sat, 24 February 2007 06:29 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
This is all ok but where is the answer.
Re: SELECT qry [message #221302 is a reply to message #221144] Mon, 26 February 2007 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @Frank said:
After 150 posts, this cannot come as a surprise:
At least try it yourself and post your query/outcome. 

You're not a newbie - you know how it works. we're not here to do your job for you. Show us what you've done and we'll make it work, but I'm not sensing too much interest in writing it all for you.
Re: SELECT qry [message #221657 is a reply to message #220503] Tue, 27 February 2007 23:29 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

select org_id , org_name,
decode(sign(length(org_desc) - 30),1,substr(org_desc,1,30)||'...',org_desc) org_desc from mst_org

Re: SELECT qry [message #221717 is a reply to message #221657] Wed, 28 February 2007 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rameshuddaraju wrote on Wed, 28 February 2007 06:29
select org_id , org_name,
decode(sign(length(org_desc) - 30),1,substr(org_desc,1,30)||'...',org_desc) org_desc from mst_org



Did you even bother to read the other replies?
Re: SELECT qry [message #221728 is a reply to message #220503] Wed, 28 February 2007 03:38 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Did you even bother to read the other replies?

oops!

I've read the messages. There are many visitors who go through these queries and I feel that the replies about the seniority is irrelevant here.

If I have a solution, I will just provide it to the query and so other visitors who read this also gets beneficial here.

Let's make this forum a better place to learn and grow.

with rgds
Ramesh Uddaraju
Re: SELECT qry [message #221735 is a reply to message #221728] Wed, 28 February 2007 04:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
Let's make this forum a better place to learn and grow.

And exactly how did you help the cause?
You never gave a chance for the OP to throw some effort!.
Just my 2 cents Smile

Re: SELECT qry [message #221763 is a reply to message #221735] Wed, 28 February 2007 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree. People learn more by trying, failing and having their mistakes explained to them than they do by simply having a solution handed to them.
Re: SELECT qry [message #221765 is a reply to message #221728] Wed, 28 February 2007 06:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rameshuddaraju wrote on Wed, 28 February 2007 10:38
There are many visitors who go through these queries and I feel that the replies about the seniority is irrelevant here.

There are no replies about seniority here. I only told him (like he was told many times before) that he should try it himself before asking here.
The rest is well worded by Mahesh and JRowbottom

[Edit: added credits to JR]

[Updated on: Wed, 28 February 2007 06:36] by Moderator

Report message to a moderator

Re: SELECT qry [message #222413 is a reply to message #221765] Sun, 04 March 2007 06:26 Go to previous messageGo to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Soory guys, by the time i got u solution, i got the qry on my own.

select org_id,org_name,case
when length(org_desc)>30
substr(org_desc,1,30)||'...'
else
org_desc
end as "org_desc"
from mst_org;


Any how thanks for the help.
Regards,
frany
Re: SELECT qry [message #222419 is a reply to message #222413] Sun, 04 March 2007 09:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
frank.svs wrote on Sun, 04 March 2007 13:26
Soory guys, by the time i got u solution, i got the qry on my own.


...which was exactly our point! Next time, first try it yourself, and if you fail, show us how far you got. Copy-paste your query and tell us why it is not what you want.
I am sure you will be helped very fast then.
Previous Topic: sql query to convert columns into rows
Next Topic: How to "unload/save" a complete table content into a file ?
Goto Forum:
  


Current Time: Wed Dec 07 04:39:56 CST 2016

Total time taken to generate the page: 0.10085 seconds