Different results Crystal SQL and same select in Oracle

[SonOfZeus]

Building a report based on tables in Crystal, I've encountered a discrepancy with Oracle (DataSource). Off an invoice I refer to the same code table multiple times to get potential descriptions for fields (the source field may be null) The problem I hit is that due to some fields being null, the report comes up blank due to complexity.

The interesting fact is that when I take the SQL (Crystal Creates) and paste it into an oracle stored procedure every thing appears (nulls where they should be) Has anyone encountered this before, there may be some flag in Crystal I miss, otherwise will have to manage stored procedures as well.

SELECT
INVOICE.INVOICE_NUMBER,
CODE_TABLE_CAMP_PROV.CODE_DESCRIPTION AS CAMP_PROV,
CODE_TABLE_CREW_VEH_PROV.CODE_DESCRIPTION AS CREW_VEH_PROV,
CODE_TABLE_LOADER_TYPE.CODE_DESCRIPTION AS LOADER_TYPE
FROM
INVOICE INVOICE,
CODE_TABLE CODE_TABLE_LOADER_TYPE,
CODE_TABLE CODE_TABLE_CREW_VEH_PROV,
CODE_TABLE CODE_TABLE_CAMP_PROV
WHERE
(INVOICE.COMPANY_ID = CODE_TABLE_LOADER_TYPE.COMPANY_ID (+)) AND
(INVOICE.LOADER_ASSET_TYPE_CODE = CODE_TABLE_LOADER_TYPE.CODE_VALUE (+)) AND
(INVOICE.COMPANY_ID = CODE_TABLE_CREW_VEH_PROV.COMPANY_ID (+)) AND
(INVOICE.CREW_VEH_PROV_TYPE_CODE = CODE_TABLE_CREW_VEH_PROV.CODE_VALUE (+)) AND
(INVOICE.COMPANY_ID = CODE_TABLE_CAMP_PROV.COMPANY_ID (+)) AND
(INVOICE.CAMP_PROV_TYPE_CODE = CODE_TABLE_CAMP_PROV.CODE_VALUE (+)) AND
( CODE_TABLE_CAMP_PROV.TABLE_ID = 'PROV_TYPE' OR
CODE_TABLE_CAMP_PROV.TABLE_ID IS NULL
) AND
( CODE_TABLE_CREW_VEH_PROV.TABLE_ID = 'PROV_TYPE' OR
CODE_TABLE_CREW_VEH_PROV.TABLE_ID IS NULL
) AND
( CODE_TABLE_LOADER_TYPE.TABLE_ID = 'ASSET_TYPE' OR
CODE_TABLE_LOADER_TYPE.TABLE_ID IS NULL
) AND
INVOICE.INVOICE_ID = 175

 

[Turkbear]

Hi,
Did you insure that the link type in the Crystal Report is specified as left outer join?

 

[SonOfZeus]

Yes, the links are all outer join. The issue is that taking the SQL from Crystal and putting it in an oracle stored procedure does return the one invoice.