Losing records because of NULLs

general discussion
1

When I do a report on our header table, I get 34 pages of records.
When I add a field from our payment table, the report only had 5 pages.

It is becasue the field on the payment table has NULLs.
I am trying to add a CASE statement to the SQL so I get my 34 pages it is supposed to get.

But I am getting a syntax error on the following:
SELECT
HDR.PAY_ENTITY,
ltrim(HDR.VENDOR_NBR),
HDR.DOC_ID,
HDR.DOC_DATE,
HDR.STATUS,
CASE PAY.REF_NBR WHEN NULL THEN 0 ELSE PAY.REF_NBR END
FROM HDR, PAY
WHERE
( PAY.VENDOR_NBR=HDR.VENDOR_NBR )
AND (PAY.PAY_ENTITY=HDR.PAY_ENTITY )
AND (
ltrim(HDR.VENDOR_NBR) = @variable(‘Enter Vendor Number:’)
)

dvandeus :us: (BOB member since 2006-04-20)

2

Your question is related to report design, not Data Integrator jobs design. I will move your post.

dnewton :us: (BOB member since 2004-01-30)

3

You would need to make the join between the two tables an outer join. This will allow you to get all the header records even if there are no matching payment records.

jwhite9 :us: (BOB member since 2006-07-28)