IBM Mainframe Forum

by **vegafacundodaniel** » Tue Oct 19, 2010 4:54 pm

Hello,

I need help please :

I need to make a query to count records (grouping by a field, for example "country") and compare them against the value of a field of a table.

Any idea ?

Thanks in advance !

by **Akatsukami** » Tue Oct 19, 2010 6:52 pm

Your request is not sufficiently coherent to give any definite answer.

Where will the records come from: a PS data set ("flat file")? an ESDS? a KSDS? an IMS or DB2 data base? (in which case the term "records" is incorrect; it should be "segments" or "rows").

by **GuyC** » Tue Oct 19, 2010 6:54 pm

something like these : (all different performance depending on #rows and indexes)?
non-correlated :

Select all

select * from
(select ctry,count(*) as cnt from tab1 group by ctry) A
, tab2 B
where A.ctry = B.ctry and A.cnt <> B.cnt

or
correlated

Select all

select * from
tab2 B
join table (select count(*) as cnt from tab1 A1 where A1.ctry = B.ctry ) A on 1=1
where A.cnt <> B.cnt

or
in where subselect (actual count(*) does not show up in result)

Select all

select * from
tab2 B
where B.cnt <> (select count(*) as cnt from tab1 A1 where A1.ctry = B.ctry )

IBM Mainframe Forum

Count records and compare with a field of a table

Count records and compare with a field of a table

Re: Count records and compare with a field of a table

Re: Count records and compare with a field of a table