SQL State 3000

[jlaz579]

Anyone have any idea what the following error means.
"Failed to Open a rowset.
Details: ADO error code: 0x
Source: Microsoft Office Access Database Engine
Description: JOIN expression not supported
SQL State: 3000
Native Error:"

I know it's not fetching any rows from the db, what I don't understand is why. Can't find any information on SQL State 3000. Error is being generated by an SQL Select statement for a Crystal Report. Only LEFT and INNER joins are used in the select statement.

[techgnome]

It's telling you what's wrong... you've got an illegal expression in one of your joins.

-tg

[jggtz]

Post the query and Access & CR version

[jlaz579]

I'm using Crystal Reports that comes integrated with Visual Studio 2008. I believe that's CR 11.

The query is based on 5 tables. The tables involved are orders (keyed by order_date (datetime)), inventory (keyed by warehouse_num, rack_num), warehouses (keyed by warehouse_num), categories (keyed by cat_num), and items (keyed by item_num). Warehouses, categories and items basically contain descriptive information, i.e. item description, category description, warehouse location.
The inventory table is used to show the location (rack) of a particular item for a particular warehouse. The orders table as implied contains a custormers order.

I want to report on everything listed on the order regardless of whether the item is available in the warehouse selected. When orders are to be filled, the clerk selects a specific order from a drop down as well as the warehouse where the order is to be filled. Since the report list only data fro one specific order, warehouse data is printed in the header of the report. The report is then grouped by rack and then by category. The items ordered are then printed in alpha order.

SELECT orders.list_date, orders.cust_num, orders.item_num, items.item_desc, orders.cat_num, orders.quantity,
orders.comment, orders.discount, inventory.warehouse_num, inventory.rack_num, categories.cat_desc,
warehouse.street_address, warehouse.city, warehouse.state, warehouse.zip, warehouse.phone
FROM (((orders
LEFT JOIN inventory ON orders.item_num=inventory.item_num)
INNER JOIN items ON orders.item_num = items.item_num)
INNER JOIN categories ON orders.cat_num = categories.cat_num)
INNER JOIN racks ON (inventory.store_num = racks.store_num) AND
(inventory.rack_num = racks.rack_num)))
INNER JOIN warehouses on inventory.warehouse_num = warehouses.warehouse_num
where ( orders.quantity <> 0 or orders.discount = 1) and (isnull(inventory.warehouse_num) or
inventory.warehouse_num= {?selected_warehouse})

I've tried adding the joins one at a time to try and isolate the join causing the problem and it seem to occur on any JOIN following the join on categories. This is probably something fairly simple but I've learned after designing systems for 25+ years that sometimes you're reading what you expect to see rather then what's actually there and in most cases, it just needs a second set of eyes on the code to point out the problem.

Thanks for taking a crack at this.

[techgnome]

Ungh.... let me guess, this query DOES work in Access, doesn't it? Just not in Crystal?

Try this:

SELECT orders.list_date, orders.cust_num, orders.item_num, items.item_desc, orders.cat_num, orders.quantity, 
orders.comment, orders.discount, inventory.warehouse_num, inventory.rack_num, categories.cat_desc, 
warehouse.street_address, warehouse.city, warehouse.state, warehouse.zip, warehouse.phone
FROM orders
LEFT JOIN inventory ON orders.item_num=inventory.item_num
INNER JOIN items ON orders.item_num = items.item_num
INNER JOIN categories ON orders.cat_num = categories.cat_num
INNER JOIN racks 
    ON (inventory.store_num = racks.store_num) 
    AND (inventory.rack_num = racks.rack_num)
INNER JOIN warehouses on inventory.warehouse_num = warehouses.warehouse_num
where 
    (orders.quantity <> 0 or orders.discount = 1)
    and (isnull(inventory.warehouse_num) 
              or inventory.warehouse_num= {?selected_warehouse})

Also - does IsNull work in Access? I'm no Access guru, so I don't know for sure. I know it works in SQL Server, but I thought Access had a different method... at any rate... give that a try.

-tg

[jlaz579]

This did not work but I think I may have finally figured out what was causing the problem. I changed the INNER JOIN on racks to a LEFT OUTER JOIN and the INNER JOIN on Warehouses to a LEFT OUTER JOIN and that seems to have done the trick. Still testing but hopefully I'm now past this problem. Thanks for the input.