Home » SQL & PL/SQL » SQL & PL/SQL » need to show lot no. along with supplier name (forms 6i , oracle -9i)
need to show lot no. along with supplier name [message #280336] Tue, 13 November 2007 04:49 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello sir,
i need to show supplier name along with lot no.
here is my query--

SELECT DISTINCT(b.vc_field1),a.vc_lot_no,a.nu_balance_qty--, c.VC_SUPPLIER_NAME
FROM STK_LOT_SUMMARY a, DT_RECEIPT b, makess.mst_supplier c, purchase.MST_SUP_ITEM d
WHERE a.vc_comp_code = '01'--:GLOBAL.vc_comp_code
AND a.vc_comp_code = b.vc_comp_code
AND b.VC_COMP_CODE=c.VC_COMP_CODE
AND c.VC_COMP_CODE=d.VC_COMP_CODE
AND c.NU_SUPPLIER_CODE=d.NU_SUPPLIER_CODE
AND a.VC_LOT_NO=b.VC_LOT_NO
AND a.vc_item_code=b.vc_item_code
AND b.vc_item_code=d.vc_item_code
AND a.vc_item_code = 'SYUWLNZXYWXXX00400100'--:HD_VERIFY.vc_item_code
--AND b.vc_field1 IS NOT NULL
AND a.vc_lot_no <> '0'
AND a.nu_balance_qty > 0
GROUP BY (a.nu_balance_qty,b.vc_field1, c.VC_SUPPLIER_NAME,a.vc_lot_no)

whose result is---

VC_FIELD1	VC_LOT_NO	NU_BALANCE_QTY
	11	48.00
22 B	8	192.00
181 A	3	200.00
54 F	13	244.50
143	10	400.00
1343	4	1,000.00
181	2	1,249.00




but when i include supplier name(removing comment) result would be....

VC_FIELD1	VC_LOT_NO	NU_BALANCE_QTY	VC_SUPPLIER_NAME
	11	48.00	Chandak Wools
	11	48.00	Somani Woollen Mills
	11	48.00	Sonu Monu & Brothers
	11	48.00	Somani Woolen Pvt. Ltd
181 A	3	200.00	Chandak Wools
181 A	3	200.00	Somani Woollen Mills
181 A	3	200.00	Sonu Monu & Brothers
181 A	3	200.00	Somani Woolen Pvt. Ltd
143	10	400.00	Chandak Wools
143	10	400.00	Somani Woollen Mills
143	10	400.00	Sonu Monu & Brothers
143	10	400.00	Somani Woolen Pvt. Ltd
1343	4	1,000.00	Chandak Wools
1343	4	1,000.00	Somani Woollen Mills
1343	4	1,000.00	Sonu Monu & Brothers
1343	4	1,000.00	Somani Woolen Pvt. Ltd
22 B	8	192.00	Chandak Wools
22 B	8	192.00	Somani Woollen Mills
22 B	8	192.00	Sonu Monu & Brothers
22 B	8	192.00	Somani Woolen Pvt. Ltd
181	2	1,249.00	Chandak Wools
181	2	1,249.00	Somani Woollen Mills
181	2	1,249.00	Sonu Monu & Brothers
181	2	1,249.00	Somani Woolen Pvt. Ltd
54 F	13	244.50	Chandak Wools
54 F	13	244.50	Somani Woollen Mills
54 F	13	244.50	Sonu Monu & Brothers
54 F	13	244.50	Somani Woolen Pvt. Ltd


please help me how could i write query to get lot no along with supplier name.
total no of rows is 7.but while adding supplier name it compare item code and executes 28 rows.

regards





Re: need to show lot no. along with supplier name [message #280375 is a reply to message #280336] Tue, 13 November 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check your join clauses.

Regards
Michel
Re: need to show lot no. along with supplier name [message #280377 is a reply to message #280336] Tue, 13 November 2007 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you want the data to look like?

From the data you have shown us, you have 4 different suppliers for Lot no 48 (the first one)

Either (as Michel sugests) you've missed out a join clause, or this is the correct data.

If it's the correct data, then you are going to have to show 4 seperate supplier names for Lot No 48.
Re: need to show lot no. along with supplier name [message #280434 is a reply to message #280377] Tue, 13 November 2007 09:48 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello Michel Cadot

you have caught the rite point.
i missed something on join clause.

my problem is solved.

thanxx
Previous Topic: get previous quarter starting_date and ending_date
Next Topic: Query help
Goto Forum:
  


Current Time: Fri Dec 09 04:25:40 CST 2016

Total time taken to generate the page: 0.25269 seconds