Re: Join , Where

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Wed, 02 Jul 2003 18:14:40 -0700
Message-ID: <F001.005C2F3B.20030702180531@fatcity.com>

I run the query and it run even though in Rapid SQL send the next error:
unexpected token:LEFT

My questions is what is the difference between where an join in Oracle, what is better and why?
>>> [EMAIL PROTECTED] 07/02/03 06:55PM
>>>
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,
 POLINESRC.QUANTITY AS CANT_PE,
 ROUND( ITEMLOC.SOH_QTY - ( ITEMLOC.ALLOC_QTY + ITEMLOC.IN_PROC_QTY), 2) AS 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"> THEN
'K'

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


WHEN SUBSTR(POLINE.ENT_BUY_UOM,1,1)='C'
THEN 'C'<FONT face="Courier New" color=#000000

POINT-SIZE="9">


END UNIDAD,
SUBSTR(POLINESRC.REQ_LOCATION,4,2) as TIENDA,
<FONT

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

WHEN SUBSTR(ITEMMAST.ALT_UOM_01,1,1) THEN ITEMMAST.ALT_UOM_CONV_01

WHEN SUBSTR(ITEMMAST.ALT_UOM_02,1,1) THEN ITEMMAST.ALT_UOM_CONV_02

WHEN SUBSTR(ITEMMAST.ALT_UOM_03,1,1) THEN ITEMMAST.ALT_UOM_CONV_03

END EL_CANT_EM,
 POLINE.ENT_BUY_UOM AS EL_UNID_EM,
 POLINE.DESCRIPTION AS EL_DESCRIP,
 POLINE.PURCH_MAJCL AS EL_NO_FAM,
<FONT

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

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


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

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

ELSE ENT_UNIT_CST

END EL_COSTO,
<FONT

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

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

WHEN '2000' THEN
POPIVUF.PIV_USR_FLD_01

WHEN '2001' THEN
POPIVUF.PIV_USR_FLD_02

WHEN '2002' THEN
POPIVUF.PIV_USR_FLD_03

ELSE '0'<FONT face="Courier New" color=#000000

POINT-SIZE="9">


END EL_PAC_PRO

<FONT

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

POPIVUF RIGHT OUTER JOIN
 POLINE
ON POPIVUF.ITEM = POLINE.ITEM AND POPIVUF.PROCURE_GROUP = POLINE.PROCURE_GROUP AND
 POPIVUF.VENDOR = POLINE.VENDOR ON
POLINESRC.COMPANY = POLINE.COMPANY AND
 POLINESRC.PO_CODE = POLINE.PO_CODE AND
POLINESRC.PO_NUMBER = POLINE.PO_NUMBER AND<FONT face="Courier New" color=#000000

POINT-SIZE="9">

 POLINESRC.PO_RELEASE = POLINE.PO_RELEASE AND
POLINESRC.LINE_NBR = POLINE.LINE_NBR LEFT OUTER JOIN
 ITEMLOC
LEFT OUTER JOIN
 ITEMMAST
ON ITEMLOC.ITEM = ITEMMAST.ITEM ON POLINE.COMPANY = ITEMLOC.COMPANY AND
 POLINE.LOCATION = ITEMLOC.LOCATION 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 POLINESRC.REQ_DEL_DATE = sysdate
<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 - 20:14:40 CDT