Concatenation (Too Many Records)

[acct98]

I'm have a question. When I execute the following script I get too many records. In the F887rpt table there is only 20,000 records. However when I run the script below 20,100 are selected. I was only expecting to get 20,000 records. Can someone please tell me why I am getting more than 20,000 records and how to stop it?




SELECT C.*
FROM f887rpt F, nodup c
WHERE F.XORGN||F.SATSTN||F.BEGBUDFY||F.ENDBUDFY||F.FUND||F.XPROG||MONAMT||F.COSTORG||F.BOC||F.SUBOC||F.XDIVSN =
C.STATION||C.SAT_STN||C.BFY||C.EBFY||C.FUND||C.ACC||C.CMOBLIG||C.CC||C.BOC||C.SUBBOC||C.AO
and f.xprog = '010070301'
ORDER BY C.STATION, C.BFY, C.EBFY, C.FUND, C.ACC, C.CC,C.BOC,C.SUBBOC
/

 

[carp]

Are any of the fields you are concatenating set to null? That might give you a situation where stringing all of the fields together gives you multiple rows with the same value. For instance, if you have two rows with two fields (x and y) as follows:
x y
'H' <null>
<null> 'H'

concatenating x||y would give you:
x||y
'H'
'H'
and now you have duplicates where you thought everything was unique!

Try "deconcatenating" the values and use the IN construct:

SELECT *
    FROM  nodup
    WHERE 
    (STATION,SAT_STN,BFY,EBFY,FUND,ACC,CMOBLIG,CC,BOC,SUBBOC,AO)
IN 
(SELECT XORGN,SATSTN,BEGBUDFY,ENDBUDFY,FUND,XPROG,MONAMT,COSTORG,BOC,SUBOC,XDIVSN 
  FROM f887rpt
  WHERE xprog = '010070301')
ORDER BY STATION, BFY, EBFY, FUND, ACC, CC,BOC,SUBBOC;

or you might try just ANDing all of the conditions together (with calls to NVL if NULLs are the root of the problem).

 

[acct98]

Carp,

I tried your first suggestion but was unsuccessful.

Could you please explain your statement:
or you might try just ANDing all of the conditions together (with calls to NVL if NULLs are the root of the problem).

 

[acct98]

Is it possible to utilize and outer join with concatenated fields?

 

[acct98]

I think if found the problem it lies inside a cobol dataset.