Home » SQL & PL/SQL » SQL & PL/SQL » Need help on using join (Oracle 10g,Toad9.1)
Need help on using join [message #335847] Thu, 24 July 2008 00:24 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
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

Re: Need help on using join [message #335848 is a reply to message #335847] Thu, 24 July 2008 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above.
Re: Need help on using join [message #335849 is a reply to message #335848] Thu, 24 July 2008 00:30 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I am attaching the query with proper formatting.
  • Attachment: query.txt
    (Size: 1.21KB, Downloaded 119 times)
Re: Need help on using join [message #335864 is a reply to message #335849] Thu, 24 July 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't attach it, some of us can't download it, post it inline but please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What is the problem with your query? How could we if it works or not and why?

Regards
Michel
Re: Need help on using join [message #335865 is a reply to message #335864] Thu, 24 July 2008 00:58 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
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.
Re: Need help on using join [message #335873 is a reply to message #335865] Thu, 24 July 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
yogeshyl wrote on Thu, 24 July 2008 07:58
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.

So do it.

Regards
Michel

Re: Need help on using join [message #335875 is a reply to message #335873] Thu, 24 July 2008 01:12 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
How can i use RIGHT JOIN to display the whole item_names from table OS_LOCN_CURR_STK.

[Updated on: Thu, 24 July 2008 01:12]

Report message to a moderator

Re: Need help on using join [message #335884 is a reply to message #335875] Thu, 24 July 2008 01:35 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows but you? You didn't provide tables definition and connections.

Regards
Michel
Previous Topic: PLS-00302: component 'A' must be declared
Next Topic: Exec Immediate question
Goto Forum:
  


Current Time: Sat Dec 03 17:50:02 CST 2016

Total time taken to generate the page: 0.04558 seconds