01001 EDT NSG Nested set function with no GROUP BY

forum.sqlbase (2000-2010)
NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

01001 EDT NSG Nested set function with no GROUP BY

Post by NewsgroupServer » 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?

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

01001 EDT NSG Nested set function with no GROUP BY

Post by NewsgroupServer » 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;

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

01001 EDT NSG Nested set function with no GROUP BY

Post by NewsgroupServer » 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.

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

01001 EDT NSG Nested set function with no GROUP BY

Post by NewsgroupServer » 18 Feb 2009, 07:09

 Posted by:  F R Bhote 

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

Thank you very much.

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

01001 EDT NSG Nested set function with no GROUP BY

Post by NewsgroupServer » 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 [Crawler] and 0 guests