Home » SQL & PL/SQL » SQL & PL/SQL » Help with tough query
Help with tough query [message #235734] Mon, 07 May 2007 15:45 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
I want to build a query that only gives me the rows with error msg: "Case record containing case external....", but they have to be within the group selection (look at attached doc)...as you can see there are two other messages that go with the grouping of the process_msg column, but I don't need them in the result.

I don't need any other rows if they don't have all three messages as shown in doc.

Thanks,
Mark S.

  • Attachment: sql.doc
    (Size: 61.50KB, Downloaded 440 times)
Re: Help with tough query [message #235735 is a reply to message #235734] Mon, 07 May 2007 15:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do something like:
with
  data as (
    select process_msg, local_err_msg,
           count(*) over (partition by process_msg) cnt
    from mytable
  )
select process_msg, local_err_msg
from data
where cnt = 3 and local_err_msg = 'Case record containing case external...'
/

Regards
Michel
Re: Help with tough query [message #236036 is a reply to message #235734] Tue, 08 May 2007 14:08 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
With a little modification it works well. I never used;
WITH Data as(....cool stuff.

Thanks again!!

Always a learning experience here!

Mark S.
Re: Help with tough query [message #236090 is a reply to message #236036] Wed, 09 May 2007 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Well, in this particular case, (in my opinion), it doesn't really make sense.
Normally you would use this if you use the query in the 'with-clause' multiple times.
This is simply an inline view.
Re: Help with tough query [message #236101 is a reply to message #236090] Wed, 09 May 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, it is just a style of writting.
I think it is clearler that way than with with inline query.
I recently talked about this with Tom Kyte.
He prefers this way:
select process_msg, local_err_msg
from (
select process_msg, local_err_msg,
       count(*) over (partition by process_msg) cnt
from mytable
     )
where cnt = 3 and local_err_msg = 'Case record containing case external...'
/

Building the query writting inner code then adding a bit above and a bit below and so on.
For myself, I find this harder to read above all when there are multiple embedded queries.
I prefer writing inner code, put it in "with", using what is in "with" to build the next query and so on.
Every one can choose his way.

Regards
Michel

Re: Help with tough query [message #236293 is a reply to message #236101] Wed, 09 May 2007 08:52 Go to previous message
marks20101
Messages: 74
Registered: May 2005
Member
Thanks guys for the insight. Point well taken!

Mark S.
Previous Topic: difference between two timestamps
Next Topic: working with dates
Goto Forum:
  


Current Time: Thu Dec 08 04:33:06 CST 2016

Total time taken to generate the page: 0.10137 seconds