Bizarre problem with embedded SQL in COBOL program

[JonathanFuller]

I am working on a display program. I am getting a count of the records to be displayed for the selection using the SQL statement below. all selections work fine except if SRCH-MM is 2 or SRCH-StatusCD is 5. In these two cases the count comes back as 0 even though there is data. The later fetch statement work fine even in these two cases but this count select statement returns a 0 count. The SQLCODE after the statement is 0 so it is finding records it just doesn't seem to be counting them. Exec SQL Select Count(*) into :ROWS-IN-CURSOR from HWEBFP0700 as ActRec join HWEBFP0701 as DtlRec On (ACTREC.RESP = DTLREC.RESP) and (ActRec.SSN3 = DtlRec.SSN3) and (ActRec.SSN1 = DtlRec.SSN1) and (ActRec.SSN2 = DtlRec.SSN2) and (ActRec.COBSTCY = DtlRec.COBSTCY) and (ActRec.COBSTMO = DtlRec.COBSTMO) and (ActRec.ASEQNO = DtlRec.ASEQNO) where (ActRec.RESP = :SRCH-RESP or :SRCH-RESP = " ") and (ActRec.SSN3 = :SRCH-SSN3 or :SRCH-SSN3 = " " or :SRCH-SSN3 = "0000") and (ActRec.SSN1 = :SRCH-SSN1 or :SRCH-SSN1 = " " or :SRCH-SSN3 = "000") and (ActRec.SSN2 = :SRCH-SSN2 or :SRCH-SSN2 = " " or :SRCH-SSN3 = "00") and (ActRec.COBSTCY = :SRCH-CY or :SRCH-CY = 0) and (ActRec.COBSTMO = :SRCH-MM or :SRCH-MM = 0) AND (ActRec.DLTFLAG <> "Y" or :SRCH-DLT = "Y") and (DtlRec.EligCode = :SRCH-Eligcode or :SRCH-EligCode = " ") and (DtlRec.StatusCD = :SRCH-StatusCD or :SRCH-StatusCD = " ")

[efnkay]

Re:Bizarre problem with embedded SQL in COBOL program

I suggest that you run a test by selecting the data columns to see if there are in fact rows returned by the "Select ActRec.*,DtlRec.* ". Could be that count(*) is telling the truth...Good luck.

[RingerSoftware]

Re:Bizarre problem with embedded SQL in COBOL program

You mean you're having trouble debugging that giant unreadable SQL statement? ) Let's line it up better and see if anything jumps out... (ActRec.SSN1 = :SRCH-SSN1 or :SRCH-SSN1 = " " or :SRCH-SSN3 = "000") and (ActRec.SSN2 = :SRCH-SSN2 or :SRCH-SSN2 = " " or :SRCH-SSN3 = "00") Chris

[JonathanFuller]

Re:Bizarre problem with embedded SQL in COBOL program

Thanks to you both for your attention. However I don't think that either of these things answers the issue. The SQLCODE returned by the statement is 0 which means that it did indeed complete normally and returned records. I have also copied the statement as close to verbatim as possible into interactive SQL and had no problem getting a count that way. Additionally the same statement is used in the define cursor statment and the fetch works just perfectly. There are records returned for sure and it is seen in all ways except that the value returned for the COUNT(*) does not work. I will try changeing the COUNT(*) to a count (actrec.*) as you suggested and see if that makes any difference. Thanks again, Jonathan

[RingerSoftware]

Re:Bizarre problem with embedded SQL in COBOL program

Look at my other post. You apparently have a typo (copy/paste?) in your WHERE clause. Replace the first SRCH-SSN3 with SRCH-SSN1 and the other SRCH-SSN3 with SRCH-SSN2. In other words, when would SRCH-SSN3 be 3 or 2 zeros? (never). Chris

[JonathanFuller]

Re:Bizarre problem with embedded SQL in COBOL program

Thanks, That was it.