01001 EDT NSG Nested set function with no GROUP BY

forum.sqlbase (1998-2010)
FRBhote
India
Posts: 1231
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

01001 EDT NSG Nested set function with no GROUP BY

Post by FRBhote » 17 Feb 2009, 12:08

 Posted by:  F R Bhote 

I want the sum of QTY * highest RATE from the below. I should get 2 * 20 =
40. This is of course a very simplified version of what I want.

CREATE TABLE QTYS ( QTY NUMBER );
INSERT INTO QTYS VALUES ( 2 );

CREATE TABLE RATES (RATE NUMBER);
INSERT INTO RATES VALUES ( 10);
INSERT INTO RATES VALUES ( 20);

COMMIT;

SELECT SUM( QTY * MAX( RATE ) )
FROM QTYS, RATES;

01001 EDT NSG Nested set function with no GROUP BY

Can someone help me please?

Jim McNamara

01001 EDT NSG Nested set function with no GROUP BY

Post by Jim McNamara » 17 Feb 2009, 14:53

 Posted by:  Jim McNamara 

I think SQLBase might protest, but you can do this in Oracle:

select sum(qty * (select max(rate_ from rates)) from qtys;

TerryP
Australia
Posts: 45
Joined: 06 Mar 2017, 22:41
Location: Australia

01001 EDT NSG Nested set function with no GROUP BY

Post by TerryP » 17 Feb 2009, 22:44

 Posted by:  Terry Phythian 

In SQL Base, you'll have to do it using a view:

create view maxrates (maxrate) as select max(rate) from rates;

select sum ( qty * maxrate ) from qtys, maxrates;

Cheers,
Terry.

FRBhote
India
Posts: 1231
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

01001 EDT NSG Nested set function with no GROUP BY

Post by FRBhote » 18 Feb 2009, 07:09

 Posted by:  F R Bhote 

Now why didn't I think of that???

Thank you very much.

TerryP
Australia
Posts: 45
Joined: 06 Mar 2017, 22:41
Location: Australia

01001 EDT NSG Nested set function with no GROUP BY

Post by TerryP » 18 Feb 2009, 22:53

 Posted by:  Terry Phythian 

No problems,

Though I don't know if/how well it will work once you add the complexity you
need back into the view and/or query.

SQL Base isn't overly quick or flexible when it comes to using views.

Anyhow, good luck,
Terry.

Return to “forum.sqlbase”

Who is online

Users browsing this forum: [Ccbot] and 0 guests