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

Home -> Community -> Mailing Lists -> Oracle-L -> Join , Where

Join , Where

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Wed, 02 Jul 2003 17:04:05 -0700
Message-ID: <F001.005C2E7E.20030702165532@fatcity.com>

Hi !!
(I am using Oracle 9.2)
 
I am trying to run the next query but I received an error in the from statement.
 
<FONT face="Courier New" color=#0000ff

POINT-SIZE="9">SELECT<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> POLINE.ITEM <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">AS 

CVEART,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       POLINESRC.QUANTITY AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> CANT_PE,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       <FONT
face="Courier New" color=#0000ff POINT-SIZE="9">ROUND<FONT face="Courier New" color=#000000 POINT-SIZE="9">( ITEMLOC.SOH_QTY - ( ITEMLOC.ALLOC_QTY +  ITEMLOC.IN_PROC_QTY), 2) <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AS<FONT face="Courier New" color=#000000 POINT-SIZE="9"> EXIS_BOD,  <FONT
face="Courier New" color=#0000ff POINT-SIZE="9">CASE<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">WHEN<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> POLINE.ENT_BUY_UOM=<FONT face="Courier New" color=#ff0000 
POINT-SIZE="9">'KG'<FONT face="Courier New" color=#000000 
POINT-SIZE="9">   <FONT face="Courier New" color=#0000ff POINT-SIZE="9">THEN
'K'<FONT
face="Courier New" color=#000000 POINT-SIZE="9">
<FONT face="Courier New" color=#000000

POINT-SIZE="9">         
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> POLINE.ENT_BUY_UOM=<FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'UNID'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">THEN<FONT face="Courier New" color=#000000
POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 
POINT-SIZE="9">'P'<FONT face="Courier New" color=#000000 
POINT-SIZE="9">

<FONT face="Courier New" color=#000000

POINT-SIZE="9">         
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9">  SUBSTR(POLINE.ENT_BUY_UOM,1,1)=<FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'C'
THEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'C'<FONT face="Courier New" color=#000000
POINT-SIZE="9">

<FONT face="Courier New" color=#000000

POINT-SIZE="9">         
END<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> UNIDAD,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">SUBSTR(POLINESRC.REQ_LOCATION,4,2) <FONT face="Courier New" color=#0000ff POINT-SIZE="9">as<FONT face="Courier New" color=#000000 POINT-SIZE="9"> TIENDA,
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">CASE<FONT face="Courier New" color=#000000 POINT-SIZE="9"> SUBSTR(POLINE.ENT_BUY_UOM,1,1)
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> SUBSTR(ITEMMAST.ALT_UOM_01,1,1) THEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ITEMMAST.ALT_UOM_CONV_01
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> SUBSTR(ITEMMAST.ALT_UOM_02,1,1) THEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ITEMMAST.ALT_UOM_CONV_02
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> SUBSTR(ITEMMAST.ALT_UOM_03,1,1) THEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ITEMMAST.ALT_UOM_CONV_03
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
END<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_CANT_EM,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       POLINE.ENT_BUY_UOM AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_UNID_EM,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       POLINE.DESCRIPTION AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_DESCRIP,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       POLINE.PURCH_MAJCL AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_NO_FAM,      
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">CASE<FONT face="Courier New" color=#000000 POINT-SIZE="9"> POLINE.TAX_CODE
<FONT face="Courier New" color=#000000

POINT-SIZE="9">          
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#ff0000 POINT-SIZE="9">'IVA 0%'<FONT face="Courier New" 
color=#000000 POINT-SIZE="9">  <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">THEN<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> POLINE.TAXBL_UNT_CST

<FONT face="Courier New" color=#000000

POINT-SIZE="9">          
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#ff0000 POINT-SIZE="9">'IVA 10%'<FONT face="Courier New" 
color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">THEN
POLINE.TAXBL_UNT_CST*1.1
<FONT face="Courier New" color=#000000

POINT-SIZE="9">          
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#ff0000 POINT-SIZE="9">'IVA 15%'<FONT face="Courier New" 
color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">THEN
POLINE.TAXBL_UNT_CST*1.15
<FONT face="Courier New" color=#000000

POINT-SIZE="9">          
ELSE<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ENT_UNIT_CST
<FONT face="Courier New" color=#000000

POINT-SIZE="9">          
END<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_COSTO,
<FONT

face="Courier New" color=#000000 POINT-SIZE="9">POLINESRC.REQ_DEL_DATE AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> FECHA, POLINE.VENDOR
AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_NO_PROV, ITEMLOC.USER_FIELD_N2
AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> FOR_SUR, ITEMLOC.USER_FIELD3
AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> TIPO_ALMAC, POLINE.PO_NUMBER
AS<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_NU_PEDI,
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">CASE<FONT face="Courier New" color=#000000 POINT-SIZE="9"> POLINE.COMPANY
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'2000'<FONT face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff POINT-SIZE="9">THEN
POPIVUF.PIV_USR_FLD_01
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'2001'<FONT face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff POINT-SIZE="9">THEN
POPIVUF.PIV_USR_FLD_02
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             
WHEN<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'2002'<FONT face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff POINT-SIZE="9">THEN
POPIVUF.PIV_USR_FLD_03
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             
ELSE<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" color=#ff0000 POINT-SIZE="9">'0'<FONT face="Courier New" color=#000000
POINT-SIZE="9">

<FONT face="Courier New" color=#000000

POINT-SIZE="9">             
END<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> EL_PAC_PRO
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">FROM<FONT face="Courier New" color=#000000
POINT-SIZE="9">         POLINESRC LEFT
OUTER JOIN
<FONT face="Courier New" color=#000000

POINT-SIZE="9">            
POPIVUF RIGHT OUTER JOIN
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             POLINE
ON<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> POPIVUF.ITEM = POLINE.ITEM AND<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> POPIVUF.PROCURE_GROUP = POLINE.PROCURE_GROUP <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             POPIVUF.VENDOR = POLINE.VENDOR <FONT face="Courier New" color=#0000ff POINT-SIZE="9">ON
POLINESRC.COMPANY = POLINE.COMPANY <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             POLINESRC.PO_CODE = POLINE.PO_CODE <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
POLINESRC.PO_NUMBER = POLINE.PO_NUMBER <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND<FONT face="Courier New" color=#000000
POINT-SIZE="9"> 

<FONT face="Courier New" color=#000000

POINT-SIZE="9">             POLINESRC.PO_RELEASE = POLINE.PO_RELEASE <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
POLINESRC.LINE_NBR = POLINE.LINE_NBR LEFT OUTER JOIN
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             ITEMLOC
LEFT OUTER JOIN
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             ITEMMAST
ON<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ITEMLOC.ITEM = ITEMMAST.ITEM ON<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> POLINE.COMPANY = ITEMLOC.COMPANY <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
<FONT face="Courier New" color=#000000

POINT-SIZE="9">             POLINE.LOCATION = ITEMLOC.LOCATION <FONT face="Courier New" color=#0000ff POINT-SIZE="9">AND
POLINE.ITEM = ITEMLOC.ITEM
<FONT
face="Courier New" color=#0000ff POINT-SIZE="9">WHERE<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> POLINE.PO_CODE=<FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'01'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">AND<FONT face="Courier New" color=#000000 POINT-SIZE="9">  POLINESRC.REQ_DEL_DATE = <FONT face="Courier New" color=#0000ff POINT-SIZE="9">sysdate<FONT face="Courier New" color=#000000 POINT-SIZE="9">
<FONT
face="Courier New" color=#0000ff POINT-SIZE="9">ORDER<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">BY<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> POLINESRC.REQ_DEL_DATE
 
 
What I was trying to do is made the next joins ( the first row are the tables, next rows are the fields of the join)
 
POPOIVUF-POLINE ( all rows from
POLINE)
PROCURE_GROUP
ITEM
VENDOR
 
POLINESRC-POLINE (all rows from
POLINESRC)
COMPANY
PO_CODE
PO_NUMBER
PO_RELEASE

LINE_NBR
 
ITEMLOC-POLINE (all rows from
POLINE)
COMPANY
LOCATION
ITEM
 
ITEMLOC-ITEMMAST (all rows from
ITEMLOC)
ITEM
 
I tried to do this whit TOAD Modeler, but it used where instead of join. In Oracle are the same?
 
WHERE (    (poline.company =
pol.company)        AND (poline.po_code = 
pol.po_code)        AND (poline.po_number 
= pol.po_number)        AND 

(poline.po_release =
pol.po_release)        AND
(poline.line_nbr = pol.line_nbr)        AND (popivuf.procure_group =
poline.procure_group)        AND
(popivuf.item = poline.item)        AND (popivuf.vendor = poline.vendor)        AND (itemloc.item =
itemmast.item_group)        AND
(itemloc.company = poline.company)        AND (poline.LOCATION =
itemloc.LOCATION)        AND
(itemloc.item = poline.item)       )
 
 
 

I was running the query in SQL Server 2000, and it works fine there, what I am  doing wrong ??? Received on Wed Jul 02 2003 - 19:04:05 CDT

Original text of this message

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