Query returning nothing

Question

My query is as follows:

Select h.ord_no
from sales_history_header h
INNER JOIN sales_history_detail d
ON d.NUMBER = h.NUMBER 
WHERE d.COMMENTS LIKE '%3838CS%'

And I get no results as shown here :

But I should get results because :

I ran the query:

Select NUMBER, Comments from SALES_HISTORY_DETAIL WHERE NUMBER LIKE '%0000125199%'

and got this (As you can see there's a comment field with 3838CS contained in it) :

And ran this query:

Select NUMBER, Ord_No from "SALES_HISTORY_HEADER" WHERE NUMBER = '0000125199'

and got this (The Ord_No exists) :

How come my first original query returns no results? Do I have the syntax wrong ?

Answer 1

comment column is text ? did you try

Select h.ord_no
from sales_history_header h
INNER JOIN sales_history_detail d ON d.NUMBER = h.NUMBER 
WHERE cast(d.COMMENTS as varchar(max) LIKE '%3838CS%'

Answer 2

There is no issues with your query. Looks like a data issue. "Number" stored in SALES_HISTORY_DETAIL might have some space. Its hard to tell if there is some space in value from the SS.

Run the following query to see if your SALES_HISTORY_DETAIL table number value is stored correctly.

  Select NUMBER, Comments from SALES_HISTORY_DETAIL WHERE NUMBER = '0000125199'

Answer 3

I think this is because you have different data type for number in both table

Answer 4

Your query is returning nothing because the execution engine is using an index that is incorrectly referenced by this specific application (Sage BusinessVision) you have to work around the issue.

Explanation:

The issue you are having is related to the way BusinessVision created the index index of the table SALES_HISTORY_DETAIL. The PK (index key0) for this table is on both column NUMBER and RECNO.

Details on Pervasive indexs for BusinessVision

Here is the explanation of the way that index works with BV:

If you run a query that is capabable of using an index you will get better performance. Unfortunately the way pervasive compute this index for NUMBER is not working on its own.

--wrong way for this table
Select * from SALES_HISTORY_DETAIL WHERE NUMBER = '0000125199'
--return no result

Because of the way pervasive handle the index you should get no results. The workaround is you have to query on all the fields of the PK for it to work. In this case RECNO represent a record from 1 to 999 so we can specify all records with RECNO > 0.

--right way to use index key0
Select * from SALES_HISTORY_DETAIL WHERE NUMBER = '0000125199' and RECNO > 0

This will give you the result you expected for that table and use the index with the performance gain.

Note that you will get the same behavior in the table SALES_ORDER_DETAIL

Back you your question.

The query you ran to see the details did execute a table scan instead of using the index.

--the way you used in your question
Select * from SALES_HISTORY_DETAIL WHERE NUMBER LIKE '%0000125199%'

in that case it working, not because of the Like keyword but because of the leading '%'; remove it and that query won't work since the engine will optimise by using the weird index.

In your original query because you are referencing d.NUMBER = h.NUMBER pervasive use the index and you don't get any result, to fix that query simply add (and RECNO > 0)

Select h.ord_no
from sales_history_header h
INNER JOIN sales_history_detail d
ON d.NUMBER = h.NUMBER and RECNO > 0
WHERE d.COMMENTS LIKE '%3838CS%'

sage-businessvision pervasive-sql