Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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
 
Old July 24th, 2008, 12:33 AM
Authorized User
 
Join Date: Apr 2007
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help using join in SQL query

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','JB RAJAN','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
__________________
Yogesh
 
Old July 30th, 2008, 04:12 AM
Registered User
 
Join Date: Jul 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use left join instead of inner join.






Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i use typed dataset with Join Sql query? vijaykumartaduri BOOK: Professional Crystal Reports for VS.NET 0 November 10th, 2005 04:02 AM
JOIN query wverner Access 2 April 5th, 2005 01:44 PM
Join Query tsimsha Classic ASP Databases 2 December 2nd, 2004 09:31 AM
T-SQL Outer join query jaucourt SQL Server 2000 5 June 2nd, 2004 10:56 AM
Strange join query nikosdra SQL Language 1 August 10th, 2003 06:10 AM





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