Inline View Equivalent in Informix

[daYorkie]

I'm new to Informix and I'm unable to get a working inline view as per OracleSql. I'm executing the following statement:

SELECT A.ACCT_ID, A.BALANCE, A.SUM_AP, NEWPAY.SUMBALANCE
FROM TACCT A,
(SELECT ACCT_ID, SUM(AMOUNT) AS SUMBALANCE
FROM TJRNL
WHERE CR_DATE=CURRENT
GROUP BY ACCT_ID) AS NEWPAY
WHERE A.ACCT_ID=NEWPAY.ACCT_ID

and receive the Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)

Problem seems to lie with the sub query -
(SELECT ACCT_ID, SUM(AMOUNT) AS SUMBALANCE
FROM TJRNL
WHERE CR_DATE=CURRENT
GROUP BY ACCT_ID) AS NEWPAY

This runs as long as I remove the table alias 'AS NEWPAY' but obviously I need the alias to be able to join.

Any ideas what's wrong - or is there not the concept of inline view in Informix.

I'm using WinSql Client with an ODBC connection to the Informix database.

Thanks

 

[PHV]

What happens if you replace this:
AS NEWPAY
with this ?
NEWPAY

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daYorkie]

Hi,
Thanks for the reply.

Removing the AS keyword still returns the same error, however I think I've cracked it - not seen syntax like this before!

SELECT A.ACCT_ID, A.BALANCE, A.SUM_AP, NEWPAY.SUMBALANCE
FROM TACCT A,
TABLE( MULTISET
(SELECT ACCT_ID, SUM(AMOUNT) AS SUMBALANCE
FROM TJRNL
WHERE CR_DATE=CURRENT
GROUP BY ACCT_ID)) AS NEWPAY
WHERE A.ACCT_ID=NEWPAY.ACCT_ID

 

[PHV]

Anyway, why not simply this ?
SELECT A.ACCT_ID, A.BALANCE, A.SUM_AP, SUM(J.AMOUNT) AS SUMBALANCE
FROM TACCT A, TJRNL J
WHERE A.ACCT_ID=NEWPAY.ACCT_ID AND J.CR_DATE=CURRENT
GROUP BY A.ACCT_ID, A.BALANCE, A.SUM_AP

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daYorkie]

Yep you're absolutely right - I'm making it unnecessarily complicated

SELECT A.ACCT_ID, A.BALANCE, A.SUM_AP, SUM(J.AMOUNT) AS SUMBALANCE
FROM TACCT A, TJRNL J
WHERE A.ACCT_ID=J.ACCT_ID
AND J.CR_DATE=CURRENT
GROUP BY A.ACCT_ID, A.BALANCE, A.SUM_AP

For some reason the simple solution didn't come to mind first!

Thanks again.

 

[mberni]

Hi, even if your problem is solved, the syntax error appears because you tried to select more than one single value within the inline-select clause. This is not supported.