2

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 :

enter image description 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) :

enter image description here

And ran this query:

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

and got this (The Ord_No exists) :

enter image description here

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

  • For troubleshooting purposes, add "or NUMBER LIKE '%0000125199%'" to your where clause. If you still don't get any rows, do a left join from the details to the header table. Maybe you don't have a matching row in the header table. – Dan Bracuk Jun 25 '13 at 19:15

4 Answers4

3

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%'

Patrick Paquet
  • 161
  • 1
  • 3
1

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

Md. Parvez Alam
  • 4,326
  • 5
  • 48
  • 108
0

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'
Vasanth
  • 1,670
  • 1
  • 13
  • 17
  • I had to put Where NUMBER LIKE '%0000125199%' for it to work,. But when i highlight it to edit it shows there are no spaces –  Jun 26 '13 at 11:27
  • Try updating number value in Sales_history_detail table to '0000125199' where number like '%0000125199%' and then try your select query. – Vasanth Jun 26 '13 at 13:54
0

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%'
Mustafa Soy
  • 48
  • 7
  • You're missing a right parenthesis on the where clause, even with that it returns nothing :| –  Jun 26 '13 at 11:19