logs archiveIRC Archive / Freenode / #oracle / 2010 / January / 23 / 1
CBro2007
is it possible to do a GROUP BY in Oracle using a PARTITION BY analytical clause instead?
was looking at the new recursive subquery factoring WITH clause and it says that you cannot use a GROUP BY in the recursive block
which sucks big time!
:(
just wondering if there is another way of doing a group by
Sargun
whoa
Oracle is awesome, but scary
TheBonsai
yep..
sqcgirirxz
Hi all. It seems we are again seeing javascript based flood spam. If you are experiencing this, please do not click the links in the messages as they will cause you to repeat the spam. More information is available at http://peoplesprimary.com. Thanks!
blqcxksqcxh
sloth and jenk show is LIVE Theme: Hating on Haiti (877-419-7477) http://klulz.fm/listen.pls
pmutpudrwc
sloth and jenk show is LIVE Theme: Hating on Haiti (877-419-7477) http://klulz.fm/listen.pls
superlinux
hi
how can I let someone log-in to a schema from enterprise manager?
gambakufu, can you help please?
how can I let someone log-in to a schema from enterprise manager?
brb
Algo
ORACLE! YOU WILL! ATONE!
superos
How do I test if a cursor returns no rows?
         

superlinux
superos, have tried to find a function that returns the count of rows?
blaamann
superlinux: for status in status_cursor (someDate) loop
Some dates has a status, others not. I need to catch both.
Might be using a cursor is wrong for this.
I probably need to check for the existing before the loop.
superlinux
blaamann, i am just very newbie in oracle, mate. all I tried to do is guide based on my experience in mysql
OH! yes blaamann i get now
blaamann, as far as I remember , isn't the cursor like the row-pointer?
blaamann
superlinux: Not sure :-) But it sounds like it.
superlinux
ok then, cos I am not sure too I cannot help
blaamann
superlinux: Maybe we should not bother with computer on a saturday :-)
superlinux
hhh
well I have to finish someones project, and I will have to start teaching oracle and java soon
blaamann
superlinux: Working at a U?
superlinux
at some private lessons institute
in lebanon
and I am a masters in I.T from Australia
blaamann
Is Lebanon safe?
superlinux
yes
why?
currently is
generally depends
blaamann
Looks like a beautiful country (from what I've seen on TV).
superlinux
you may come anytime. just dont go deep in the sothern parts
well yeah
just stay on the coast and the mountains and the far north, dont go to the sothern borders
blaamann
(Action) looking at Google Maps
Does that mean everything north of Beirut?
to be safe
superlinux
I said all the coast
blaamann
cool
superlinux
blaamann, if a war happens like in 2006 you can be evicted easily
that's why i said to you such
so it will be an adventure
blaamann
Once I was in Nicosia I was invited to Beirut. A salesman I got to know said I should go with him.
I didn't, but I would like to go some day.
superlinux
i will be a way to meet different people. and when they say to you welcome to lebanon, or beirut, they mean it!
and mean it in a sracastic way
blaamann, if you want to know about how to travel over here, dont meet with taxi cabs . they will charge you alot
just take a hike away from the airport. for 3$ only or maybe up to ten dollars and go to any hotel in beirut
blaamann, Hamra street (hamra means the red thing)
this street is in the very heart of beirut
blaamann
I see some known international hotels in that street
         

superlinux
yes
and there is a good market place
all along the street
you can really feel home there, cos also is full of Uni students who most of know english in an acceptable level
so you won't be in trouble anyway
blaamann, take a cab for 2$ only we call it a service
it's just a short trip
blaamann
nice
(Action) has to do some housekeeping
CBro2007
does anyone know if I can change a "group by - having" sql statement to something using purely analytical functions in Oracle?
like a partition over ... etc
Xgc
CBro2007: Yes. If you SELECT only aggregated infomration over each partition, you can DISTINCT to collapse the calculated rows per partition.
CBro2007
Xgc: the problem I am having is that I am using the new recusive subquery factoring clause using WITH
and in there I have resrictions in the recursive block that say I am not allowed to use a GROUP BY or a DISTINCT
:(
Xgc
CBro2007: Normally, you'd see the aggregated values duplicated for each row in the partition. DISTINCT will remove that, if you don't select other non-aggregates.
CBro2007
hmm
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#i2077142
is there any other way without using a distinct?
Xgc
Right. You only asked about GROUP BY. DISTINCT is the alternative. If you can't use either, you might be in trouble. Can you wrap the entire mess (as a derived table)?
CBro2007: SELECT DISTINCT ... FROM (your recursive SQL here) v1 ...;
CBro2007
how do you mean as a derived table?
nah don't think that will work either
i didn't know this recursive SQL will be so restrictive in nature
Xgc
You should try.
CBro2007
the thing is that if the recursive block query will return duplicates then I am in trouble anyway
the outcome will be crap
as its going to go over quite some large data sets later on
can I paste the SQL?
on a pastebin I mean
does anyone have an idea of how I can use an analytical function to do a group by having and without using a distinct
maybe using first_value or something?
Python-Idle
with partition over
CBro2007
Yeah i know
but it seems to not suppress duplicate entries
I am looking for an alternative to using a group by clause
coz the recursive subquery factoring WITH clause doesn't allow a group by or a distinct in the recursive block
making it much harder... though it will allow analytics
Python-Idle
it seems you have a problem in design
if you take more elements into the partition, you might be able to reach uniqueness
is this possible with your base data?
Egyptian[laptop]
evening all
i have a fresh install of oracle rac on 10.2.0.1 and i need to upgrade it to 10.2.0.4.. i have already downloaded from metalink patchset p6810189 for x86_64 linux .. but the readme says i have to upgreade the clusterware first.. what is the patchset number for that?
or is it the same patchset ?
« prev next »