Here is a query

SELECT
 P.ID,
 SC_O.Total as OpeningStockTotals,
 SC_C.Total as ClosingStockTotals

FROM
 Products P
 LEFT JOIN StockCountOnDate(DATE '2013-04-28') SC_O ON SC_O.ID = P.ID
 LEFT JOIN StockCountOnDate(DATE '2013-05-28') SC_C ON SC_C.ID = P.ID

StockCountOnDate is defined as follows:

CREATE PROCEDURE "StockCountOnDate" (IN "MinDate" DATE)
BEGIN
 DECLARE Result CURSOR WITH RETURN FOR Stmt;

PREPARE Stmt FROM
' SELECT
    ProductsID as ID,
    SUM(SCount) as OpeningBalance
 FROM StockCounts
 WHERE DateDone = ?
 GROUP BY ID ';
 OPEN Result using MinDate;

END

--------------

I can run the procedures correctly, but when I run the query I get an error that "ON is expected NOT ( "

There are other ways to make the above query work, but is it possible to do it in the way I have shown?

Adam Brett wrote:

> Here is a query
>
> SELECT
>   P.ID,
>   SC_O.Total as OpeningStockTotals,
>   SC_C.Total as ClosingStockTotals
>
> FROM
>   Products P
>   LEFT JOIN StockCountOnDate(DATE '2013-04-28') SC_O ON SC_O.ID = P.ID
>   LEFT JOIN StockCountOnDate(DATE '2013-05-28') SC_C ON SC_C.ID = P.ID
>
>
> I can run the procedures correctly, but when I run the query I get an
> error that "ON is expected NOT ( "
>
> There are other ways to make the above query work, but is it possible
> to do it in the way I have shown?

Adam,

Your query is trying to treat
"StockCountOnDate(DATE '2013-04-28')" and
"StockCountOnDate(DATE '2013-05-28')" as Derived Tables.

When I tried building a similar query on my end I got a different error:

SELECT
D.Creditor,
P.Title
FROM tblDebts D
LEFT JOIN (spSelectPaymentPlans) AS P
      ON D.PaymentPlanId = P.PaymentPlanId

ElevateDB Error #700 An error was found in the statement at line 5 and
column 12 (Expected SELECT but instead found spSelectPaymentPlans)

Try changing your syntax and see if you get the same error message as
me.

SELECT
P.ID,
SC_O.Total as OpeningStockTotals,
SC_C.Total as ClosingStockTotals

FROM
Products P
LEFT JOIN (StockCountOnDate(DATE '2013-04-28')) AS SC_O
      ON SC_O.ID = P.ID
LEFT JOIN (StockCountOnDate(DATE '2013-05-28')) AS SC_C
      ON SC_C.ID = P.ID

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael,

Yes I end up with the same error as you when I put the PROC into a bracketed WHERE:

SELECT
*
FROM Table T
LEFT JOIN (procName(param)) as proc ON proc.ID = T.ID

The syntax of allowing a procedure which returns data to be included in a standard WHERE doesn't seem to be present in EDB.

I have altered my scripts to use NESTED SELECTS instead, and found this method is surprisingly cool ... I haven't done it before, and it looks a little confusing but works fine!

SELECT
*
FROM Table T
LEFT JOIN (SELECT [Fields] FROM [Tables] WHERE [Conditions]) as proc ON proc.ID = T.ID

Adam Brett wrote:

> I have altered my scripts to use NESTED SELECTS instead, and found
> this method is surprisingly cool ... I haven't done it before, and it
> looks a little confusing but works fine!
>
> SELECT
>  *
> FROM Table T
> LEFT JOIN (SELECT [Fields] FROM [Tables] WHERE [Conditions]) as proc
> ON proc.ID = T.ID

Adam,

I agree that it looks confusing. It took me a while to wrap my head
around the concept. Glad to see it's working for you.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com