Re: Computed columns

Taking all your advice I changed the SQL to this:

SELECT DISTINCT q.RESPONSE_MEDIA_TYPE,

         (SELECT SUM(q.extended_price) FROM printfile p, qualified_recipient q WHERE (p.printfileid = q.printfileid) and (p.catalogfile = 'GBR')) gbramt,

         (SELECT SUM(q.extended_price) FROM printfile p, qualified_recipient q WHERE (p.printfileid = q.printfileid) and (p.catalogfile = 'HBR')) hbramt,

         (SELECT SUM(q.extended_price) FROM printfile p, qualified_recipient q WHERE (p.printfileid = q.printfileid) and (p.catalogfile = 'CI')) ciamt,

         (SELECT SUM(q.extended_price) FROM printfile p, qualified_recipient q WHERE (p.printfileid = q.printfileid) and (p.catalogfile = 'SSP')) sspamt,

         (SELECT SUM(q.extended_price) FROM printfile p, qualified_recipient q WHERE (p.printfileid = q.printfileid) and (p.catalogfile = 'KB')) kbamt,

         (SELECT COUNT(p.printfileid) FROM printfile p WHERE (p.catalogfile = 'GBR')) gbrcount,

         (SELECT COUNT(p.printfileid) FROM printfile p WHERE (p.catalogfile = 'HBR')) hbrcount,

         (SELECT COUNT(p.printfileid) FROM printfile p WHERE (p.catalogfile = 'CI')) cicount,

         (SELECT COUNT(p.printfileid) FROM printfile p WHERE (p.catalogfile = 'SSP')) sspcount,

         (SELECT COUNT(p.printfileid) FROM printfile p WHERE (p.catalogfile = 'KB')) kbcount

    FROM PRINTFILE p,

         QUALIFIED_RECIPIENT q
     WHERE ( p.PRINTFILEID = q.PRINTFILEID )

Now I am receiving an ORA 00936 Missing Expression error. I tried using the As syntax you suggested with the same result. Anything else I can try?

Thanks

Tim

>On Mon, 23 Feb 1998 22:42:38 -0500, "Tim Hatton" <thatton_at_crtvsoft.com>
 wrote:
>Hi,
>
>first of all I would use table aliases in your from clauses to get rid of
 these tons of literals.
>
>SELECT SUM(q.extended_priceq) FROM
>printfile p, qualified_recipient q WHERE (p.printfileid =
>q.printfileid) and (p.catalogfile ='GBR')),
>
>Second remove all your double quotes surrounding your table and column
 names.
>
>Third a computed column is named by putting the name behind the computed
 expression:
>
>SELECT DISTINCT q.response_media_type,
> SELECT
> SUM(q.extended_priceq)
> FROM
> printfile p, qualified_recipient q
> WHERE
> (p.printfileid = q.printfileid) and (p.catalogfile ='GBR')) gbramt,
> etc.
>FROM
> printfile p,
> qualified_recipient q
>;
>
>
>You can also use a more Access-like notation:
>
>SELECT DISTINCT q.response_media_type,
> SELECT
> SUM(q.extended_priceq)
> FROM
> printfile p, qualified_recipient q
> WHERE
> (p.printfileid = q.printfileid) and (p.catalogfile ='GBR')) as gbramt,
> etc.
>FROM
> printfile p,
> qualified_recipient q
>;
>
>
>
Received on Tue Feb 24 1998 - 00:00:00 CST