Oracle View and Crystal Reports

From: cory.swartz <cory.swartz_at_icmatls.com>
Date: 25 Apr 2007 06:37:01 -0700
Message-ID: <1177508221.020198.15620_at_r30g2000prh.googlegroups.com>

I apologize if I am in the wrong group, but I'm not sure if this deals more with the Oracle view or how Crystal is using the view I created. The view script is below:

---

Select

CMDSERIES.ORDR.CUST_CODE,
CMDSERIES.ORDR.CUST_NAME,
CMDSERIES.ORDR.PROJ_CODE,
CMDSERIES.ORDR.PRICE_PLANT_CODE,
CMDSERIES.ORDR.SLSMN_EMPL_CODE,
CMDSERIES.ORDR.ORDER_DATE,
CMDSERIES.ORDR.ORDER_CODE,
CMDSERIES.ORDL.ORDER_DATE,
CMDSERIES.ORDL.ORDER_CODE,
CMDSERIES.ORDL.PROD_CODE,
CMDSERIES.ORDL.PROD_DESCR,
CMDSERIES.ORDL.PRICE_QTY,
CMDSERIES.ORDL.PRICE,
CMDSERIES.PROJ.PROJ_CODE,
CMDSERIES.PROJ.PROJ_NAME,
CMDSERIES.PROJ.CA_SALES_ANL_CODE,
CMDSERIES.EMPL.EMPL_CODE,
CMDSERIES.EMPL.NAME,
CMDSERIES.PLNT.PLANT_CODE,
CMDSERIES.PLNT.NAME

From
CMDSERIES.ORDR,CMDSERIES.ORDL,CMDSERIES.PROJ,CMDSERIES.EMPL,CMDSERIES.PLNT WHERE
((CMDSERIES.ORDR.CUST_CODE=CMDSERIES.PROJ.CUST_CODE)

AND(CMDSERIES.ORDR.ORDER_DATE=CMDSERIES.ORDL.ORDER_DATE)
AND(CMDSERIES.ORDR.PROJ_CODE=CMDSERIES.PROJ.PROJ_CODE)
AND(CMDSERIES.ORDR.ORDER_CODE=CMDSERIES.ORDL.ORDER_CODE)
AND(CMDSERIES.ORDR.SLSMN_EMPL_CODE=CMDSERIES.EMPL.EMPL_CODE)
AND(CMDSERIES.ORDR.PRICE_PLANT_CODE=CMDSERIES.PLNT.PLANT_CODE))


=============================================

The issue I have is there are records in the ORDR table that do not have an ORDR.PROJ_CODE value. I still want to display these values in my report, but they do not show up. When I remove the AND(CMDSERIES.ORDR.PROJ_CODE=CMDSERIES.PROJ.PROJ_CODE) line from the view, I get tons of repeating values. I've tried outer joins, but to no avail. I'm lost at this point and any assistance would be greatly appreciated.

Thanks. Received on Wed Apr 25 2007 - 15:37:01 CEST