OraFAQ Forum: Reports & Discoverer » Searching Text Query

Home » Developer & Programmer » Reports & Discoverer » Searching Text Query

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

Searching Text Query [message #214320]

Mon, 15 January 2007 17:59

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Hi

I am a total novice & need some help with a query I've got in Oracle Discoverer.

What I have got is a query that searches a text field looking for where the text I am searching exsists and then pulling out the necessary entry for that.

That works fine. My only problem is when I conduct a keyword search and get my results I don't necessary know what keyword is in the entry returned unless I read the whole thing.

If possible what I want is to conduct the query, get the result and also include a column that informs me what keyword was hit.

Is this possible and if so how do I go about writing it?

Thanks

Jon

Report message to a moderator

Re: Searching Text Query [message #214454 is a reply to message #214320]

Tue, 16 January 2007 08:55

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Jon,

Do you mean you have a parameter (let's say p_text_search) and a condition (let's say c_text_search with the content: item_text like '%:p_text_search%')?

If so, you can show the value of p_text_search either in the title of the workbook, by entering &parameters there. Or you can have a column which repeats that value for every row in the resultset; to do so create a calculation with the content :p_text_search (this one will be at the bottom of the list of available items).

Regards,
Sabine

Report message to a moderator

Re: Searching Text Query [message #214618 is a reply to message #214320]

Wed, 17 January 2007 04:57

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Hi Sabine

Thanks for reply. As I said I am a novice, can write the query but that's about all!!

Anyway I am not sure if I understood you or not.

Here is an example of my SQL

SELECT BUS_REGION.ORDEROBJECTIDENTIFIER, BUS_REGION.ORDERSOURCE, BUS_REGION.ORDERTEXT1
FROM STAGE.BUS_REGION BUS_REGION
WHERE ( ( (
BUS_REGION.ORDERTEXT1 LIKE '%123456789%'
OR BUS_REGION.ORDERTEXT1 LIKE '%987654321%'
) ) )
;

This will return all the BUS_REGION which contain within the ORDERTEXT1 the parameters I am searching. (I am only using two for ease of the example).

The result returned may list hundreds of rows, which is why I would like to have a new column indictating the hit of the research (i.e. 123456789 or 987654321 in this example)

I've looked at the calculations and the last one I have is NLSSORT(char[, nlsparams])

Now I've no idea what I need to put into this calculation to get what I want. Am I right that I'd have to list all my search parameters here?

Sorry if I am unclear.

Many thanks
Jon

Report message to a moderator

Re: Searching Text Query [message #214717 is a reply to message #214618]

Wed, 17 January 2007 13:29

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Hi Jon,

No problem, we've all had our first experiences with Discoverer Very Happy

If your query is:

SELECT bus_region.orderobjectidentifier
      ,bus_region.ordersource
      ,bus_region.ordertext1
  FROM stage.bus_region bus_region
 WHERE bus_region.ordertext1 LIKE '%123456789%' OR
       bus_region.ordertext1 LIKE '%987654321%'

(formatted it a bit)

Then in Discoverer you would do:
- create a folder (probably from database) with the items bus_region.orderobjectidentifier, bus_region.ordersource, bus_region.ordertext1;
- create a new workbook, selecting these items
- create a condition on ordertext1, choose like and on the right part of the equation choose new parameter, let's say we name the parameter p_searchtext
- run the workbook

Now, if everthing went ok, Discoverer will ask you for a value for the parameter, fill in %123456789% and we have the first part ready.

Now, edit the condition and add one more line to it (choose advances, add a line similar but with a new parameter, let's say p_searchtext2), choose OR for the two lines in the condition.
Final step: go to the menu, choose edit title, type in &parameters and the values of p_searchtext and p_searchtext2 will apear in the title of your workbook.

Succes!

Regards,
Sabine

Report message to a moderator

Re: Searching Text Query [message #214842 is a reply to message #214717]

Thu, 18 January 2007 04:36

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Thanks for that Sabine

I think we may have crossed lines. I'm happy with creating the condition. I am actually importing the SQL as I normally having a large number of conditions to search.

My ideal solution is when I have the result returned is to have a column beside What I would really like is to have a new column created showing the search parameter which shows what search parameter was hit in ORDERTEXT1.

I think you did mention this was possible.

Thanks so far for all your help

Jon

Report message to a moderator

Re: Searching Text Query [message #214943 is a reply to message #214842]

Thu, 18 January 2007 12:58

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Yes, you can create a column with the search text as the value (please mind that that value will be repeated for each row then). However, if the search text is in the SQL itself (so, imported into a custom folder) Discoverer doesn't know about it, it just shows the result of the SQL statement.
But, if you have a condition based on a parameter, then you can create a calculation with a content like ':p_searchtext' (you can select that from the items available, it will be at the bottom of the list). This calculation now becomes that column with the value of p_searchtext in it.

Report message to a moderator

Re: Searching Text Query [message #215324 is a reply to message #214943]

Sun, 21 January 2007 06:43

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Hi Sabine

Thanks for all your help. I got this to work but from what you are saying it is not possible to get exactly what I wanted becuase if I added a 2nd or 3rd parameter they are also included in a new column against all rows.

Looks likes I'll have to live with this but once again thanks for all your help. I've learnt a few extras things about discoverer.

Jon

Report message to a moderator

Re: Searching Text Query [message #215327 is a reply to message #215324]

Sun, 21 January 2007 07:16

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Just to be sure:
if you have three pararameters (let's say :p_1, :p_2 and :p_3), you can create three calculations, each giving the value of one of the parameters.
But maybe I misunderstand your requirement...

Report message to a moderator

Re: Searching Text Query [message #215520 is a reply to message #215327]

Mon, 22 January 2007 12:28

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Sabine

I tried this and I get a column with p_1, p_2 bu in every row. Which is not what I wanted.

I know it's difficult to explain clearly.

For example the field I am searching contains

"I am hoping for 123456789 by tomorrow and 987654321 by next week."

If I search on 123456789 or 987654321 I would expect two results. I would ideally like an additional column which would list in the 1st returned result 123456789 (as this is the hit for the search) and in the 2nd row 987654321 (again this is because it is the search hit)

What is probably difficult is that I will be searching 1000's of records with 100's of parameters. Which is why an addtional column with the hit listed would help speed my work up.

From what you have said I don't think this will be possible.

Once again many thanks
Jon

Report message to a moderator

Re: Searching Text Query [message #216028 is a reply to message #215520]

Wed, 24 January 2007 13:11

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Aha, indeed I completely misunderstood your requirement... Sorry! But hey, that's a nasty requirement Wink

Of course this should be possible using SQL (although I have no clue how right now...), I'm thinking about a function that can be used in Discoverer. But I have to think it over, I'll get back to you.

Report message to a moderator

Re: Searching Text Query [message #216237 is a reply to message #216028]

Thu, 25 January 2007 13:36

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Have been thinking, no luck, can't find a solution. Mad

If I were you, I would post this question in the SQL experts, maybe someone overthere can help you (don't mention Discoverer, because then your post will be moved straight back to here Laughing

)

And another suggestion: state your question something like

I have data like this:

id  value
1   'blablaba 123 blabla 234'
2   'blabla 234 blablabla 345'

I want to search the value column with 2 parameters, with values like for example 123 and 234, and I need the output to be:

id  search_hit  value
1   123         'blablaba 123 blabla 234'
1   234         'blablaba 123 blabla 234'
2   234         'blabla 234 blablabla 345'

(That is if I understand your requirement correctly now...)

Good luck!

Report message to a moderator

Re: Searching Text Query [message #216255 is a reply to message #216237]

Thu, 25 January 2007 14:42

Devon Dodo
Messages: 10
Registered: January 2007
Location: United Kingdom

Junior Member

Hi Sabine

Many many thanks for all your help.

I will give it a go in SQL Experts.

Jon

Report message to a moderator

Previous Topic:	Absolute Newbie
Next Topic:	5 queries in single report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 04:52:43 CDT 2024