Re: Computed columns

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/02/24
Message-ID: <6ctv0m$kcb$3@news00.btx.dtag.de>#1/1

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

;

>I am trying to create some computed columns as part of a select statement:
>
>SELECT DISTINCT ("qualified_recipient"."response_media_type"),
> gbramt = (SELECT SUM("qualified_recipient"."extended_price") FROM
>"printfile", "qualified_recipient" WHERE ("printfile"."printfileid" =
>"qualified_recipient"."printfileid") and ("printfile"."catalogfile" =
>'GBR')),
> hbramt = (SELECT SUM("qualified_recipient"."extended_price") FROM
>"printfile", "qualified_recipient" WHERE ("printfile"."printfileid" =
>"qualified_recipient"."printfileid") and ("printfile"."catalogfile" =
>'HBR')),
> ciamt = (SELECT SUM("qualified_recipient"."extended_price") FROM
>"printfile", "qualified_recipient" WHERE ("printfile"."printfileid" =
>"qualified_recipient"."printfileid") and ("printfile"."catalogfile" =
>'CI')),
> sspamt = (SELECT SUM("qualified_recipient"."extended_price") FROM
>"printfile", "qualified_recipient" WHERE ("printfile"."printfileid" =
>"qualified_recipient"."printfileid") and ("printfile"."catalogfile" =
>'SSP')),
> kbamt = (SELECT SUM("qualified_recipient"."extended_price") FROM
>"printfile", "qualified_recipient" WHERE ("printfile"."printfileid" =
>"qualified_recipient"."printfileid") and ("printfile"."catalogfile" =
>'KB')),
> gbrcount = (SELECT COUNT("printfile"."printfileid") FROM
>"printfile" WHERE ("printfile"."catalogfile" = 'GBR')),
> hbrcount = (SELECT COUNT("printfile"."printfileid") FROM
>"printfile" WHERE ("printfile"."catalogfile" = 'HBR')),
> cicount = (SELECT COUNT("printfile"."printfileid") FROM
>"printfile" WHERE ("printfile"."catalogfile" = 'CI')),
> sspcount = (SELECT COUNT("printfile"."printfileid") FROM
>"printfile" WHERE ("printfile"."catalogfile" = 'SSP')),
> kbcount = (SELECT COUNT("printfile"."printfileid") FROM
>"printfile" WHERE ("printfile"."catalogfile" = 'KB'))
> FROM "printfile",
> "qualified_recipient"
>
>I keep getting the error FROM keyword not expected where found ORA-00923.
>
>This same SQL compiles fine against SQL Server and Sybase. Does anyone know
>the proper Oracle syntax?
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de

Received on Tue Feb 24 1998 - 00:00:00 CST