Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 21st, 2008, 04:08 AM
Authorized User
 
Join Date: Apr 2007
Location: Silvassa, Dadra & Nagar Haveli, India.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem while joining two queries

Kindly refer the attached queries namely
1)REORDER
2)PENDING WORKORDER

I want to calculate the work order requirement on basis of
Stock, Pending sales order, re-order level and already entered workorders. Now i want to add the first column of 2nd query in the report of 1st query. For that i tried the below statements

AND ITEM_CODE = LCS_ITEM_CODE (+)
AND ITEM_CODE = SOI_ITEM_CODE (+)
AND ITEM_CODE = PWH_ITEM_CODE (+)

LCS_ITEM_CODE is Stock table item_code,
SOI_ITEM_CODE is Sales order item_code,
PWH_ITEM_CODE is Production work order item_code,

When the above statments used while combining the 2 queries does display the result, but the details comes only for the ietm_codes whose work orders are present.

Kindly guide me how can i get the desired output.



1)RE-ORDER QUERY :

SELECT DISTINCT ITEM_CODE,
ITEM_NAME,
(CASE
WHEN SUBSTR(ITEM_CODE,11,3) = '000' THEN 'LOOSE'
ELSE SUBSTR(ITEM_CODE,11,4)
END) PKG_SIZE,
((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3))
||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_ST K_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) STOCK,
(CASE
WHEN SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) < 0 THEN 0
ELSE SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1))
END) PNDG,
((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3))
||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_ST K_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) NETSTK,
ITEM_RORD_LVL REORD_LVL,
(CASE
WHEN ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3))
||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_ST K_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)) > 0
AND ITEM_RORD_LVL > 0 THEN ((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3))
||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_ST K_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1))
ELSE NVL(ITEM_RORD_LVL,0) - (((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1) * TO_NUMBER(SUBSTR(ITEM_CODE,- 3,3))
||DECODE(SUBSTR(LCS_ITEM_CODE,11,3),'000',((LCS_ST K_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU) / IU_MAX_LOOSE_1)) - SUM(TO_NUMBER(SOI_QTY
||'.'
||SOI_QTY_LS) - (SOI_INVI_QTY_BU / IU_CONV_FACTOR / IU_MAX_LOOSE_1)))
END) REQ,
DECODE(ITEM_RORD_QTY_LS,NULL,ITEM_RORD_QTY,
(ITEM_RORD_QTY
||'.'
||ITEM_RORD_QTY_LS)) BTCHSZE
FROM OM_ITEM,
OT_SO_HEAD,
OT_SO_ITEM,
OM_ITEM_UOM,
OS_LOCN_CURR_STK
WHERE (ITEM_UOM_CODE = IU_UOM_CODE
AND ITEM_CODE = IU_ITEM_CODE)
AND ITEM_CODE = LCS_ITEM_CODE (+)
AND ITEM_CODE = SOI_ITEM_CODE (+)
AND LCS_LOCN_CODE = 'FG'
AND LCS_ITEM_CODE = SOI_ITEM_CODE
AND SOI_SOH_SYS_ID = SOH_SYS_ID
AND SOH_TXN_CODE IN ('SORLLOC',
'SORLCT3',
'SORLNPL',
'EXPFABLK')
AND NVL(SOH_CLO_STATUS,0) = 0
AND NVL(SOI_SHORT_CLO_STATUS,2) = 2

GROUP BY ITEM_CODE,
ITEM_NAME,
LCS_STK_QTY_BU,
LCS_RCVD_QTY_BU,
LCS_ISSD_QTY_BU,
IU_MAX_LOOSE_1,
ITEM_RORD_LVL_LS,
ITEM_RORD_LVL,
ITEM_RORD_QTY_LS,
ITEM_RORD_QTY,
LCS_ITEM_CODE
ORDER BY ITEM_CODE ASC

---------------------------------------------------------------------


2) WORK-ORDERS QUERY :


SELECT DISTINCT PWH_NO PWO_NO,
PWH_DT PWO_DT,
(CASE
WHEN PWFGPI_QTY = 0 THEN PWH_ITEM_CODE
ELSE PWFGPI_ITEM_CODE
END) ITEMCODE,
ITEM_NAME PRODUCT_NAME,
(CASE
WHEN PWFGPI_QTY = 0 THEN SUM(PWH_QTY)
ELSE PWFGPI_QTY * SUBSTR(PWFGPI_ITEM_CODE,11,3)
END) PWO_QTY,
PSD_PR_ACCP_QTY_BU / 1000 PROD_REP,
PWH_PS_QTY_BU / 1000 PROD_SLIP_PENDING
FROM OT_PWO_HEAD,
OM_ITEM,
OT_PWO_STAGE_DETAIL,
OT_PWO_FG_PACKED_ITEM
WHERE PWH_ITEM_CODE = ITEM_CODE
AND PWFGPI_PWH_SYS_ID = PWH_SYS_ID
AND NVL(PWH_CLO_STATUS,0) = 0
AND PSD_PWH_SYS_ID = PWH_SYS_ID
AND PWH_PS_QTY_BU / 1000 IS NULL
GROUP BY PWH_NO,
PWH_DT,
PWH_ITEM_CODE,
ITEM_NAME,
PWFGPI_ITEM_CODE,
PWFGPI_QTY,
PSD_PR_ACCP_QTY_BU,
PWH_PS_QTY_BU,
ITEM_CODE
ORDER BY PWH_NO


Yogesh
__________________
Yogesh


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with parameter queries ph0neman Classic ASP Basics 2 July 1st, 2008 08:57 PM
Joining 3 Queries. rupen SQL Language 1 May 11th, 2006 11:35 AM
problem joining tables using vfpoledb in c# ACE2084 ADO.NET 2 April 1st, 2006 03:10 AM
Select with multiple queries problem djbst84 SQL Language 0 May 21st, 2004 07:58 PM
MySQL queries problem hosefo81 PHP Databases 1 January 22nd, 2004 07:15 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.