Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production, Centos 5)
SQL Query [message #626836] Tue, 04 November 2014 01:51 Go to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Hello,


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc news
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NEWS_ID                                   NOT NULL NUMBER(14)
 NEWS_DATE                                          TIMESTAMP(0)
 SL_ID                                              NUMBER(2)
 HEADING                                            VARCHAR2(3120)
 DESCRIPTION                                        VARCHAR2(3900)



I am working with news slider and I intend to select 5 news randomnly from:
1) Last posted 15 news_ids
2) 50 words will be selected from description
3) If in these 50 words "<table" or "<img" or "<br>" exist then replace the first backet of the tag like "<!"
4) This process will be repeated auto after every 3 hours

Please advise

Thanks in aniticpation
Re: SQL Query [message #626840 is a reply to message #626836] Tue, 04 November 2014 01:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Please provide the create and insert statements as sample data. You need to provide a test case.

Raakh wrote on Tue, 04 November 2014 13:21

I am working with news slider and I intend to select 5 news randomnly from:
1) Last posted 15 news_ids
2) 50 words will be selected from description
3) If in these 50 words "<table" or "<img" or "<br>" exist then replace the first backet of the tag like "<!"
4) This process will be repeated auto after every 3 hours


1. For random Rows, use ROWNUM <=15.
2. Use SUBSTR.
3. REGEXP_REPLACE perhaps, but solution depends on your data.
4. DBMS_SCHEDULER.

[Updated on: Tue, 04 November 2014 01:59]

Report message to a moderator

Re: SQL Query [message #626842 is a reply to message #626836] Tue, 04 November 2014 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post a test case: CREATE TABLE and INSERT statements so we can work with them.
Also post an example of result you want from the data you provide.


Re: SQL Query [message #626860 is a reply to message #626840] Tue, 04 November 2014 03:53 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Quote:
1. For random Rows, use ROWNUM <=15.


OP says he needs last posted 15 news_id

Quote:
I am working with news slider and I intend to select 5 news randomnly from:
1) Last posted 15 news_ids


I guess ROWNUM should be applied after we select sorted news_id (15 last posted news_id). But question to OP is how news_id are sorted, is it just the increasing sequence value.
Re: SQL Query [message #626861 is a reply to message #626860] Tue, 04 November 2014 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
OP says he needs last posted 15 news_id


As far I understand the point 1, he needs 5 random rows among the 15 newest ones.

Re: SQL Query [message #626862 is a reply to message #626840] Tue, 04 November 2014 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
4. DBMS_SCHEDULER.


Or DBMS_JOB.

(Added the wiki link in quote.)
Re: SQL Query [message #626863 is a reply to message #626861] Tue, 04 November 2014 03:58 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Exactly!
Re: SQL Query [message #626869 is a reply to message #626861] Tue, 04 November 2014 04:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 04 November 2014 15:26

Quote:
OP says he needs last posted 15 news_id


As far I understand the point 1, he needs 5 random rows among the 15 newest ones.



My understanding is also the same. The latest 15 news_id could be top 15 rows after sorting them in descending order. And, then the 5 random rows among them.

@OP, but, why 5 random rows out of the 15 selected, better select the top 5 rows at the first place.
Re: SQL Query [message #626998 is a reply to message #626840] Thu, 06 November 2014 07:10 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Hello,

Sorry for the late reply as I was travelling

Quote:
2. Use SUBSTR.

Its finding and displaying subStrings whereas I am trying to display words

Quote:
3. REGEXP_REPLACE perhaps, but solution depends on your data.

I am trying to set it in my Java code

Quote:
4. DBMS_SCHEDULER.

Good advice. I am checking some example

Thanks & best regards
Re: SQL Query [message #627000 is a reply to message #626998] Thu, 06 November 2014 07:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Your topic title is SQL QUERY. So, point 1,2 and 3 can be done with plain sql. You are yet to post the test case.
Re: SQL Query [message #627004 is a reply to message #627000] Thu, 06 November 2014 08:11 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
create table news(
news_id number(14) primary key,
news_Date timestamp(0) default sysdate,
heading varchar2(32),
description varchar2(320));

insert into news(heading, description) values('News heading','Description of the news will goes here. This is dynamic text that will be inserted');

the description may be different every time but I want to select say 5 first words e.g. "Description of the news will"

Thanks again
Re: SQL Query [message #627005 is a reply to message #627004] Thu, 06 November 2014 08:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
>First 5 words

Substr is capable of doing that.
Re: SQL Query [message #627008 is a reply to message #627005] Thu, 06 November 2014 08:26 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Can you please give me an example?

Thanks again
Re: SQL Query [message #627012 is a reply to message #627008] Thu, 06 November 2014 08:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I will be more than happy to assist you, but you need to first show what have you tried so far. Google is your friend. Oracle documentation should be the first place, and then there are numerous examples out there on the web. And, hey, why not search this forum itself Smile
Re: SQL Query [message #627014 is a reply to message #627005] Thu, 06 November 2014 09:12 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Thu, 06 November 2014 14:15
>First 5 words

Substr is capable of doing that.


substr and instr together are capable of doing that.
Re: SQL Query [message #627017 is a reply to message #627014] Thu, 06 November 2014 09:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ah, yes of course. I was more stressing over the test case Smile
Re: SQL Query [message #627042 is a reply to message #627012] Thu, 06 November 2014 14:11 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
can anybody please provide any link about the topic in this forum?

Thanks again
Re: SQL Query [message #627044 is a reply to message #627042] Thu, 06 November 2014 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/t/88153/0/
http://www.orafaq.com/wiki/Test_case
http://www.orafaq.com/forum/t/174502/102589/

Re: SQL Query [message #627046 is a reply to message #627044] Thu, 06 November 2014 14:42 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Thanks for sending links of forum help. I presently getting words by using Java split() method but was looking to optimize performance if this is possible through oracle SQL but disappointed.

I searched a lot on google but was unable to find how to split words in sql by using substr and instr.

anyways still anxious to have any help in this regards
Re: SQL Query [message #627047 is a reply to message #627046] Thu, 06 November 2014 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, you are unable, but what did you try?
The following very recent topic wiil give you a way and an example: split clob into rows
Re: SQL Query [message #627048 is a reply to message #627047] Thu, 06 November 2014 15:03 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Thanks Michel Cadot for your provided link. I studied but not get any idea due to limited knowledge about oracle.

I am giving up at this point and realized oracle is very difficult to understand. I am feeling comfortable with Java split() function

Thanks all who have given me their precious time

Best regards
Re: SQL Query [message #627079 is a reply to message #627048] Fri, 07 November 2014 02:59 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can use instr to search for spaces. Use that to find the position of the fourth space. then use that as a parameter to substr.
It's not complicated.
Re: SQL Query [message #627115 is a reply to message #627079] Fri, 07 November 2014 06:23 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Hello,

public String singledOutNews(){		
	
		String news="";

		try{
			if ((cnn==null)||cnn.isClosed()){
			cnn=DB.getOracleConnection();
			}

		String query = 	" select a.news_id,to_char(news_date,'Month day, yyyy'),heading,"+
				" substr(description,1,370),country from news a, news_location b "+
				" where a.news_id=b.news_id and rownum<5"+
				" order by news_id desc";

		PreparedStatement ps=cnn.prepareStatement(query);		
		rs=ps.executeQuery();
		
			while(rs.next()){
				String newsId	=rs.getInt(1);
				String newsDate	=rs.getInt(2);
				String heading	=rs.getString(3);
				String desc	=rs.getString(4);
				String country	=rs.getString(5);

				if(desc.equals("") || desc==null){
				desc="";
				}else{
				desc=desc.replaceAll("<br>","&nbsp;");			
				
				int index=desc.lastIndexOf(" ");
				String descByDroppingLastWord = desc.substring(0,index));

			news+= "<a href=\"/servlet/displayNews1?newsID="+newsId+"\">"+
				"<font size=3 color=olive face='times new roman'><b>"+
				heading+"</b</font></a><br>"+
				"<font color=teal size=1 face=verdana><b>"+rs.getString(2)+"</b></font>"+
				"<font color=maroon size=1 face='verdana'> &nbsp;&nbsp;&nbsp; [<b>"+
				country+"</b>]  </b></font><br>"+descByDroppingLastWord+
				"<a href=/servlet/displayNews?newsID="+news_id+"\">&nbsp;&nbsp; Details.....</a><br><br>";
		}	
		ps.close();		
		rs.close();
		cnn.close();		

		}catch(Exception e){
		errorMsg("ERROR: IndexDAO.displayNewsTicker()", e);
		}
	
	return news;
	}


This is what I did with java code but still facing two problems:
1) To select rownum<=5 from out of last 15 rows
2) Repeating this query after every 3 hours

Best regards
Re: SQL Query [message #627120 is a reply to message #627115] Fri, 07 November 2014 06:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Raakh wrote on Fri, 07 November 2014 17:53

		String query = 	" select a.news_id,to_char(news_date,'Month day, yyyy'),heading,"+
				" substr(description,1,370),country from news a, news_location b "+
				" where a.news_id=b.news_id and rownum<5"+
				" order by news_id desc";


That query will not return correct result set. ROWNUM is applied before ORDER BY and thus, you just get 5 random rows. You need to first sort the rows and then use ROWNUM in the subquery. In 12c, you can simply use TOP-n method using FETCH FIRST 5 ROWS ONLY. However, since you are on 11g, you could do it the way I explained.

As you initially said,

Quote:

I intend to select 5 news randomnly from:
1) Last posted 15 news_ids


I guess you need something like,

select * from(
select <column_list> from table
   order by news_id desc
) where rownum <=15


Now, can you explain why do you again want 5 random rows from the above 15 rows? Why not select only 5 rows at first place.

[Updated on: Fri, 07 November 2014 06:43]

Report message to a moderator

Re: SQL Query [message #627122 is a reply to message #627120] Fri, 07 November 2014 06:56 Go to previous messageGo to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Thanks for replying

Daily 15 news are inserted in db. I intend to fetch data of 5 news randomnly to display in shape of 5 slides in websites. The query will run after each 3-4 hours to update these news slides/ticker to seem change in news website.

The following that you posted is selecting only 5 or 15 news in desc order not different 5 news from 15 everytime
select * from(
select news_id from news
   order by news_id
) where rownum <=5;


Thanks again
Re: SQL Query [message #627123 is a reply to message #627122] Fri, 07 November 2014 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need to randomize the order after getting the 15.
SELECT * 
FROM (select * 
      from(select news_id 
           from news
           ORDER by news_id DESC
          ) 
      where rownum <=15
      ORDER BY dbms_random.value)
WHERE ROWNUM <= 5;

[Updated on: Fri, 07 November 2014 07:49]

Report message to a moderator

Re: SQL Query [message #627124 is a reply to message #627123] Fri, 07 November 2014 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
As for running the query every three hours, if you're coding this in java then that's a java issue, not a DB one.
Re: SQL Query [message #627125 is a reply to message #627123] Fri, 07 November 2014 07:54 Go to previous message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Thanks from the bottom of my heart.
Previous Topic: How to get the result for my query
Next Topic: EXECUTE IMMEDIATE trowing an error in PL/SQL Block
Goto Forum:
  


Current Time: Fri Apr 26 08:02:45 CDT 2024