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
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.
