Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 21st, 2010, 12:52 PM
Registered User
 
Join Date: May 2010
Posts: 4
Thanks: 3
Thanked 0 Times in 0 Posts
Default Openquery with array

Hi Im having some problems with passing a list to an openquery call. Evey thing process correctly until the group_no in call. I am setting this list with the following code:

Code:
declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210,1227,1233,1255,1258,1228'
select @temp_array =  replace(@temp_array,',',''''',''''')
set @array =  (''''+'!'+'''')
select @array = replace (@array,'!',@temp_array)
This works fine for 3 or less items in the list, however it bombs out for any thing greater than that


Entire Call:
Code:
select @sql = 'insert into #temp_refund_export (cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code, type)
SELECT cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code, ''cheque'' as type  
FROM OPENQUERY(Saslink,''SELECT cert_no, member_no, plan_no, ben_year, cov_code, group_no, act_code from g_certif
		  where ((plan_no = ' + '''''' + rtrim(convert varchar,@ic_TERM1)) + '''''' + ' and ben_year = ' +
			         '''''' + rtrim(convert(varchar,@ic_benefit_year1)) + '''''' + 
			 ') or 
		         (plan_no = ' + '''''' + rtrim(convert(varchar,@ic_TERM2)) + '''''' +
		          ' and ben_year = ' + '''''' + rtrim(convert(varchar,@ic_benefit_year2)) + '''''' + 
		        ')
		        )
		  and act_code = ' + '''''' + cast (@ic_act_code as varchar) + '''''' + '
		  and cov_code <> ' + '''''' + cast ('1' as varchar) + '''''' + '
		  and group_no in (' + '''' + rtrim(convert(varchar,@array)) + '''' + ')'+	    ''')'
 
EXEC(@sql)
The error I get it:

Server: Msg 105, Level 15, State 1, Line 12
Unclosed quotation mark before the character string ')'.


Any pointers in the right direction would be very appreciated

Skywalker

Last edited by skywalker; May 21st, 2010 at 01:42 PM..
 
Old May 21st, 2010, 08:28 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Can I ask a dumb question?

What is the data type of your group_no field???

Because if it is INT (or any numeric type), then you are going all wrong putting the '...' around each item, in the first place.

And if it is not INT, could you use it as INT?

In other words:
Code:
set @array = '1053,1112,1149,1210,1227,1233,1255,1258,1228'
...
select @sql = '.....
        and CONVERT(INT,group_no) in (' + @array + ')'+
       ...
???

If the values in @array are always integers, why work harder than that?
 
Old May 21st, 2010, 08:38 PM
Registered User
 
Join Date: May 2010
Posts: 4
Thanks: 3
Thanked 0 Times in 0 Posts
Default

No unfortunately its a char, otherwise it would have been a lot easier.
 
Old May 21st, 2010, 08:40 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Code:
declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210'
select @temp_array =  replace(@temp_array,',',''''',''''')
--  Okay, so at this point @temp_array should contain
-- 1053'',''1112'',''1149'',''1210
set @array =  (''''+'!'+'''')
-- so @array contains
--   '!'
select @array = replace (@array,'!',@temp_array)
-- and now @array contains
-- '1053'',''1112'',''1149'',''1210'
 
    and group_no in (' + '''' + rtrim(convert(varchar,@array)) + '''' + ')'+  
-- should result in
    and group_no in (''1053'',''1112'',''1149'',''1210'')'+
Which does look right.

Kind of a convoluted way to get there, but whatever.

I think I would have done this:
Code:
declare @array char (1000)
declare @temp_array char (500)
set @temp_array = '1053,1112,1149,1210'
select @array =  '$' + replace(@temp_array,',','$,$') + '$'
...
    and group_no in (' + replace(@array,'$','''''') + ')'+
Assuming you can't simply do the CONVERT(INT,group_no) trick.
The Following User Says Thank You to Old Pedant For This Useful Post:
skywalker (May 21st, 2010)
 
Old May 21st, 2010, 08:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Okay, so group_no is char. But are any of the values in the IN( ) list ever non-numeric??

Because if they aren't, you could do:
Code:
    and ( CASE WHEN ISNUMERIC(group_no) = 1 AND CONVERT(INT,group_no) IN (' + @array + ') THEN 1
           ELSE 0 END ) = 1 ' +
The Following User Says Thank You to Old Pedant For This Useful Post:
skywalker (May 21st, 2010)
 
Old May 21st, 2010, 10:23 PM
Registered User
 
Join Date: May 2010
Posts: 4
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Thanks for the two post's, you are right in assuming that the char field will only every contain numeric's. Ill try out the two methods that you suggest, although why would the openquery accept 3 elements in the list but not 4.

I realise that the solution I setup is bit long winded, Im more at ease with Perl in which you could do a very simple 1 line regular expression.

Thanks

skywalker
 
Old May 25th, 2010, 10:42 AM
Registered User
 
Join Date: May 2010
Posts: 4
Thanks: 3
Thanked 0 Times in 0 Posts
Smile

Thanks Old Pedant, the replace worked perfectly


Code:
select @array =  '$' + replace(@temp_array,',','$,$') + '$'
...
    and group_no in (' + replace(@array,'$','''''') + ')'+





Similar Threads
Thread Thread Starter Forum Replies Last Post
openquery syntax mpankuj SQL Server 2000 11 March 15th, 2010 01:27 AM
T-SQL MERGE and OPENQUERY 4thhorseman SQL Server 2008 1 December 3rd, 2009 09:42 AM
OPENQUERY Problem tsimsha SQL Server 2005 0 October 18th, 2007 06:18 AM
Openquery statements aven SQL Server 2000 2 January 11th, 2006 01:33 AM
openquery gr_chris SQL Server 2000 0 September 16th, 2005 08:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.