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