Alastair,
I started using "all" when I discovered that I'd made an error in a
view construct (forgot to remove a table from the "from" clause).
Couldn't understand why the view was soooo slow. Turned out
RBase was eliminating something like 180,000 duplicates because
of my error (given that, it wasn't so slow after all <g>)
Using "all" checks my work, and I assume, relieves RBase of the
overhead of checking for duplicates. I have to move on, so I'll
eliminate "all" from the definition.
Thanks!
Ben Petersen
On 10 Sep 2002, at 7:23, Alastair Burr wrote:
> Ben,
>
> I've check your command and it looked right to except for one thing. I do
> much the same myself - the one union that I use regularly - but I do not use
> the ALL clause.
>
> So I looked at R:Syntax and this is what it says:
>
>
> This optional operator combines the results of two SELECT commands or
> clauses, displaying the results of the second SELECT command below those of
> the first. By default, UNION deletes duplicate rows. Include the optional
> keyword ALL to include duplicate rows in the final result. You cannot
> combine sub-SELECT commands using UNION.
>
> The UNION operator requires the following three conditions:
>
> The SELECT statements must specify an equal number of columns.
>
> Columns that are being combined must have the same data type.
>
> Only the last SELECT statement can contain an ORDER BY clause.
>
>
> You'll see that is specifies to use ALL to OBTAIN duplicates - in my command
> I get (as far as I know) all the rows I want without the ALL. I think you
> should be getting the correct results without it.
>
> Hope this helps,
> Regards,
> Alastair.
>
>
> ----- Original Message -----
> From: "Ben Petersen" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, September 09, 2002 4:50 PM
> Subject: Re: Unexplained Dups in Union/View
>
>
> Hi Albert and Alastair,
>
> Here's the rest of the story. I reduced the view to two selects. Each
> should return 250 rows. When executed independently of the view
> they return the correct count(*). When included in the view the first
> returns the correct count, the second 499; doesn't matter _which_
> is first . I used literals in the view ('d' and 'c') to identify the source
> of the row. The view definition is below.
>
> Of course, union, rather than union all, eliminates the dups. But
> this is just a smallish test DB... in actual use there would generally
> be several thousand rows generated. I'd like to avoid the
> performance hit.
>
> Thanks,
>
> Ben Petersen
>
>
> CREATE TEMP VIEW ar_recurLines +
> (track, BillID, RefID, ArAcct, AcctRef, BillingCode, TranDesc,
> Quantity, Unitcost, LineTotal, TranID, Serial) +
> AS SELECT +
> 'd', +
> T1.OfficeID, +
> T1.MemberID, +
> T2.aracct, +
> T2.AcctRef, +
> (CTXT(T1.billingcode)), +
> T2.TranDesc, +
> T2.Quantity, +
> T2.unitcost, +
> T2.LineTotal, +
> 0, +
> T2.TranSerial +
> FROM +
> Memstats T1, +
> ar_recurbill T2 +
> WHERE +
> T1.billingcode = T2.billingcode AND +
> T2.billto = 'O' AND +
> T2.summarize = 'N' AND +
> T1.mbrstatus IN ('A', 'P') +
> UNION ALL +
> SELECT +
> 'c', +
> T1.MemberID, +
> T1.OfficeID, +
> T2.aracct, +
> T2.AcctRef, +
> (ctxt(T1.billingcode)), +
> T2.TranDesc, +
> T2.Quantity, +
> T2.unitcost, +
> T2.LineTotal, +
> 0, +
> T2.TranSerial +
> FROM +
> Memstats T1, +
> ar_recurbill T2 +
> WHERE T1.billingcode = T2.billingcode AND +
> T2.billto = 'M' AND +
> T1.mbrstatus IN ('A', 'P')
>
>
>
>
>
> On 7 Sep 2002, at 3:56, Albert Berry wrote:
>
> > Alastair - make sure that ALL the columns in the 4 selects are correctly
> linked. The slightest difference will cause a UNION ALL to produce what
> appears to be a duplicate row, even when it is not.
> >
> > I think we list fellers would need to see the exact code to have a proper
> idea of what might be going wrong.
> >
> >
> > "Ben Petersen" <[EMAIL PROTECTED]> wrote:
> >
> > >Alastair,
> > >
> > >Well I "know" <g> I don't have duplicate columns in my source
> > >tables. And, like I said, the select in question, _independent_ of
> > >the view, only returns single instances of each row. I think that
> > >eliminates the possibility of more common columns than the where
> > >clause accounts for.
> > >
> > >I have to assume I've done something wrong, but I can't see it. I've
> > >rebuilt the view piecemeal... torn the select apart... it's been a long
> > >_couple days_. I'd post the view, but it's a bit much. Also, it's odd
> > >that one row does not duplicate, in this select and one other as
> > >well when it did the same thing.
> > >
> > >Thanks,
> > >
> > >Ben
> > >
> > >
> > >On 7 Sep 2002, at 6:21, Alastair Burr wrote:
> > >
> > >> I'm not a union man myself <g>, Ben, but my guess is that you've got
> more
> > >> common columns than you think. I presume that you really haven't got
> > >> duplicate data in the table! (It can be so easy to "know" what you've
> > >> got --- until you check it with your eyes open.)
> > >>
> > >> Regards,
> > >> Alastair.
> > >>
> > >>
> > >> ----- Original Message -----
> > >> From: "Ben Petersen" <[EMAIL PROTECTED]>
> > >> To: <[EMAIL PROTECTED]>
> > >> Sent: Friday, September 06, 2002 5:06 PM
> > >> Subject: Unexplained Dups in Union/View
> > >>
> > >>
> > >> > Hi All,
> > >> >
> > >> > I have a view that is a series of 4 'union all' selects. One, and
> > >> > sometimes 2, duplicate all but 1 row. So, instead of 250 rows being
> > >> > returned, 499 are. If I issue the select independently I get the
> > >> > correct result. By rearranging the unions one select stopped
> > >> > duplicating (it became the first select).
> > >> >
> > >> > I included a literal in the selects so I could clearly identify what
> was
> > >> > happening and did counts before and after to be sure I wasn't
> > >> > getting combinations from prior selects. I know I can use UNION
> > >> > rather then UNION ALL, but I don't want the performance hit of
> > >> > RBase trying to remove the dups (after it creates them????). I do
> > >> > this routinely and can't figure out what might be going on in this
> > >> > case.
> > >> >
> > >> > Of course, I've made sacrifices to the DB Gods, bloodied the wall
> > >> > next to my desk with my forehead, cursed, cried, and laughed
> > >> > maniacally... all for not. Has anyone else experienced this (the
> > >> > duplicating 'union all sel', that is <g>)?
> > >> >
> > >> > tia,
> > >> >
> > >> > Ben Petersen
> > >> >
> > >> > ================================================
> > >> > TO SEE MESSAGE POSTING GUIDELINES:
> > >> > Send a plain text email to [EMAIL PROTECTED]
> > >> > In the message body, put just two words: INTRO rbase-l
> > >> > ================================================
> > >> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > >> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > >> > ================================================
> > >> > TO SEARCH ARCHIVES:
> > >> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >>
> > >> ================================================
> > >> TO SEE MESSAGE POSTING GUIDELINES:
> > >> Send a plain text email to [EMAIL PROTECTED]
> > >> In the message body, put just two words: INTRO rbase-l
> > >> ================================================
> > >> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > >> In the message body, put just two words: UNSUBSCRIBE rbase-l
> > >> ================================================
> > >> TO SEARCH ARCHIVES:
> > >> http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >>
> > >
> > >
> > >================================================
> > >TO SEE MESSAGE POSTING GUIDELINES:
> > >Send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: INTRO rbase-l
> > >================================================
> > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: UNSUBSCRIBE rbase-l
> > >================================================
> > >TO SEARCH ARCHIVES:
> > >http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >
> >
> >
> > --
> > Albert Berry
> > Full Time Consultant to
> > PSD Solutions
> > 350 West Hubbard, Suite 210
> > Chicago, IL 60610
> > 312-828-9253 Ext. 32
> >
> >
> > __________________________________________________________________
> > The NEW Netscape 7.0 browser is now available. Upgrade now!
> http://channels.netscape.com/ns/brows
> ers/download.jsp
> >
> > Get your own FREE, personal Netscape Mail account today at
> http://webmail.netscape.com/
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
>
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
