AnsweredAssumed Answered

SQL query with ODBC driver works, with JDBC we get an Error?

Question asked by CarstenLehmann on Apr 2, 2018

Hello Filemaker Community,


The following problem with the SQL query over JDBC (FM14 driver, the FM16 made problems). It searches for supplier number% 795%, displays a selection of results -> query for this one works.
If you then query the orderID in the second field, it looks like the wrong column is being searched for -> instead of the orderID in the supplier no searched.

 

The following query shows the result correctly: (795 in the vendor number column)
SELECT DISTINCT
Order number_FM AS ORDER,
OrderID AS ORDER,
Process AS PROCESS,
OrderName Company AS NAME,
Supplier ID AS NUMBER,
Supplier name AS SHORT NAME
FROM OrdersPOS
WHERE ((STRVAL (vendor-no) LIKE '% 795%') OR (STRVAL (orderID) LIKE '% 795%'))
ORDER BY Order Number_FM DESC

The following query shows the result is incorrect: (4437 in the VendorNum column instead of the OrderID):
SELECT DISTINCT
Order number_FM AS ORDER,
OrderID AS ORDER,
Process AS PROCESS,
OrderName Company AS NAME,
Supplier ID AS NUMBER,
Supplier name AS SHORT NAME
FROM OrdersPOS
WHERE ((STRVAL (vendor-no) LIKE '% 4437%') OR (STRVAL (orderID) LIKE '% 4437%'))
ORDER BY Order Number_FM DESC

Since the 4437 is not assigned to a supplier, it shows no result here. But would have to find a hit in the order ID.

 

When I run this SQL query via Excel and the Filemaker ODBC driver, I get the correct result! Is here a Error in the SQL Syntax?

 

Sincerely

Carsten Lehmann

Outcomes