×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Inline View Equivalent in Informix

Inline View Equivalent in Informix

Inline View Equivalent in Informix

(OP)
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

RE: Inline View Equivalent in Informix

(OP)
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

RE: Inline View Equivalent in Informix

(OP)
Yep you're absolutely right - I'm making it unnecessarily complicated smile

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.

RE: Inline View Equivalent in Informix

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close