Guys,

Trying to do an aggregate function in InformiX, and alias the column as I place the syntax below into a view. I'm having problems, and keep getting this message. What's the deal?

"Need to specify view column names in the view definition"

CREATE VIEW tkt_chrg AS
SELECT a.traddate, a.source_acct, a.description, b.proddesc, a.buysell, a.shares, a.priceper,
a.invested, a.receivable, a.clearing, sum (a.receivable-a.clearing) as net
FROM
open_blot a, producttype b
WHERE a.prodtype = b.productype AND
a.prodtype in ('11','12') AND
a.traddate > = '01/01/2009'
group by 1,2,3,4,5,6,7,8,9,10

You need to specify the column names in your view definition, INFORMIX won't implicitly assign column names from tables to the column names in your view so use something like this:

CREATE VIEW tkt_chrg (tr_dt, src_acct, descr, prod_descr, buysell, shares, unit_price, invested, recv, clearing, net) AS
SELECT a.traddate, a.source_acct, a.description, b.proddesc, a.buysell, a.shares, a.priceper,
a.invested, a.receivable, a.clearing, sum (a.receivable-a.clearing) as net
FROM
open_blot a, producttype b
WHERE a.prodtype = b.productype AND
a.prodtype in ('11','12') AND
a.traddate > = '01/01/2009'
group by 1,2,3,4,5,6,7,8,9,10

MJL,

Many thanks! That was the trick/key! Please send me your address, so I can mail you a Christmas present.

glad to help, have a great day!!