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/
