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