Query - count records

Hi,

I wanted to compare tables of two libraries on the AS/400 which I believe should be identical. I was wanting to write a query that would either count the number of records in each table or compare each respective table in the two libraries to check if they have the same number of records.

If anyone could suggest any help, I would really appreciate it.

DSPFD will give you that count with no programming. Just go to the bottom of the display (page down 3-4 screens and you'll have the file size, number of records and number of deleted records.

Source Creation Last Change
Member Size Type Date Date Time Records
F4211 9525596160 03/22/00 10/28/04 10:36:01 7354556
Text:
Total number of members . . . . . . . . . : 1
Total number of members not available . . : 0
Total records . . . . . . . . . . . . . . : 7354556
Total deleted records . . . . . . . . . . : 0
Total of member sizes . . . . . . . . . . : 9525596160

You could use command DSPFD this would tell you the number of records in the
file.



_____

From: [email protected] [mailto:[email protected]] On
Behalf Of rousav
Sent: Thursday, October 28, 2004 10:47 AM
To: [email protected]
Subject: Query - count records



Hi,

I wanted to compare tables of two libraries on the AS/400 which I believe
should be identical. I was wanting to write a query that would either count
the number of records in each table or compare each respective table in the
two libraries to check if they have the same number of records.

If anyone could suggest any help, I would really appreciate it.

_____


The entire <http://www.jdelist.com/ubb/showflat.php?Cat=&Board=> JDELIST
thread is available for viewing.


This is the JDELIST EnterpriseOne Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here <http://www.jdelist.com/unsubscr.shtml> .
JDELIST is not affiliated with JDEdwardsR.

If you are familiar with Query 400, you can write a quick unmatched records query & compare the contents of the tables rather than merely performing a record count. However, a simple SQL statement also counts the records ... Count * from library/filename
Heather

Hi,

Not sure if this is what you want, but this is my 2 cents worth...

This is quite an interesting issue which I can use in future; so I decided to try this. I found a similar SQL at: -
http://www.sitepoint.com/forums/archive/index.php/t-5731.html

I tested it on the F0101 since I have a few copies of it in many libraries. I ran: -

DSPFD FILE(*ALL/F0101) TYPE(*MBR) OUTPUT(*OUTFILE)
OUTFILE(QGPL/DIFF0101)

The resulting Physical File, DIFF0101, had field names that started with MB. I used the F4 Prompt key in STRSQL to see which fieldnames I wanted. These were 'file', 'lib', and 'nrcd' (No of records in a member).

Then I ran this SQL: -

SELECT
a1.mbfile, a1.mblib, a1.mbnrcd,
a2.mbfile, a2.mblib, a2.mbnrcd
FROM
diff0101 as a1, diff0101 as a2
WHERE ( (a1.mbnrcd = a2.mbnrcd) and (a1.mblib <> a2.mblib) )


The (a1.mblib <> a2.mblib) is to exclude the same library from appearing twice.

Note 1) You will notice that the SQL is not perfect since there will be 2 records for the same 'nrcd'. It will be more, if there are Zero records.

** CAUTION !!! **
*** Note 2) SQL can HANG your system, if not properly coded. This one did not hang my system, probably because I only wanted to check one file, F0101. It probably would hang if I carried on, and did coding to check for all the files in each library. ***

...DLK

Rousav,

Are these two libraries part of your EnterpriseOne Architecture? Another words, do you have a Database DataSource that points to these two libraries? If so there is a "Record Count" report in EnterpriseOne that allows you to compare record counts between two datasources. I am not sure of the report ID, but if you go to "Work with Batch Versions", you can find it by doing a search on *Table* or *Count*.
If you do not have these setup as Database DataSource in EnterpriseOne and plan to frequent count checks, then I would set up these datasources to utilize the report, even if they are not associated to any Environment. No harm no foul...

PeterL

Thanks guys for your help. It helped.