I tried to run a dynamically generated SQL query within PHP targeting an Sybase SQL-Anywhere database and I got the following error:
Warning: sybase_query(): message: SQL Anywhere Error -680: Invalid expression in WHERE clause of Transact-SQL outer join (severity 16) in /path/to/file.php
SQL Query String:
SELECT DISTINCT v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster
WHERE ( v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 6
)
OR v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 14
)
OR v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 25
)
-- more OR clause subqueries with different ILD_DIS_FK values
)
ORDER BY v_InventoryMaster.INV_ScanCode
I'm not completely new to SQL or interfacing with a database, but this message has me stumped. It claims that there is an invalid expression in the WHERE
clause, but I can't see how the query is illegally structured. My guess is the error involves the OR
and joining of the two search results.
Furthermore, by running these three separate queries and combining the results (in Excel), returns the correct set of results:
Query A:
SELECT DISTINCT v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster
WHERE ( v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 6
)
ORDER BY v_InventoryMaster.INV_ScanCode
Query B
SELECT DISTINCT v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster
WHERE ( v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 14
)
ORDER BY v_InventoryMaster.INV_ScanCode
Query C
SELECT DISTINCT v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster
WHERE ( v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = 25
)
ORDER BY v_InventoryMaster.INV_ScanCode
To clarify what return results I want:
Sybase documentation about error -680
says the following:
An expression in the WHERE clause of a query that uses Transact-SQL syntax contains a comparison of a column from the NULL-supplying table with a subquery or an expression that references a column from another table.
What is invalid about the original SQL query?
What does the documented explanation mean?
How could I edit the original SQL query to get the desired results?
Note that since this query was dynamically generated I want to know how I can change the statements between the OR
clauses:
Statement Structure:
v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = value -- value dynamically chosen by user
)
You could trivially replace the fragments:
v_InventoryMaster.INV_PK NOT IN (
SELECT DISTINCT v_InventoryMaster.INV_PK
FROM ecrs.v_InventoryMaster, ecrs.StockInventoryLinkDiscounts
WHERE v_InventoryMaster.INV_PK = StockInventoryLinkDiscounts.ILD_INV_FK
AND StockInventoryLinkDiscounts.ILD_DIS_FK = value -- value dynamically chosen by user
)
By:
NOT EXISTS (
SELECT *
FROM ecrs.StockInventoryLinkDiscounts sild
WHERE sild.ILD_INV_FK = tbl.INV_PK
AND sild.ILD_DIS_FK = value -- value dynamically chosen by user
-- ^^^^^ NOTE: this should probably be sild.ILD_DIS_PK
)
With tbl
being the correlation name for the outer query; the outer query would become:
SELECT DISTINCT v_InventoryMaster.INV_ScanCode
, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster tbl
Also note that I removed the ecrs.v_InventoryMaster
table from the subquery, since it is already present in the outer query, and would result in exactly the same row(s) being checked as the outer query already has found.
This will give the complete query as:
SELECT DISTINCT v_InventoryMaster.INV_ScanCode
, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster tbl
WHERE NOT EXISTS (
SELECT *
FROM ecrs.StockInventoryLinkDiscounts sild
WHERE sild.ILD_INV_FK = tbl.INV_PK
AND sild.ILD_DIS_FK = 6
)
OR NOT EXISTS (
SELECT *
FROM ecrs.StockInventoryLinkDiscounts sild
WHERE sild.ILD_INV_FK = tbl.INV_PK
AND sild.ILD_DIS_FK = 14
)
OR NOT EXISTS (
SELECT *
FROM ecrs.StockInventoryLinkDiscounts sild
WHERE sild.ILD_INV_FK = tbl.INV_PK
AND sild.ILD_DIS_FK = 25
)
;
My guess is that the parser is confused by the unaliased references to ecrs.v_InventoryMaster
. Another possibility is that the range table is full (if you have a lot of subquery-terms)