SQL Command

50 pts.
Tags:
Microsoft SQL Server 2000
Stored Procedures
T-SQL
I have created a stored procedure in SQL2000 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 (v.rc_CODE = r.rc_code) and i.iv_code = v.iv_code and i.Cancel = 'N' ) I wonder why the cancel data in iv_List Table not showed in this query. What wrong with this.Please advice. This query want the data not exists in IV_List Table or exits in IV_List Table and fields Cancel = 'Y' Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

See Frank’s Answer below

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FrankKulash
    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:
    report
  • 2830360
    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:
    report
  • FrankKulash
    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:
    report
  • 2830360
    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:
    report
  • FrankKulash
    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:
    report
  • 2830360
    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:
    report
  • FrankKulash
    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:
    report
  • 2830360
    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:
    report
  • 2830360
    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:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following