Question
David Hockenbroch · Jul 7

Subquery to Remote View Via ODBC Gateway

I'm having trouble with the following query:

SELECT
     'Y' AS "Invoiced",
     odlocn AS "Location ID",
     cmcust AS "Customer Number",
     cmbdat AS "Creation Date",
     cmname AS "Customer Name",
     cmadd1 AS "Address Line 1",
     cmadd2 AS "Address Line 2",
     cmcity AS "City",
     cmstat AS "State",
     cmzipc AS "ZIP Code",
     cmphon AS "Phone Number",
     innumb AS "Order Number",
     inpono AS "Cust PO Number",
     inordt AS "Order Date",
     inshdt AS "Ship Date",
     odsnum AS "Line Number",
     odqord AS "Qty Ordered",
     oddsc1 AS "Description",
     odmcst AS "Merchandise Cost",
     odbcst AS "Burden Cost",
     odlcst AS "Labor Cost",
     odnet AS "Price",
    (SELECT "Production Date" FROM CustV.CSP_WMS001 WHERE hsmfl.innumb = Order AND hsdfl.odsnum = Line) AS "Production Date",
     inindt AS "Last Updated",
     (SELECT "Operation Work Center" FROM CustV.CSP_WMS001 WHERE Order = inivno AND Line = odsnum) AS "Operation Work Center",
    (SELECT "Worker" FROM CustV.CSP_WMS001 WHERE Order = inivno AND Line = odsnum) AS "Worker",
     inuser As "User"
FROM SQLUser.hsmfl
     INNER JOIN SQLUser.hsdfl ON SQLUser.hsmfl.inivno = SQLUser.hsdfl.odnumb
     INNER JOIN SQLUser.cmmst ON SQLUser.hsmfl.incust = SQLUser.cmmst.cmcust
GROUP BY inivno, odsnum

This is information about old orders that have been invoiced. CustV.CSP_WMS001 is a view I've linked to through an odbc gateway connection. Those subqueries are failing, telling me:

 [SQLCODE: <-223>:<>]

  [%msg: <Connection 'WMSDRBETH': Prepare(select T4."Production Date" AS C1 FROM "Cust...): SQLState: (42000) NativeError: [23] Message: [Cache ODBC][State : 42000][Native Code 23] [libcacheodbc35.so] [SQLCODE: <-23>:<Label is not listed among the applicable tables>] [Location: <Prepare>] [%msg: < Label 'T1' is not listed among the applicable tables^select T4 . "Production Date" AS C1 FROM "CustV" . "CSP_WMS001" T4 where ( ( T4 . "Order" = %EXACT T1 . innumb )>] >]

It looks as if the data from the outer query is not being passed to the subquery property. I've tried giving the outer query an alias and using that in the subquery, and I've tried using the fully qualified names everywhere, but I always get the same error. I can query that linked view separately, and it works very quickly. Why wouldn't this be working correctly?

Product version: Caché 2016.1
00
1 0 0 67
Log in or sign up to continue