Home » SQL & PL/SQL » SQL & PL/SQL » Need help in SQL query (Oracle 10g)
Need help in SQL query [message #361771] Fri, 28 November 2008 00:48 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
When i tried to display the BAL_QTY and BAL_VAL then system displays all the items & BAL_QTY & BAL_VAL instead of against the specific rows.

CNT ITEM_CODE ITEM_NAME
2 FAAA000 AAA
25 FBBB000 BBB
2 FCCC000 CCC

I want the BAL_QTY and BAL_VAL to appear as per the count. Here the count is the row no. for that item.

If count is 25, then the item is appearing 25 times in table & i want to display the BAL_QTY & BAL_VAL against the 25 row for that item in the table.
SELECT DISTINCT COUNT(ITEM_CODE) CNT,
ITEM_CODE ,
ITEM_NAME
FROM
(SELECT ROWNUM ,
SYS_ID ,
TXN ,
RI ,
COST_GROUP,
SL_NO ,
SL_DT ,
ITEM_CODE ,
ITEM_NAME ,
BAL_QTY ,
BAL_VALUE
FROM
(SELECT SL_SYS_ID SYS_ID ,
SL_TXN_CODE TXN ,
SL_COST_GROUP_CODE COST_GROUP,
SL_NO ,
SL_DT ,
SL_ITEM_CODE ITEM_CODE ,
ITEM_NAME ,
SL_RCVD_ISSD RI ,
ROUND((SUM(SL_QTY_BU*DECODE(SL_RCVD_ISSD,
'R',1,
-1))OVER (PARTITION BY ITEM_CODE ORDER BY SL_DT,SL_SYS_ID))/IU_CONV_FACTOR/IU_MAX_LOOSE_1,3) BAL_QTY,
ROUND(SUM(SL_VAL_1 *DECODE(SL_RCVD_ISSD,
'R',1,
-1))OVER (PARTITION BY ITEM_CODE ORDER BY SL_DT,SL_SYS_ID),2) BAL_VALUE
FROM OS_STK_LEDGER,
OM_ITEM_UOM ,
OM_ITEM
WHERE IU_ITEM_CODE = SL_ITEM_CODE
AND IU_UOM_CODE = SL_BASE_UOM_CODE
AND ITEM_CODE = SL_ITEM_CODE
AND SL_ITEM_CODE = ITEM_CODE
AND SL_ITEM_CODE NOT LIKE 'C%'
AND SL_ITEM_CODE LIKE 'F%'
AND SL_COST_GROUP_CODE='CGSIL001'
ORDER BY 5
)
)
GROUP BY ITEM_CODE,
ITEM_NAME
ORDER BY ITEM_CODE
Re: Need help in SQL query [message #361773 is a reply to message #361771] Fri, 28 November 2008 01:03 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@yogeshyl

Hmmm.. 44 Posts... I wonder... have you ever gone through OraFAQ Forum Guide. If not please do it now and follow the guidelines especially on "How to Format Your Post"

Post a test case(CREATE TABLE Statements and INSERT statements for some dummy records) so that we can run and test your query.

Help us Help you

Regards,
Jo
Previous Topic: How Connect To Database Locally On Server.
Next Topic: ORACLE
Goto Forum:
  


Current Time: Sat Dec 03 14:05:31 CST 2016

Total time taken to generate the page: 0.05122 seconds