Home » SQL & PL/SQL » SQL & PL/SQL » very complex group by challange
very complex group by challange [message #185942] Fri, 04 August 2006 07:31 Go to next message
vaibhavbhawsar
Messages: 1
Registered: August 2006
Location: india
Junior Member
Hi frenz,

I have a select query structured as follows:




Select col1, col2, .........................

from table1
inner join table2
..
..
.
.
group by col1, col2,..................


In the col list col1, col2 ... and onwards, i need to implement some logic which i have done using SELECT CASE and works perfect so it creates a new column based on the select query but the col used in this new select query needs to be added in the list of columns specified in the group by .....after doin this other aggregates for other colmns and also many unnecessary rows get generated...

so wht i want is to keep the output of the select query with my logic in the above query without making it a part of group by, and an important thing to NOTE here is that the select query which acts as a column is running in syncronisation with outer query.... i.e. the where clause of select query which acts as col matches against a column of outer query.

Kindly suggest me some probable solutions for rectifying this problem.


Re: very complex group by challange [message #185944 is a reply to message #185942] Fri, 04 August 2006 07:40 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You need to do 3 things

1. Supply create table scripts.
2. Supply insert statements
3. Supply what you ahve already tried (not in pseudo code)
4. ... we need 4 things
Supply what results you expect to be returned.
5. .... we need 5 things
Supply any error messages that you might be getting from your attempts

Jim


PS a beer to anyone who gets the Joke reference Very Happy
Re: very complex group by challange [message #185946 is a reply to message #185944] Fri, 04 August 2006 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Among the things we need are such elements as table scripts, insert statments....oh sod it - I'll come in again.


Beer?
Re: very complex group by challange [message #185947 is a reply to message #185946] Fri, 04 August 2006 07:56 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Applause Applause. I had a feeling you might get it Smile
Next time you're in Glasgow (sunny or not) I'll gladly shout you beer or 2.
Jim
Re: very complex group by challange [message #185949 is a reply to message #185944] Fri, 04 August 2006 08:00 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
JSI2001 wrote on Fri, 04 August 2006 08:40



PS a beer to anyone who gets the Joke reference Very Happy


The 5 book Trilogy of the Hitchiker's Guide To The Galaxy series?
Re: very complex group by challange [message #185950 is a reply to message #185949] Fri, 04 August 2006 08:16 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

The 5 book Trilogy of the Hitchiker's Guide To The Galaxy series?
Not deliberately Very Happy but then, I've never read them :shame: Embarassed
Re: very complex group by challange [message #186182 is a reply to message #185950] Sun, 06 August 2006 20:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In case @JRowbottom's casual (yet witty) allusion wasn't enough for the average punter...


In the early years of the 16th century, to combat the rising tide of religious unorthodoxy, the Pope gave Cardinal Ximinez of Spain leave to move without let or hindrance throughout the land, in a reign of violence, terror and torture that makes a smashing film. This was the Spanish Inquisition...
--------------------------------------------------------------------------------

Chapman: Trouble at mill.
Cleveland: Oh no - what kind of trouble?
Chapman: One on't cross beams gone owt askew on treadle.
Cleveland: Pardon?
Chapman: One on't cross beams gone owt askew on treadle.
Cleveland: I don't understand what you're saying.
Chapman: [slightly irritatedly and with exaggeratedly clear accent] One of the cross beams has gone out askew on the treadle.
Cleveland: Well what on earth does that mean?
Chapman: *I* don't know - Mr Wentworth just told me to come in here and say that there was trouble at the mill, that's all - I didn't expect a kind of Spanish Inquisition.

[JARRING CHORD]

[The door flies open and Cardinal Ximinez of Spain [Palin] enters, flanked by two junior cardinals. Cardinal Biggles [Jones] has goggles pushed over his forehead. Cardinal Fang [Gilliam] is just Cardinal Fang]

Ximinez: NOBODY expects the Spanish Inquisition! Our chief weapon is surprise...surprise and fear...fear and surprise.... Our two weapons are fear and surprise...and ruthless efficiency.... Our *three* weapons are fear, surprise, and ruthless efficiency...and an almost fanatical devotion to the Pope.... Our *four*...no... *Amongst* our weapons.... Amongst our weaponry...are such elements as fear, surprise.... I'll come in again.

[The Inquisition exits]


more
Previous Topic: EXCEPTION Woes?
Next Topic: how to get the Max
Goto Forum:
  


Current Time: Thu Dec 08 10:06:10 CST 2016

Total time taken to generate the page: 0.09535 seconds