Re: Computed columns

On Tue, 24 Feb 1998 08:16:01 -0500, "Tim Hatton" <thatton_at_crtvsoft.com> wrote: Hi,

now it's a bit more clear what you want to do:

What you want is some pevoting.

So try:

SELECT

	 q.RESPONSE_MEDIA_TYPE	,
	SUM(DECODE (p.CATALOGFILE, 'GBR', q.extended_price, 0)) gbramt, 
	SUM(DECODE (p.CATALOGFILE, 'HBR', q.extended_price, 0) ) hbramt, ...

	SUM(DECODE(p.CATALOGFILE, 'GBR', 1, 0)) gbrcount,
	SUM(DECODE(p.CATALOGFILE, 'HBR', 1, 0)) hbrcount, ....
FROM 
	PRINTFILE p,
	QUALIFIED_RECIPIENT q
WHERE 	 
	p.PRINTFILEID = q.PRINTFILEID 

>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
>>;
>>
>>
>>
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de