Re: Re[2]: Union quries: INTERSECT, MINUS, etc
Love it classic execution of KISS, no bull it just works and quick and
efficient
Waiting for my plane home from Perth
Have a good weekend as its Friday night here
Cheers
--
=
Peter McLarty E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane, AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
"If people did not sometimes do silly things, nothing intelligent would
ever
get done."
- Ludwig Wittgenstein
=
Mincom "The People, The Experience, The Vision"
=
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please
delete it and notify the sender. The contents of this e-mail are the
opinion of the writer only and are not endorsed by the Mincom Group of
companies unless expressly stated otherwise.
Rachel Carmichael <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
26-07-2003 12:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Re[2]: Union quries: INTERSECT, MINUS, etc
okay, I answered this offlist but...
it started out as "do we have a problem, indicated by records in the
parent table with no children"
select id from parent
minus
select parentid from child
that identified that we had a problem.
next step (I'm a paranoid DBA when it comes to permanently deleting
data from production)
create holding_table
as select * from parent
where id in
(select id from parent
minus select parentid from child)
last step
delete from parent where id in select id from holding_table
elegant? no. Fast? yes. And when I'm doing this 10 minutes before I'm
supposed to leave for the day, fast is what I want :)
I'm a BIG believer in "plain vanilla" coding. Slick is fine, but if I
have to spend too much more time than the slick query saves me in
creating it, it's not worth it. This is a one-off.
--- Jonathan Gennick <[EMAIL PROTECTED]> wrote:
> Friday, July 25, 2003, 6:39:35 AM, you wrote:
> RC> not very slick but I used MINUS yesterday to find parents with no
> RC> children so as to purge them
>
> Offhand, I'd think you could do this without using MINUS.
> Maybe I'm wrong. But assuming there is a non-MINUS solution,
> what led you to choose to use MINUS?
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
>
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article,
> or send email to [EMAIL PROTECTED] and
> include the word "subscribe" in either the subject or body.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Gennick
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mail
Re: Re[2]: Union quries: INTERSECT, MINUS, etc
okay, I answered this offlist but... it started out as "do we have a problem, indicated by records in the parent table with no children" select id from parent minus select parentid from child that identified that we had a problem. next step (I'm a paranoid DBA when it comes to permanently deleting data from production) create holding_table as select * from parent where id in (select id from parent minus select parentid from child) last step delete from parent where id in select id from holding_table elegant? no. Fast? yes. And when I'm doing this 10 minutes before I'm supposed to leave for the day, fast is what I want :) I'm a BIG believer in "plain vanilla" coding. Slick is fine, but if I have to spend too much more time than the slick query saves me in creating it, it's not worth it. This is a one-off. --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > Friday, July 25, 2003, 6:39:35 AM, you wrote: > RC> not very slick but I used MINUS yesterday to find parents with no > RC> children so as to purge them > > Offhand, I'd think you could do this without using MINUS. > Maybe I'm wrong. But assuming there is a non-MINUS solution, > what led you to choose to use MINUS? > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit > http://four.pairlist.net/mailman/listinfo/oracle-article, > or send email to [EMAIL PROTECTED] and > include the word "subscribe" in either the subject or body. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Gennick > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: Union quries: INTERSECT, MINUS, etc
where will you publish this? Id like to read your results. > > From: Jonathan Gennick <[EMAIL PROTECTED]> > Date: 2003/07/25 Fri AM 10:04:25 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re[2]: Union quries: INTERSECT, MINUS, etc > > Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: > RC> not very slick but I used MINUS yesterday to find parents with no > RC> children so as to purge them > > Rachel, you make me very glad I have two kids. Please don't > purge me, ok? > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, > or send email to [EMAIL PROTECTED] and > include the word "subscribe" in either the subject or body. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Gennick > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: Union quries: INTERSECT, MINUS, etc
I'd purge myself first 'cause I don't have kids. Like the bad joke "After I kill myself I'm turning the gun on you" --- Jonathan Gennick <[EMAIL PROTECTED]> wrote: > Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: > RC> not very slick but I used MINUS yesterday to find parents with no > RC> children so as to purge them > > Rachel, you make me very glad I have two kids. Please don't > purge me, ok? > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit > http://four.pairlist.net/mailman/listinfo/oracle-article, > or send email to [EMAIL PROTECTED] and > include the word "subscribe" in either the subject or body. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Gennick > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Union quries: INTERSECT, MINUS, etc
Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: RC> not very slick but I used MINUS yesterday to find parents with no RC> children so as to purge them Rachel, you make me very glad I have two kids. Please don't purge me, ok? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Union quries: INTERSECT, MINUS, etc
Friday, July 25, 2003, 6:39:35 AM, you wrote: RC> not very slick but I used MINUS yesterday to find parents with no RC> children so as to purge them Offhand, I'd think you could do this without using MINUS. Maybe I'm wrong. But assuming there is a non-MINUS solution, what led you to choose to use MINUS? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: Union quries: INTERSECT, MINUS, etc
Thursday, July 24, 2003, 10:09:25 AM, Dave wrote: DP> Compared actual result set DP> with expected result set via minus. I've done that too. I need to search the cobwebs of my memory a bit, but I recall having use MINUS both ways to be sure: results MINUS expected_results tells you whether the real results included any rows that are unexpected expected_results MINUS results tells you whether results omitted any expected rows I seem to recall once having to use GROUP BY and COUNT to ensure that the *right quantity* of each row was in the result set. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
