Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Query

 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,




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 ">
" 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
 
Dave Tolls
Ranch Hand
Posts: 2109
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What have you got so far?
Which bit are you stuck on?
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry for late reply as I was travelling

rownum<=5 can select 5 news from last 15 news

I stuck as to how can I select words in oracle because substr function is selecting sub string from residing data not words

Thanks again
 
Dave Tolls
Ranch Hand
Posts: 2109
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Farakh khan wrote:rownum<=5 can select 5 news from last 15 news


Depends how you're using rownum.
You might simply end up with a random 5 rows.

I stuck as to how can I select words in oracle because substr function is selecting sub string from residing data not words


Since there's only the 5 rows to handle, I would seriously consider doing this bit in Java.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Since there's only the 5 rows to handle, I would seriously consider doing this bit in Java.

I know split() function in java to separate words but trying to optimize performance as the description string have minimum 3950 characters. The java code will get string, split in words then to select first say 5 or 50 words.

Thanks again
 
Dave Tolls
Ranch Hand
Posts: 2109
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're only handling the 5 rows, though.
You could reduce the work needed by Java if you work on a max length rather than exactly 50 words.
Get that length from Oracle, then strip off the last word since it might only be a partial word.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great Dave. You mean I get bunch of characters and discard last word? Its really good idea

Thanks again
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Hello,



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

Best regards
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Slightly off-topic, but if (desc.equals("") || desc==null) doesn't work - if desc is null, then the first part of the condition will cause a NPE. You need to switch the two parts around, so that equals is invoked only if desc is not null.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks again for your reply

You mean:



Best regards
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that is essentially the same as you had before (because the null comparison never would have been invoked with a null object) - it fails with a null object. Re-read the second sentence of my previous post.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sorry for my poor language understanding due to non native person.

you mean that should be:
 
Dave Tolls
Ranch Hand
Posts: 2109
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Farakh khan wrote:
This is what I did with java code but still facing two problems:
1) how to select rownum<=5 from out of last posted 15 rows
2) Repeating this query after every 3 hours

Best regards


For (1):
Nested queries:

That will give you the first 15.
Then that needs nesting again to randomise the order:

and one more time to get only 5:


Note, you probably want to shift the substring stuff to the last (outer) select.
No point substringing rows that you're not actually going to use.
Of course, Oracle may handle that itself, I'm not sure.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thats worked perfectly what I was looking for but want to select randomly after 3-4 hours not on every fresh that the above code is doing
Farakh khan wrote:
2) Repeating this query after every 3 hours


please help me to sort out how can I ran it after every 3-4 hours so that once again random news could be displayed

Thanks again
 
Dave Tolls
Ranch Hand
Posts: 2109
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that ceases to be JDBC and becomes a question of using some form of timing mechanism, which itself becomes a question of who is driving this timing?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic