RE: SQL Question - Merging files or result sets?

[Alan Shore <AlanShore@xxxxxxxx>]

Try this

with combine as (select 'FILEA' as orgFile, a.* from fileA a
                 union all
                 select 'FILEB' as orgFile, b.* from fileB b)
select count(*) from combine



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 04:47:48 PM:

> Alan,
>
> I was wondering if using a union on two files if I can construct a single
> count(*) SQL command to count the total records from both files.
>
> Bradley V. Stone
> BVSTools - www.bvstools.com
> eRPG SDK - www.erpgsdk.com
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Shore
> > Sent: Thursday, March 13, 2008 3:43 PM
> > To: Midrange Systems Technical Discussion
> > Subject: RE: SQL Question - Merging files or result sets?
> >
> >
> >
> > Brad - could you re-post your question about the count as I don't seem
to
> > have received that.
> >
> >
> >
> > Alan Shore
> >
> > NBTY, Inc
> > (631) 244-2000 ext. 5019
> > AShore@xxxxxxxx
> > "If you're going through Hell, keep going" - Winston Churchill
> >
> >
> >
> >
> >              "Bradley V.
> >
> >              Stone"
> >
> >              <bvstone@bvstools
> >       To
> >              .com>                     "Midrange Systems
> > Technical
> >              Sent by:                  Discussion"
> >
> >              midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>
> >
> >              s@xxxxxxxxxxxx
> >       cc
> >
> >
> >
> >  Subject
> >              03/13/2008 04:37          RE: SQL Question - Merging
> > files or
> >              PM                        result sets?
> >
> >
> >
> >
> >
> >              Please respond to
> >
> >              Midrange Systems
> >
> >                  Technical
> >
> >                 Discussion
> >
> >              <midrange-l@midra
> >
> >                  nge.com>
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Well, I got everything working slick now except the count.  :)  Still
> > hoping
> > someone has ideas on that.
> >
> > Thanks to everyone for your help so far!
> >
> > Bradley V. Stone
> > BVSTools - www.bvstools.com
> > eRPG SDK - www.erpgsdk.com
> >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx
> > > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
> > > Sent: Thursday, March 13, 2008 3:31 PM
> > > To: Midrange Systems Technical Discussion
> > > Subject: RE: SQL Question - Merging files or result sets?
> > >
> > >
> > > Sure...
> > >
> > > select 'FILEA' as orgFile, a.* from fileA a
> > > union all
> > > select 'FILEB' as orgFile, b.* from fileB b
> > >
> > > Now the first field tells you the originating file.
> > >
> > > Charles Wilt
> > > Software Engineer
> > > CINTAS Corporation - IT 92B
> > > 513.701.1307
> > >
> > > wiltc@xxxxxxxxxx
> > >
> > >
> > > > -----Original Message-----
> > > > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > > > bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
> > > > Sent: Thursday, March 13, 2008 4:26 PM
> > > > To: Midrange Systems Technical Discussion
> > > > Subject: RE: SQL Question - Merging files or result sets?
> > > >
> > > > Ha...  one more question I'm guessing the answer is "no"..
> > but is there
> > > > any
> > > > way to know which file the record from the result set came from
> > > if it was
> > > > a
> > > > union?  :)
> > > >
> > > > Bradley V. Stone
> > > > BVSTools - www.bvstools.com
> > > > eRPG SDK - www.erpgsdk.com
> > > >
> > > > > -----Original Message-----
> > > > > From: midrange-l-bounces@xxxxxxxxxxxx
> > > > > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
> > > > > lgoodbar@xxxxxxxxxxxxxx
> > > > > Sent: Thursday, March 13, 2008 3:11 PM
> > > > > To: midrange-l@xxxxxxxxxxxx
> > > > > Subject: RE: SQL Question - Merging files or result sets?
> > > > >
> > > > >
> > > > > If you name the fields, you can substitute a literal for the
missing
> > > > > field.
> > > > >
> > > > > Select field1a as firstfield, '' as secondfield from file1
> > > > > Union all
> > > > > Select field2a, field2b from file2
> > > > > Order by 1
> > > > >
> > > > > Loyd Goodbar
> > > > > Senior programmer/analyst
> > > > > BorgWarner
> > > > > TS Water Valley
> > > > > 662-473-5713
> > > > >
> > > > > -----Original Message-----
> > > > > From: midrange-l-bounces@xxxxxxxxxxxx
> > > > > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley V.
> > Stone
> > > > > Sent: Thursday, March 13, 2008 15:08
> > > > > To: Midrange Systems Technical Discussion
> > > > > Subject: RE: SQL Question - Merging files or result sets?
> > > > >
> > > > > Ok, Darn.  Almost worked.  I found out the file in
> > production (fileB)
> > > > > does
> > > > > have one extra field at the end.
> > > > >
> > > > >  Message ID . . . . . . :   SQL0421       Severity . . . . .
> > > . . :   30
> > > > >  Message type . . . . . :   Diagnostic
> > > > >  Date sent  . . . . . . :   03/13/08      Time sent  . . . . . .
:
> > > > > 15:06:05
> > > > >
> > > > >  Message . . . . :   Number of UNION operands not equal.
> > > > >  Cause . . . . . :   The operands of a UNION or UNION ALL
> > > must have the
> > > > > same
> > > > >    number of columns.
> > > > >  Recovery  . . . :   Correct the SQL statement so that it has the
> > same
> > > > > number
> > > > >    of operands in each SELECT list.
> > > > >
> > > > > Is this telling me it won't work because the files don't have the
> > same
> > > > > number of coumns?
> > > > >
> > > > > For select I'm using select * and fetching into a DS defined
> > > externally
> > > > > by
> > > > > fileA.
> > > > >
> > > > > Bradley V. Stone
> > > > > BVSTools - www.bvstools.com
> > > > > eRPG SDK - www.erpgsdk.com
> > > > >
> > > > >
> > > > > --
> > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > > > mailing list
> > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > Before posting, please take a moment to review the archives
> > > > > at http://archive.midrange.com/midrange-l.
> > > >
> > > > --
> > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> > > > list
> > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/midrange-l.
> > >
> > >
> > >
> > > This e-mail transmission contains information that is intended to
> > > be confidential and privileged.  If you receive this e-mail and
> > > you are not a named addressee you are hereby notified that you
> > > are not authorized to read, print, retain, copy or disseminate
> > > this communication without the consent of the sender and that
> > > doing so is prohibited and may be unlawful.  Please reply to the
> > > message immediately by informing the sender that the message was
> > > misdirected.  After replying, please delete and otherwise erase
> > > it and any attachments from your computer system.  Your
> > > assistance in correcting this error is appreciated.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.