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/
