Compound Query Problems 1

[DrunkenEngineer]

I'm trying to do a concurrent search through a database, and I'm running into a roadblock...

I'm building a search page using Intergraph's Digital Print Room software, and we're trying to import data from an old Dbase dataset.. because this old dbase set could only handle 27 characters per data field, we had to split the title blocks of all the CAD drawings into 3 parts (title1, title2, title3). What I'm trying to do with the search is to search ALL these fields at once, similar to:

SELECT * FROM TABLE WHERE TITLE1 OR TITLE2 OR TITLE3 LIKE <whatever>

But the database goes crazy whenever I submit that string.. sometimes returning garbage, sometimes returning nothing at all...

Anyone have any ideas as to how I can do this with SQL Server 7, or another approach? The whole system is based around ASP..

THanks!

 

[rajeevnandanmishra]

Hi DrunkenEngineer,
In SQL you should run your query like :
SELECT * FROM TABLE WHERE TITLE1 LIKE <whatever> OR OR TITLE2 LIKE <whatever> OR TITLE3 LIKE <whatever>

 

[DrunkenEngineer]

That's what I've tried, and its still returning almost all the lines in the database even though there's only 1 entry with my specific query.. quite unusual!

 

[tlbroadbent]

How about searching the columns as though they were one?

SELECT * FROM TABLE
WHERE TITLE1 + TITLE2 + TITLE3 LIKE '%searchstring%' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[DrunkenEngineer]

That method doesn't return any rows at all... its very unusual using the title1 LIKE <blah> OR title 2 LIKE.... etc what happens is that when it returns all the rows, it somehow inserts the query into ALL the entries, even though it only exists in one...

For example, I'll query on &quot;test&quot;, and only 1 entry in the whole table will have the word &quot;test&quot; in any of its title fields.. but it returns ALL the rows in the table, and ALL of them have the word &quot;test&quot; in their title(x) field. THAT'S what's throwing me for a loop... at first I thought it was with the Intergraph software, but as it turns out its SQL 7.0... doing a query in the QUery Analyzer returns the garbage as well...

This is getting frustrating!

 

[rajeevnandanmishra]

Hi DrunkenEngineer,
Now it is the time, when you should put your actual SQL statement. Please put your full Statement. And the data row in which you are having the 'test' value.

 

[tlbroadbent]

Could you post your exact query? It is difficult to undrestand why you get the results you get unless the syntax is incorrect in some way. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[DrunkenEngineer]

Here's the query:

Select PlotSetName, QPR_ORIG_USER, NUM_PAGES, QPR_QUEUE, QPR_HOST, NAME, title1, title2, title3, issue, vehgrp, vehicle, notes, supersededBy, A13PlotSets.iPlotSetID, iSheetID, iDirectoryName From A13PlotSets, A13Sheets WHERE A13PlotSets.iPlotSetID = A13Sheets.iPlotSetID AND title1 LIKE 'brake' OR title2 LIKE 'brake' OR title3 LIKE 'brake'


This query string was generated by Ingr's DPR software, and it works fine when you're only doing 1 query at a time. I've tried adding the ORs at the end and thats when the trouble began.

I've done this on Oracle 8i without any problems, that's the weird part.

 

[karluk]

I haven't been following this thread closely, but it always makes me suspicious to see &quot;and&quot; and &quot;or&quot; operators grouped together without parentheses. Are you sure you shouldn't be writing

WHERE A13PlotSets.iPlotSetID = A13Sheets.iPlotSetID
AND
(title1 LIKE 'brake' OR title2 LIKE 'brake' OR title3 LIKE 'brake')

The way you have it right now, your conditions are evaluated as if they were grouped as follows

WHERE (A13PlotSets.iPlotSetID = A13Sheets.iPlotSetID
AND title1 LIKE 'brake')
OR title2 LIKE 'brake'
OR title3 LIKE 'brake'

 

[DrunkenEngineer]

ARGH I can't beleive I didn't think of that I guess Oracle and MS process their operations differently...

ANyways, thanks for the help!