FrankKulash Jan 31, 2008   5:58 PM GMT

Hi, Here's the problem: you;re saying

...r.RC_code not IN
(Select v.RC_code From iv_detail v 
,iv_list i 
Where (v.rc_CODE = r.rc_code) and ...

The sub-query is correlated to the main query: that is, it uses a value from the main query and (potentially) runs the sub-query a separate time for each row in the main query. The correlating condition in the sub-query is "v.rc_CODE = r.rc_code", so when, in the sub-query, you say "Select v.RC_code ..." that's equivalent to saying "Select r.RC_code ...", and your main query has the condition

r.RC_code not IN
(Select r.RC_code ...

I think what you want is an UNcorellated sub-query, that is, a sub-query that will run once (no matter how many rows are in the main query) to produce a list of bad rc_codes, like this:

SELECT p.PO_code,p.Po_date,
p.Supp_code + ' : '+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount
FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code
inner join Supplier s on s.supp_code = p.supp_code
WHERE (p.Supp_code in ('102200','102083')) and (substring(p.PO_CODE ,5,1) = 'R' ) and 
(p.cancel = 'N' and r.cancel = 'N' ) and (r.RC_code not IN
(Select v.RC_code From iv_detail v 
,iv_list i 
Where i.iv_code = v.iv_code and i.Cancel = 'N' )...

that is, get rid of the condition "(v.rc_CODE = r.rc_code)" in the sub-query. The first person to answer this question has a good point. It's always better if you can include a specific example, the simpler the better.

1,240 pointsBadges:

2830360 Feb 1, 2008   5:15 AM GMT

Thank you for solving my program This right the problem is NOT IN command. Please see the detail below : File Structure : po_list : PO_code(key) , desc, supp_code, cancel ,... rc_list : RC_code(key) , po_code , desc, cancel , ... iv_detail : iv_code, rc_code iv_list : IV_code(key) , desc. cancel ,... supplier : Supp_code(key) , snme, addr ,... Data sample : PO_list : po507486 , po_desc , 102200 , n : po508270 , po_desc , 102083 , n RC_list : rc50c100 , po507486 , rc_desc , n : rc508018 , po508270 , rc_desc , n IV_detail : iv50c059 , rc50c100 : iv508014, rc508018 IV_listt : iv50c059 , iv_desc, n : iv508014, iv_desc , Y The data of PO508270 should be showed in this query. Thaks again for any help, ndID

50 pointsBadges:

FrankKulash Feb 1, 2008   3:43 PM GMT

Hi, Disregard the suggestion I made yesterday; it won't change the results of your query. The row with PO_CODE 'PO508270' isn't being selected because the WHERE-clause says

(substring(p.PO_CODE ,5,1) = 'R' )

The fifth character of 'PO508270' is '8', not 'R'. Also, there may not be a match in the supplier table.

1,240 pointsBadges:

2830360 Feb 4, 2008   11:25 AM GMT

Hi, Sorry . I made a sample data incorrect .The actual data are : PO_list : H01-R-po507486 ,po_desc , 102200 , n : H01-R-po508270 , po_desc , 102083 , n RC_list : H01-R-rc50c100 , H01-R-po507486 , rc_desc , n : H01-R-rc508018 , H01-R-po508270 , rc_desc , n IV_detail : H01-R-iv50c059 , H01-R-rc50c100 : H01-R-iv508014, H01-R-rc508018 IV_listt : H01-R-iv50c059 , iv_desc, n : H01-R-iv508014, iv_desc , Y Table : Supplier : Supp_code , sname,addr ... and query is SELECT p.PO_code,p.Po_date, p.Supp_code + ' : '+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code inner join Supplier s on s.supp_code = p.supp_code WHERE (p.Supp_code in ('102200','102083')) and (substring(p.PO_CODE ,5,1) = 'R' ) and (p.cancel = 'N' and r.cancel = 'N' ) and (r.RC_code not IN (Select v.RC_code From iv_detail v ,iv_list i Where (v.rc_CODE = r.rc_code) and i.iv_code = v.iv_code and i.Cancel = 'N' ) The H01-R-PO508270 still did not showed Thanks you for your feedback. ndID

50 pointsBadges:

FrankKulash Feb 4, 2008   4:43 PM GMT

Hi, Then there's probably no match in the supplier table. Without seeing what's in your supplier table, I can't be more specific.

1,240 pointsBadges:

2830360 Feb 6, 2008   10:14 AM GMT

Hi, I don't think so. Because when i changed The condition as : (Not IN to be IN command ) SELECT p.PO_code,p.Po_date, p.Supp_code + ‘ : ‘+ s.Sname as Supp_Nme,p.po_total,r.RC_code,r.Amount FROM PO_list p inner join RC_list r on r.PO_code = p.PO_code inner join Supplier s on s.supp_code = p.supp_code WHERE (p.Supp_code in (’102200′,’102083′)) and (substring(p.PO_CODE ,5,1) = ‘R’ ) and (p.cancel = ‘N’ and r.cancel = ‘N’ ) and (r.RC_code IN (Select v.RC_code From iv_detail v ,iv_list i Where (v.rc_CODE = r.rc_code) and i.iv_code = v.iv_code and i.Cancel = ‘N’ ) ) The H01-R-po507486 ,which have i.cancel = 'N' is showed in this query. Thank you for your help, ndID

50 pointsBadges:

FrankKulash Feb 6, 2008   3:52 PM GMT

Hi, I'm not sure if you still have a problem, and, if so, what it is. When I run the script below in SQL Server 2005 Express, the output of the "Original Query" is one row:

PO_code         RC_code
H01-R-po508270  H01-R-rc508018

If you're not getting the same results, what are you doing differently? Describe the results you want to get and why. If the data below is not a good test case, then modify it and post the improved version. This site seems to replace double-dashes with another character that looks like one dash. The script below contains no subtraction: anything that looks like a minus sign is actually the beginning of a comment. Here's my script:

--	==========  iv_detail  ==========

DROP TABLE	iv_detail;

CREATE TABLE	iv_detail
(	iv_code	CHAR (20)
,	rc_code	CHAR (20)
);

INSERT INTO	iv_detail	(iv_code,		rc_code)
	VALUES			('H01-R-iv50c059',	'H01-R-rc50c100');
INSERT INTO	iv_detail	(iv_code,		rc_code)
	VALUES			('H01-R-iv508014',	'H01-R-rc508018');

SELECT	*
FROM	iv_detail
ORDER BY	iv_code;


--	==========  iv_list  ==========

DROP TABLE	iv_list;

CREATE TABLE	iv_list
(	iV_code	CHAR (20)
,	dsc	CHAR (10)
,	cancel	CHAR (10)
);

INSERT INTO iv_list	(iV_code,		dsc,		cancel)
	VALUES		('H01-R-iv50c059',	'iv_desc',	'n');
INSERT INTO iv_list	(iV_code,		dsc,		cancel)
	VALUES		('H01-R-iv508014',	'iv_desc',	'Y');

SELECT	*
FROM	iv_list
ORDER BY	iv_code;


--	==========  po_list  ==========

DROP TABLE	po_list;

CREATE TABLE	po_list
(	PO_code		CHAR (20)
,	dsc		CHAR (10)
,	supp_code	INT
,	cancel		CHAR (10)
);

INSERT INTO po_list	(PO_code,		dsc,		supp_code,	cancel)
	VALUES 		('H01-R-po507486',	'po_desc',	102200,		'n');
INSERT INTO po_list	(PO_code,		dsc,		supp_code,	cancel)
	VALUES 		('H01-R-po508270',	'po_desc',	102083,		'n');

SELECT	*
FROM	po_list
ORDER BY	po_code;


--	==========  rc_list  ==========

DROP TABLE	rc_list;

CREATE TABLE	rc_list
(	RC_code	CHAR (20)
,	po_code	CHAR (20)
,	dsc	CHAR (10)
,	cancel	CHAR (10)
);


INSERT INTO	rc_list	(RC_code,		po_code,		dsc,		cancel)
	VALUES		('H01-R-rc50c100',	'H01-R-po507486',	'rc_desc',	'n');
INSERT INTO	rc_list	(RC_code,		po_code,		dsc,		cancel)
	VALUES		('H01-R-rc508018',	'H01-R-po508270',	'rc_desc',	'n');

SELECT	*
FROM	rc_list
ORDER BY	rc_code;


--	==========  supplier  ==========

DROP TABLE	supplier;

CREATE TABLE	supplier
(	Supp_code	CHAR (10)
,	snme		CHAR (10)
,	addr		CHAR (10)
);

SELECT	*
FROM	supplier
ORDER BY	supp_code;



--	==========  Original Query  ==========

SELECT	p.PO_code
-- ,	p.Po_date
-- ,	p.Supp_code + ' : '+ s.Sname	as Supp_Nme
-- ,	p.po_total
,	r.RC_code
-- ,	r.Amount
FROM		PO_list		p
inner join	RC_list		r on r.PO_code		= p.PO_code
WHERE	(p.Supp_code			in ('102200','102083')) 
and	(substring (p.PO_CODE, 5, 1)	= 'R')
-- inner join	 Supplier 	s on s.supp_code 	= p.supp_code
and	(	p.cancel		= 'N' 
	AND	r.cancel		= 'N'
	)
and	(	r.RC_code		not IN
		(
		Select	v.RC_code
		From	iv_detail	v 
		,	iv_list		i 
		Where	(v.rc_CODE	= r.rc_code)
		and	i.iv_code	= v.iv_code
		and	i.Cancel	= 'N'
		)
	)
;

1,240 pointsBadges:

2830360 Feb 7, 2008   9:05 AM GMT

Hi, Thank again for your advise. I will try your suggestion and let you know the result. I wonder if i could replace Not In with Not Exists Command in this query. what is the different using. Your help is greatly appreciated. ndID

50 pointsBadges:

2830360 Feb 12, 2008   9:39 AM GMT

Hi, It works fine without any problem in your script and I found that some wrong in my data ,not script. Thanks you again for your help and advice. ndID

50 pointsBadges: