|
Subject:
|
Need help using join in SQL query
|
|
Posted By:
|
yogeshyl
|
Post Date:
|
7/24/2008 12:33:28 AM
|
I have written a query to display 3 columns in output namely
1)ITEM_NAME
2)STOCK
3)PENDING
I want to display the total item names and its stock and pending.
If there is no stock against ant item_name then 0 should get displayed. Like wise if no order is pending then 0 should appear.
My query is as follows:
SELECT DISTINCT ITEM_NAME,
(
CASE
WHEN LCS_LOCN_CODE IN ('PM','RM','JBCHEM','JBSURF','JBSUYO','JBVIJC','JBRAJAN','FG','PMSCR','REUSELOC')
THEN (LCS_STK_QTY_BU/IU_MAX_LOOSE_1)
ELSE 0
END)STOCK,
TO_NUMBER(PI_QTY||'.'||PI_QTY_LS) - (PI_GI_QTY_BU/IU_CONV_FACTOR/IU_MAX_LOOSE_1) PNDG_QTY
FROM OS_LOCN_CURR_STK,
OM_ITEM ,
OM_ITEM_UOM ,
OT_PO_HEAD ,
OT_PO_ITEM
WHERE ITEM_CODE = LCS_ITEM_CODE
AND ITEM_CODE = PI_ITEM_CODE
AND PI_PH_SYS_ID = PH_SYS_ID
AND ITEM_UOM_CODE = IU_UOM_CODE
AND ITEM_CODE = IU_ITEM_CODE
AND TO_NUMBER(PI_QTY||'.'||PI_QTY_LS) > (PI_GI_QTY_BU/IU_CONV_FACTOR/IU_MAX_LOOSE_1)
AND NVL(PH_CLO_STATUS,0) = 0
AND NVL(PI_SHORT_CLO_STATUS,2) = 2
GROUP BY PH_NO ,
PH_DT ,
ITEM_NAME ,
PI_QTY ,
PI_QTY_LS ,
PI_GI_QTY_BU ,
IU_CONV_FACTOR,
IU_MAX_LOOSE_1,
LCS_STK_QTY_BU,
LCS_LOCN_CODE
The item_names are more than 1000, but with this query, the 231 only are displayed after executing.
I want to join the OS_LOCN_CURR_STK table with the OT_PO_ITEM table , such that all items in OS_LOCN_CURR_STK are displayed.
Yogesh
|
|
Reply By:
|
sandy72911
|
Reply Date:
|
7/30/2008 4:12:28 AM
|
Use left join instead of inner join.
|
|
