> Two thoughts:
>
> 1. Looking at your view definition, where are the duplicates coming from?
Which ever select is second in the definition
> Since both selects come from the same two tables and one SELECT has WHERE
> T2.Billto = 'M' and the other has WHERE T2.Billto = '0', I can't see how
> there would be duplicates ACROSS the SELECTS. Therefor, why not use DISTINCT
> in each SELECT?
It is easy enough to eliminate the duplicates by not including "all"
with the union operator... I was looking for the cause... maybe the
way I assembled the view, or a limitation with unions I was unaware
of.
> 2. Since the two selects are from the same two tables, why not use ONE
> Where clause and ONE select? Is it too slow?
You're right. I run into "out of expression space" errors often
enough that I do this habitually now. And since forms and, I think,
reports affect available expression space, this just seems "safest",
since testing at the R> doesn't _always_ tell the tale. Boolean
expressions have a significant impact on performance, but I should
try and compare the two methods.
Thanks David,
Ben Petersen
> ----- Original Message -----
> From: "Ben Petersen" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, September 09, 2002 10:50 AM
> 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/
