Help with building an Oracle View for Crystal Reports

From: cory.swartz <cory.swartz_at_icmatls.com>
Date: 24 Apr 2007 11:32:28 -0700
Message-ID: <1177439547.970757.45200@t38g2000prd.googlegroups.com>

I'm not sure if this is more Crystal related or a problem with my Oracle view I created... here is the view script:

---

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))



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

My issue is with the CMDSERIES.ORDR.PROJ_CODE field. We have orders that exist (ORDR.ORDER_CODE and ORDR.ORDER_DATE both exist), but do not contain any data in the ORDR.PROJ_CODE field. I want to display these orders, but when I remove the
"AND(CMDSERIES.ORDR.PROJ_CODE=CMDSERIES.PROJ.PROJ_CODE)" from the WHERE clause, I get thousands and thousands of duplicate records in my Crystal Report. Any assistance would be greatly appreciated.

Thanks! Received on Tue Apr 24 2007 - 13:32:28 CDT