Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Oracle View and Crystal Reports

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@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 - 08:37:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US