SQL LIKE OR problem.

[MrDigital]

Okay, I'm trying to create a sales history report and I'm encountering a problem I can't easily find the answer.

My SELECT statement works fine for the most part. I select from multiple tables (this is the SQL backend to a inventory/accounting software, it's Pervasive SQL) and use a couple of where clauses. It works fine until I try to do an OR at the end.

Here's the whole SQL line:
SELECT SALES_HISTORY_HEADER.IN_DATE,SALES_HISTORY_HEADER.NUMBER,SAL ES_HISTORY_DETAIL.WHSE,SALES_HISTORY_DETAIL.CODE,SALES_HISTO RY_HEADER.CUST_NO,SALES_HISTORY_DETAIL.BVUNITPRICE,SALES_HIS TORY_DETAIL.BVCMTDQTY FROM \"SALES_HISTORY_HEADER\",\"SALES_HISTORY_DETAIL\" WHERE SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.RECNO > 0 AND SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%'

The statement is basically trying to retrieve all data that has a part code that starts with HG or HX.

Now, the problem comes at the end.
SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%'
If I remove the OR statement, it functions fine. I can get HG% titles OR HX% titles, but not both.

If I keep the LIKE statement but specify specific titles instead of wildcards, that doesn't work either.
SALES_HISTORY_DETAIL.CODE LIKE 'HGV07111906' OR SALES_HISTORY_DETAIL.CODE LIKE 'HXDA0046772'

The statement also doesn't work if I just specify two titles individually.
SALES_HISTORY_DETAIL.CODE = 'HGV07111906' OR SALES_HISTORY_DETAIL.CODE = 'HXDA0046772'

Am I just misusing the OR clause? Online it seems like people are using it to compare two separate columns, and I'm trying to use it on the same column. If that is in fact my problem, can someone point me to the correct way to select specific wildcards from the same column?

Thanks.

[Fig]

Try this. I'm not sure what you are doing with the double quotes and backslashes. I'm guessing it has something to do with the pervasive sql which I'm not sure what you mean by that. If I were to write this query in MS SQL Server I would write it like what I've pasted below. Try to stay away from LIKE ('xx%') as it is far more costly and not as efficient. Again, if this is pervasive SQL then maybe this doesn't apply, I dunno. Good luck. :)


SELECT
SALES_HISTORY_HEADER.IN_DATE,
SALES_HISTORY_HEADER.NUMBER,
SALES_HISTORY_DETAIL.WHSE,
SALES_HISTORY_DETAIL.CODE,
SALES_HISTORY_HEADER.CUST_NO,
SALES_HISTORY_DETAIL.BVUNITPRICE,
SALES_HISTORY_DETAIL.BVCMTDQTY
FROM 'SALES_HISTORY_HEADER' WITH (NOLOCK)
INNER JOIN 'SALES_HISTORY_DETAIL' WITH (NOLOCK)
ON SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER
WHERE
(SALES_HISTORY_DETAIL.RECNO > 0 AND LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')
OR
(LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')

[MrDigital]

The \'s and quotes = I dunno. The guy who wrote the Perl script before me had them there and I just leave them alone. ;) Sorry, should've clarified that.

Pervasive SQL used to be known as Btrieve, if you're familiar with that. It's not quite the same as regular SQL, but it seems to use all the regular ANSI SQL statements.

I'm not sure exactly what you're doing here:
(SALES_HISTORY_DETAIL.RECNO > 0 AND LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')
OR
(LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')

Why are you using an AND statement in the first case but not the second?

If I try it as is, I get this:
DBD::ODBC::db prepare failed: [Pervasive Software][ODBC Interface][Pervasive Software SQL Engine]An alias name has already been defined for the table. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at \\ppm\scripts\modules/bv_access.pm line 54.

I had to remove the inner join to make it work. So now it's like
WHERE SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND (SALES_HISTORY_DETAIL.RECNO > 0 AND LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX') OR (LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')

And it still doesn't work. :( It takes a long time to run too, which is how I know it's going to fail.

If I tell it to just give me H% it finishes quickly, but the problem is that the H% includes a few things I don't want, which is why I need to use the two letters.

When I use the OR statement it just takes 10x as long.

[MrDigital]

I know PSQL like the code, since if I use the DB control screen and use "SELECT * FROM "SALES_HISTORY_DETAIL" where code like 'hx%' or code like 'hg%'" it works fine and gives me the results I want.

EDIT: and to clarify what's happening, it just returns 0 results, the SQL doesn't fail or anything. The script generates Excel files and it just generates zero results if I use the OR clause.

[Fig]

Got it. I'll look around and see if I can find anything out concerning PSQL syntax and supported searching commands, etc.

[Fig]

The \'s and quotes = I dunno. The guy who wrote the Perl script before me had them there and I just leave them alone. ;) Sorry, should've clarified that.

Pervasive SQL used to be known as Btrieve, if you're familiar with that. It's not quite the same as regular SQL, but it seems to use all the regular ANSI SQL statements.

Never heard of it, but supporting regular ANSI SQL statements is a good to know. It's possibly a problem with syntax embedding the SQL statements, perhaps?

I'm not sure exactly what you're doing here:
(SALES_HISTORY_DETAIL.RECNO > 0 AND LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')
OR
(LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')

Why are you using an AND statement in the first case but not the second?


LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX') evaluates only the first two characters in that field while searching so it's faster as it doesn't then have to compare more than necessary. Depending on how PSQL translates the code it may make no difference here at all, I haven't read enough about it yet.

We don't need the first and statement because the join was done earlier so the where statement is no longer where the join is specified. The parenthesis works the same in this code as it does in algebra. I saw it being used in the code samples that I linked to so it looks like it should work the same.

If I try it as is, I get this:
DBD::ODBC::db prepare failed: [Pervasive Software][ODBC Interface][Pervasive Software SQL Engine]An alias name has already been defined for the table. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1) at \\ppm\scripts\modules/bv_access.pm line 54.

I had to remove the inner join to make it work. So now it's like
WHERE SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND (SALES_HISTORY_DETAIL.RECNO > 0 AND LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX') OR (LEFT(SALES_HISTORY_DETAIL.CODE, 2) = 'HX')

And it still doesn't work. :( It takes a long time to run too, which is how I know it's going to fail.

If I tell it to just give me H% it finishes quickly, but the problem is that the H% includes a few things I don't want, which is why I need to use the two letters.

When I use the OR statement it just takes 10x as long.

You'll have to forgive me, I'm home sick and heavily medicated so I'm a bit out of it.

Ok, I found a link that shows SQL syntax and you are correct, it basically supports a fair amount of common functions and the joins even so it has to be something else that is causing it to fail.

http://ww1.pervasive.com/library/docs/psql/910/sqlref/sqlref-04-63.html


If it doesn't work, lemme know if it returns and error or not. We're only returning the first 10 results so it should take too long. I've reverted to the join method which you are using as we already know it works. If this doesn't work, try the one below. If that doesn't worke, let me know what error you get or if it still just chugs along for too long. I was hoping 'WITH (NOLOCK)' would work so that it would be faster but it doesn't look like it's supported. Is this live data?


SELECT TOP 10
SALES_HISTORY_HEADER.IN_DATE,
SALES_HISTORY_HEADER.NUMBER,
SALES_HISTORY_DETAIL.WHSE,
SALES_HISTORY_DETAIL.CODE,
SALES_HISTORY_HEADER.CUST_NO,
SALES_HISTORY_DETAIL.BVUNITPRICE,
SALES_HISTORY_DETAIL.BVCMTDQTY
FROM
\"SALES_HISTORY_HEADER\",\"SALES_HISTORY_DETAIL\"
WHERE
(SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.RECNO > 0 AND Left(SALES_HISTORY_DETAIL.CODE,2) = 'HG')
OR
(SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND Left(SALES_HISTORY_DETAIL.CODE,2) = 'HX')




SELECT TOP 10
SALES_HISTORY_HEADER.IN_DATE,
SALES_HISTORY_HEADER.NUMBER,
SALES_HISTORY_DETAIL.WHSE,
SALES_HISTORY_DETAIL.CODE,
SALES_HISTORY_HEADER.CUST_NO,
SALES_HISTORY_DETAIL.BVUNITPRICE,
SALES_HISTORY_DETAIL.BVCMTDQTY
FROM
\"SALES_HISTORY_HEADER\",\"SALES_HISTORY_DETAIL\"
WHERE
(SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.RECNO > 0 AND SALES_HISTORY_DETAIL.CODE LIKE 'HG%')
OR
(SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.CODE LIKE 'HX%')

[MrDigital]

I'm sorry to bother you while you're sick. :)

Actually, it seems like just adding parantheses around the OR statement worked.
(SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%')

Next I tried it with your method:
(Left(SALES_HISTORY_DETAIL.CODE,2) = 'HG' OR Left(SALES_HISTORY_DETAIL.CODE,2) = 'HX')

And it works too. However, it actually seems to take longer using the LEFT method..? Either way it takes 10x longer than just using the singular method which seems weird. Even if it was reading the database twice instead of comparing on the fly it wouldn't be as much of a time difference as it is.

EDIT: And yeah, it's live data. That's how I roll. ;)

[Fig]

I'm sorry to bother you while you're sick. :)

Actually, it seems like just adding parantheses around the OR statement worked.
(SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%')

Next I tried it with your method:
(Left(SALES_HISTORY_DETAIL.CODE,2) = 'HG' OR Left(SALES_HISTORY_DETAIL.CODE,2) = 'HX')

And it works too. However, it actually seems to take longer using the LEFT method..? Either way it takes 10x longer than just using the singular method which seems weird. Even if it was reading the database twice instead of comparing on the fly it wouldn't be as much of a time difference as it is.

EDIT: And yeah, it's live data. That's how I roll. ;)

Wierd that a wild card mulit-character search with 'LIKE' was faster than a definitive two digit search. I dunno what to say about that, but I'm glad it worked!

EDIT: So is it working with SELECT instead of 'SELECT TOP' in a timely manner now? I just assumed it was working well now...

[MrDigital]

Oh, I never even did the TOP 10 to be honest. It worked before I remembered to add it so I never bothered. Now it's back to Perl script modification. :/

Thanks for your help. I know where to come with SQL questions now. ;)

[Fig]

Oh, I never even did the TOP 10 to be honest. It worked before I remembered to add it so I never bothered. Now it's back to Perl script modification. :/

Thanks for your help. I know where to come with SQL questions now. ;)


Sure thing. I'll leave the perl to you. *shudder*

I should have a good amount of free time for the next couple of months so feel free. I need to get my brain working again.

[ksuohio]

Just like to throw in that an OR clause is actually the same as running two queries with a UNION statement. Therefore:

SELECT LastNm, FirstNm
FROM Customers
WHERE (LastName LIKE 'H%' OR LastNm LIKE 'G%') AND State = 'OH'

SELECT LastNm, FirstNm
FROM Customers
WHERE LastName LIKE 'H%' AND State = 'OH'
UNION
SELECT LastNm, FirstNm
FROM Customers
WHERE LastName LIKE 'G%' AND State = 'OH'

For the example you gave, I like to do something like:

SELECT LastNm, FirstNm
FROM Customers
WHERE SUBSTRING(LastName,1,1) IN ('G','H') AND State = 'OH'

Depending on the database, a LIKE clause will still use an INDEX whereas comparing positions of a string requires a table scan. This is because, the substring function has to parse each record and then do a comparision. To you, it makes sense to look at the first character and use the index in that manor, but to the database engine, it just nows that you are looking at a character in a string and doesn't consider if you start at position 1 or position 8.

[MrDigital]

A good point, and something I may look into as the database gets larger. The script was developed for a database half the size, and it's already fraying at the edges. I'm not looking forward to recoding it though, cause I'm not a coder.

[coop_2000]

You aren't using the mighty parens when dealing with ANDs & ORs:

WHERE SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.RECNO > 0 AND (SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%')

[MrDigital]

You aren't using the mighty parens when dealing with ANDs & ORs:

WHERE SALES_HISTORY_DETAIL.NUMBER = SALES_HISTORY_HEADER.NUMBER AND SALES_HISTORY_DETAIL.RECNO > 0 AND (SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%')
Actually, it seems like just adding parantheses around the OR statement worked.
(SALES_HISTORY_DETAIL.CODE LIKE 'HG%' OR SALES_HISTORY_DETAIL.CODE LIKE 'HX%')

:) Thanks for the input though.

[coop_2000]

LOL sorry didnt read through the all the posts, obviously :)