Re: Help with building an Oracle View for Crystal Reports

On Apr 24, 4:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
> cory.swartz wrote:
> > 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!
>
> http://www.psoug.org
> Click on Morgan's Library
> Click on Joins
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Apologies for the double post. IT dept is at it with their web caching.

Thanks for the info Daniel. I was messing around with out joins and just couldn't get it prior to posting.

Thanks to Sybrand for pointing out the shortcomings of a system I inherited. Also thanks for pointing out my shortcomings of my SQL knowledge, which I was already aware of. I will be able to sleep better tonight knowing someone else is aware of it. Received on Thu Apr 26 2007 - 20:57:04 CEST