AllExperts > Oracle 
Search      
Oracle
Volunteer
Answers to thousands of questions
 Home · More Oracle Questions · Answer Library  · Encyclopedia ·
More Oracle Answers
Question Library

Ask a question about Oracle
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Suchitra Joshi
Expertise
I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience
15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
OCP - SQL and PL/SQL
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

 
   

You are here:  Experts > Computing/Technology > Oracle > Oracle > Search query

Oracle - Search query


Expert: Suchitra Joshi - 5/13/2009

Question
QUESTION: Hi Suchitra, i have to write a search statement on a table which is index based where the user wants to search all the columns for each row where all results are like or contain their search criteria. I can only narrow the rows by the index and a DATEFROM column. Could you give me some guidance with this please

Thanks
Niall

ANSWER: Hi Niall,

Can you please give me the exact table structure and what you want to query for? I am not able to understand your problem here.

Please give more details so that I can help you.

Regards

Suchitra

---------- FOLLOW-UP ----------

QUESTION: Hi Suchitra,

My Table is a jobs table with the following columns

Job_reference
customer_code
agent_reference
agent_code
consignee_town
Terms
port_of_loading
port_discharge_descr
number_of_pieces
cargo_descr
weight_kilos
hazardous
airway_bill
bill_of_lading_no
estimate_depart_date
estimate_arrival_date
flight_no
Ship
date_from

My select statement will need to search all the columns and rows for a list of jobs that match the search critera. My first query is get all the jobs for a particular customer_code i then can filter on date_from which will narrow my row count. Then with this resultset i have to get all rows which "contain or match" what the customer want to filter on. so every column for all these rows need to be searched and if returned if it finds a match.

The start of my query is like this i think.

select * from jobs where customer_code = 'my_customer' and date_from >= 'my_date' and(any row which has a column like or contains 'my_search" )

Thanks for your help
Niall

Answer
Hi Niall,

If you have to search all remaining columns for my_search, then give the following conditions -

(agent_reference like '%my_search%'
or agent_code like '%my_search%'
or ...
or ...
)

If the customer has option to choose the columns then you will have to use the Dynamic SQL query.

Regards

Suchitra

Add to this Answer   Ask a Question


 
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.