A puzzle for the DBA crowd.
When a report asks you to pick multiple values for something (i.e. all of the contributions in these 6 campaigns) it passes them as a comma delimited string. So in SQL we might get a list of campaigns like this:
@campaign_str = '124,238,125,126,127,234,128'
The variable is then passed to the Stored Procedure and it gets parsed out. The logic I've seen most often to do this is:
and (charindex(','+convert(varchar,t.campaign_no)+',' , ','+@campaign_str+',') > 0
This basically says "find the position in the string @campaign_str where t.campaign_no is found and if the position is > 0, include the record in the result set"
Now the problem.
I have one campaign that causes others to be excluded. The offending campaign's position in the string doesn't matter. It doesn't remove ALL other campaigns, just a select few and for the life of me I can't figure out where the SNAFU is. If I remove the offending campaign_no from @campaign_str, everything works out great.
I'm hoping someone else has run into this before, spent a sleepless night or two being driven crazy by it and then, in a fit of divine revelation, discovered the problem and is now willing to share.
I promise dinner and the beverages of your choosing at next year's conference for a solution.
Thanks
Hi Levi,
Is it possible that this particular campaign_no has some white space that needs to be trimmed? Have you tried running the stored procedure manually typing the campaign_no's manually instead of having them called through the cursor?
Naomi
Manually typing the string gives the same result. If I include the offending number I get the same exclusions whether it is run from Tessitura proper or from SQL manager.
There are no cursors in the stored procedure (one of the reasons I like this method, current problems aside). The charindex process means it doesn't need to loop through anything, just determine if the substring we care about exists in the larger string.
So, to test this I wrote a smaller bit of sql:
@campaign_str = '133,124,238,125,274,126,127,128,234'
select campaign_no, charindex(','+convert(varchar,campaign_no)+',' , ','+@campaign_str+',')from t_campaignwhere charindex(','+convert(varchar,campaign_no)+',' , ','+@campaign_str+',') > 0order by charindex(','+convert(varchar,campaign_no)+',' , ','+@campaign_str+',')
Which gives me:
campaign_no position----------- -----------133 1124 5238 9125 13274 17126 21127 25128 29234 33
So the charindex bit is correctly picking up all of the values it should.
I am beginning to think I am on the wrong track with this. Perhaps the problem is elsewhere. But it just seems odd that this one value can have such an odd effect.
You may want to post the rest of your code, if that part of the code is running correctly the issue might be in another part of the code, I agree with you that is the likely suspect but just in case.
Here is the complete SP.
(It's days like this where I realize how strange my job is. When they said I had to take a foreign language in high school, it would have been nice if they offered SQL as an option.)
The report is supposed to return all gifts, pledges, and pledge payments made within the date range with the dollar range for the (optionally) selected list and/or campaigns.
USE [impresario]GO/****** Object: StoredProcedure [dbo].[lrp_contrib_and_payment] Script Date: 11/02/2009 09:19:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[lrp_contrib_and_payment]( @campaign_str varchar(30), @start_dt datetime, @end_dt datetime, @start_amt money, @end_amt money, @list_no int)ASSelect a.* ,b.street1,b.street2,b.city,b.state,b.postal_codefrom ( --Pledge Payments
select c1.customer_no, cs.esal1_desc, cs.esal2_desc, cs.lsal_desc, t.trn_type, tr.description as 'Transaction', t.trn_dt, t.trn_amt, t.ref_no, c1.cont_dt, c1.cont_amt, c1.recd_amt, t.campaign_no, cr.description as 'Campaign', t.fund_no, fr.description as 'Fund', t.source_no, sr.source_name as 'Source', t.appeal_no, ar.description as 'Appeal', cast(CASE when c1.notes like '%anonymous%' then 'Y' else 'N' end as char(1)) as Anon_flag, c1.notes from t_transaction t join t_contribution c1 on t.ref_no = c1.ref_no join tx_cust_sal cs on c1.customer_no = cs.customer_no and cs.signor = 2 join tr_transaction_type tr on t.trn_type = tr.id join t_campaign cr on t.campaign_no = cr.campaign_no join t_fund fr on t.fund_no = fr.fund_no join tx_appeal_media_type sr on t.source_no = sr.source_no join t_appeal ar on t.appeal_no = ar.appeal_no where t.create_dt between @start_dt and @end_dt -- In the date range and abs(t.trn_amt) between @start_amt and @end_amt -- In the amount range, + or - and t.trn_type = 3 -- Optional Campaign selection: and (charindex(','+convert(varchar,t.campaign_no)+',' , ','+@campaign_str+',') > 0 or isnull(@campaign_str,'') = '') -- Optional List selection: and (c1.customer_no in (select customer_no from t_list_contents where list_no = @list_no) or isnull(@list_no,0)=0 ) Union All --Gifts and pledges select c1.customer_no, cs.esal1_desc, cs.esal2_desc, cs.lsal_desc, t.trn_type, tr.description as 'Transaction', t.trn_dt, t.trn_amt, t.ref_no, c1.cont_dt, c1.cont_amt, c1.recd_amt, t.campaign_no, cr.description as 'Campaign', t.fund_no, fr.description as 'Fund', t.source_no, sr.source_name as 'Source', t.appeal_no, ar.description as 'Appeal', cast(CASE when c1.notes like '%anonymous%' then 'Y' else 'N' end as char(1)) as Anon_flag, c1.notes from t_transaction t join t_contribution c1 on t.ref_no = c1.ref_no join tx_cust_sal cs on c1.customer_no = cs.customer_no and cs.signor = 2 join tr_transaction_type tr on t.trn_type = tr.id join t_campaign cr on t.campaign_no = cr.campaign_no join t_fund fr on t.fund_no = fr.fund_no join tx_appeal_media_type sr on t.source_no = sr.source_no join t_appeal ar on t.appeal_no = ar.appeal_no where c1.create_dt between @start_dt and @end_dt -- In the date range and abs(c1.cont_amt) between @start_amt and @end_amt -- In the amount range, + or - and t.trn_type in (1,2) -- Optional Campaign selection: and (charindex(','+convert(varchar,t.campaign_no)+',' , ','+@campaign_str+',') > 0 or isnull(@campaign_str,'') = '') -- Optional List selection: and (c1.customer_no in (select customer_no from t_list_contents where list_no = @list_no) or isnull(@list_no,0)=0 )) a left join t_address b (nolock) on a.customer_no = b.customer_no and primary_ind = 'Y'RETURN
Ken,
You win the dinner! Changing it to @campaign_str varchar(100) did the trick.
Let me know your favorite type of food and I'll find somewhere in D.C. that has it. Better yet, I'm hoping to visit Sydney this summer and could stand you to a night out then.
Sydney is quickly becoming my favorite city in the world just based on the folks I met at the conference this year. Can't wait to visit. Say hello to Aaron Curran for me.
Just FYI, a multi-select string parameter can be up to 4000 characters. Why skimp?
J
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei Sent: Monday, November 02, 2009 5:00 PM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] A SQL conundrum
From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com> Sent: 11/2/2009 3:49:41 PM
Hi Levi Just a thought - try making the @campaign_str parameter much longer than 30? I've often thought that SQL should be like Latin was in Europe a century or two ago - every educated person should be able to speak it. Ken McSwain +61 (0)418 659 360 Sent from BlackBerry ----- Original Message ----- From: Tessitura Technical Forum To: Ken McSwain Sent: Tue Nov 03 06:05:10 2009 Subject: Re: [Tessitura Technical Forum] A SQL conundrum Here is the complete SP. (It's days like this where I realize how strange my job is. When they said I had to take a foreign language in high school, it would have been nice if they offered SQL as an option.) The report is supposed to return all gifts, pledges, and pledge payments made within the date range with the dollar range for the (optionally) selected list and/or campaigns. USE [impresario] GO /****** Object: StoredProcedure [dbo].[lrp_contrib_and_payment] Script Date: 11/02/2009 09:19:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[lrp_contrib_and_payment]( @campaign_str varchar(30), @start_dt datetime, @end_dt datetime, @start_amt money, @end_amt money, @list_no int ) AS Select a.* ,b.street1,b.street2,b.city,b.state,b.postal_code from ( --Pledge Payments select c1.customer_no, cs.esal1_desc, cs.esal2_desc, cs.lsal_desc, t.trn_type, tr.description as 'Transaction', t.trn_dt, t.trn_amt, t.ref_no, c1.cont_dt, c1.cont_amt, c1.recd_amt, t.campaign_no, cr.description as 'Campaign', t.fund_no, fr.description as 'Fund', t.source_no, sr.source_name as 'Source', t.appeal_no, ar.description as 'Appeal', cast(CASE when c1.notes like '%anonymous%' then 'Y' else 'N' end as char(1)) as Anon_flag, c1.notes from t_transaction t join t_contribution c1 on t.ref_no = c1.ref_no join tx_cust_sal cs on c1.customer_no = cs.customer_no and cs.signor = 2 join tr_transaction_type tr on t.trn_type = tr.id join t_campaign cr on t.campaign_no = cr.campaign_no join t_fund fr on t.fund_no = fr.fund_no join tx_appeal_media_type sr on t.source_no = sr.source_no join t_appeal ar on t.appeal_no = ar.appeal_no where t.create_dt between @start_dt and @end_dt -- In the date range and abs(t.trn_amt) between @start_amt and @end_amt -- In the amount range, + or - and t.trn_type = 3 -- Optional Campaign selection: and (charindex(','+convert(varchar,t.campaign_no)+',' , ','+@campaign_str+',') > 0 or isnull(@campaign_str,'') = '') -- Optional List selection: and (c1.customer_no in (select customer_no from t_list_contents where list_no = @list_no) or isnull(@list_no,0)=0 ) Union All --Gifts and pledges select c1.customer_no, cs.esal1_desc, cs.esal2_desc, cs.lsal_desc, t.trn_type, tr.description as 'Transaction', t.trn_dt, t.trn_amt, t.ref_no, c1.cont_dt, c1.cont_amt, c1.recd_amt, t.campaign_no, cr.description as 'Campaign', t.fund_no, fr.description as 'Fund', t.source_no, sr.source_name as 'Source', t.appeal_no, ar.description as 'Appeal', cast(CASE when c1.notes like '%anonymous%' then 'Y' else 'N' end as char(1)) as Anon_flag, c1.notes from t_transaction t join t_contribution c1 on t.ref_no = c1.ref_no join tx_cust_sal cs on c1.customer_no = cs.customer_no and cs.signor = 2 join tr_transaction_type tr on t.trn_type = tr.id join t_campaign cr on t.campaign_no = cr.campaign_no join t_fund fr on t.fund_no = fr.fund_no join tx_appeal_media_type sr on t.source_no = sr.source_no join t_appeal ar on t.appeal_no = ar.appeal_no where c1.create_dt between @start_dt and @end_dt -- In the date range and abs(c1.cont_amt) between @start_amt and @end_amt -- In the amount range, + or - and t.trn_type in (1,2) -- Optional Campaign selection: and (charindex(','+convert(varchar,t.campaign_no)+',' , ','+@campaign_str+',') > 0 or isnull(@campaign_str,'') = '') -- Optional List selection: and (c1.customer_no in (select customer_no from t_list_contents where list_no = @list_no) or isnull(@list_no,0)=0 )) a left join t_address b (nolock) on a.customer_no = b.customer_no and primary_ind = 'Y' RETURN From: Naomi Williams Sent: 11/2/2009 12:52:30 PM You may want to post the rest of your code, if that part of the code is running correctly the issue might be in another part of the code, I agree with you that is the likely suspect but just in case. Naomi This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! Please consider the environment before printing this email. =====This message is intended for the addressee(s) named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust===== This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Won’t it be nice when we get to SQL Server 2008, which offers table-valued parameters and eliminates the need for this entirely!
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan Creps Sent: Monday, November 02, 2009 5:05 PM To: Levine, Alan Subject: RE: [Tessitura Technical Forum] A SQL conundrum
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!