Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    19

    Unanswered: Aliasing column in a view,

    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

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    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

  3. #3
    Join Date
    May 2009
    Posts
    19
    MJL,

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

  4. #4
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    glad to help, have a great day!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •