Home » SQL & PL/SQL » SQL & PL/SQL » Problem using join in sql query (Oracle, Toad 9.1)
Problem using join in sql query [message #329345] Wed, 25 June 2008 02:59 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I want to develop a report in which the column sequence will be as follows:
1)Item_code
2)Item_name
3)Stock
4)Pending
5)Re-order Level
6)Item_reorder_qty

I had written the query, but the problem is all the products does not get displayed. The products are more than 700. But after running the query, only 364 gets displayed.

How i can make use of outer join in my query to solve this. The Sql query is as follows :-

SELECT
ITEM_CODE,
ITEM_NAME,
((LCS_STK_QTY_BU + LCS_RCVD_QTY_BU - LCS_ISSD_QTY_BU- LCS_REJECT_QTY_BU)/IU_MAX_LOOSE_1)STK ,
SUM(TO_NUMBER(PI_QTY||'.'||PI_QTY_LS) - (PI_GI_QTY_BU/IU_CONV_FACTOR/IU_MAX_LOOSE_1))PNDG,
ITEM_RORD_LVL,
ITEM_RORD_QTY
FROM
OM_ITEM,
OM_ITEM_UOM,
OT_PO_HEAD,
OT_PO_ITEM,
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 = PI_ITEM_CODE
AND LCS_ITEM_CODE = PI_ITEM_CODE
AND LCS_LOCN_CODE ='RM'
AND PI_PH_SYS_ID = PH_SYS_ID
GROUP BY ITEM_CODE,ITEM_NAME,LCS_STK_QTY_BU,LCS_RCVD_QTY_BU,LCS_ISSD_QTY_BU,LCS_REJECT_QTY_BU,IU_MAX_LOOSE_1,ITEM_RORD_LVL,ITEM_RORD_QTY
ORDER BY ITEM_CODE ASC

Yogesh

Re: Problem using join in sql query [message #329347 is a reply to message #329345] Wed, 25 June 2008 03:05 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you checked the documentation for outer joins. Did you try to implement what you read? If so, post what you tried. It might (would) also be helpful if you were to post the structures of your tables and their relationships (remember, we have no idea where your products are - or any other values for that matter) Save us from having to guess Wink
Re: Problem using join in sql query [message #329353 is a reply to message #329345] Wed, 25 June 2008 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, 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).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 25 June 2008 03:16]

Report message to a moderator

Re: Problem using join in sql query [message #329359 is a reply to message #329353] Wed, 25 June 2008 03:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OP has decided to ask the question elsewhere rather than reply to the question posed. As far as I am concerned, he's on his own (HOHO) Wink
Re: Problem using join in sql query [message #329363 is a reply to message #329353] Wed, 25 June 2008 03:47 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I am using Oracle 10. Sorry but i don't know the exact 4 decimal oracle version. Where can i find out the same ?
Yes i had checked the documentation for outer joins. But i did'nt understood. I had tried like this :

AND ITEM_CODE = LCS_ITEM_CODE(+)
AND ITEM_CODE = PI_ITEM_CODE(+)
AND LCS_ITEM_CODE = PI_ITEM_CODE

Here i want all the LCS_ITEM_CODE to get listed, and stock and pending against LCS_ITEM_CODE to be displayed, irrespective of nil stock / nil pending.


Yogesh


Re: Problem using join in sql query [message #329369 is a reply to message #329363] Wed, 25 June 2008 04:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What happened when you 'tried this'? Where are your table defs and relationships?
Re: Problem using join in sql query [message #329370 is a reply to message #329363] Wed, 25 June 2008 04:13 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where can i find out the same ?

v$version
DBMS_UTILITY.DB_VERSION

Regards
Michel
Previous Topic: using a package variable for a function argument (urgent)
Next Topic: Query on multi group
Goto Forum:
  


Current Time: Wed Dec 07 03:18:20 CST 2016

Total time taken to generate the page: 0.21830 seconds