September 5, 2008 at 11:03 am
I have a UDF I need to call twice. UDF name is dbo.ufn_splitToStringTable. I call the UDF to match the results based on zipcode.so inner join here.
left outer JOIN PI_Common AS p ON p.accountID = a.accountID
LEFT OUTER JOIN ABI_customerFlag ac on ac.abinum = a.abinum
inner join dbo.ufn_splitToStringTable(@zipcodes) z on z.itemList = a.zipcode
inner join dbo.ufn_splitToStringTable(@zipcodes1) z1 on z1.itemList = a.zipcode
here @zipcodes, @zipcodes1 may or may not be empty.
If @zipcodes is empty, matching should goto 2nd inner join,
if @zipcodes1 is empty, matching should be in 1st inner join.
If @zipcodes, @zipcodes1 are both not empty, matching should be done by calling the UDF both times.
If I use one inner join I get the required results. If I use both these inner joins and set @zipcodes1 is empty, I get 0.
I cannot modify the UDF so I have to find some way with joins here.
So how to fix this issue? I mean I need the results matching the UDF. How can I have 2 inner joins and still get the results??
Any ideas/suggestions welcome and appreciated!!!
Thanks,
Vijeya Shobana
September 5, 2008 at 11:31 am
here i want to join a few tables and extract the results. The LEFT OUTER JOINS are used on PI_Common p , ABI_Customer ac and Account a tables. Now I have to filter the resulting records based on zipcodes.
UDF ufn_splitToStringTable is used to split the zipcodes. @zipcodes, @zipcodes are set dynamically in the stored proc.
If @zipcodes is empty, the 1st inner join should be skipped and results should be matched on @zipcodes1
If @zipcodes1 is empty, the 2nd inner join should be skipped and results should be matched on @zipcodes.
If @zipcodes, @zipcodes1 both exist, the inner joins should not be skipped and results should be matched on both @zipcodes and @zipcodes1.
I don't see a best way for this problem, I am sure it should be very simple but right now, i'm missing it.
Thanks & Regards
September 5, 2008 at 11:32 am
Are you sure that you want to return every item in @Zipcodes times every item in @Zipcodes1?
It seems like what you really want is the UNION of those 2 lists, not their Product, which would also fix the empty list problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 11:35 am
Oops, we crossed replies...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 11:41 am
well yes. I need a UNION here. I had one inner join to get the desired result. Now due to some enhancements I have to do the matching twice, hence called UDF twice so I was thinking about using inner join twice.
but how can i use Union here??? The full code is something like this...
INSERT into @retList
(source
,accountID
,ABINum
,companyName)
select distinct 'account' as source
,a.accountID
,a.ABINum
,a.companyName
from AccountSearch AS a (nolock)
left outer JOIN PI_Common AS p ON p.accountID = a.accountID
LEFT OUTER JOIN GroupPurchasingOrganizations_PI gpo_p on gpo_p.piID = p.piID --CR 41493
LEFT OUTER JOIN IDN_PI idn_p on idn_p.piID = p.piID
LEFT OUTER JOIN MHS_PI mhs_p on mhs_p.piID = p.piID
LEFT OUTER JOIN BrandName brName on brName.BrandNameId = p.BrandNameId
LEFT OUTER JOIN businessDivision b on p.businessdivisionid = b.businessdivisionid
LEFT OUTER JOIN dbo.SalesRange AS sr ON p.estimate_salesRangeID = sr.salesRangeID
LEFT OUTER JOIN dbo.Task AS t ON t.taskID = p.taskID_last
LEFT OUTER JOIN dbo.Task AS t2 ON t2.taskID = p.taskID_next
LEFT OUTER JOIN dbo.competitor_PI cp on cp.piID = p.piID
LEFT OUTER JOIN ABI_customerFlag ac on ac.abinum = a.abinum --ac.accountid = a.accountid
LEFT OUTER JOIN IMPORT_POWERRANKINGS pr on
a.abinum = pr.abinum and pr.businessDivisionID = @businessdivisionID
inner join dbo.ufn_splitToStringTable(@zipcodes) z on z.itemList = a.zipcode
inner join dbo.ufn_splitToStringTable(@zipcodes1) z1 on z1.itemList = a.zipcode
where
....10 to 15 conditions...
Though I need a UNION function I don't know where to put that here :w00t:. May be its very simple but I don't know.ooopppsssss.....
September 5, 2008 at 11:50 am
Like this:
declare @businessdivisionID int
declare @zipcodes varchar(999)
declare @zipcodes1 varchar(999)
INSERT into #@retList
(source
,accountID
,ABINum
,companyName)
Select distinct 'account' as source
,a.accountID
,a.ABINum
,a.companyName
From AccountSearch AS a (nolock)
left outer JOIN PI_Common AS p ON p.accountID = a.accountID
LEFT OUTER JOIN GroupPurchasingOrganizations_PI gpo_p on gpo_p.piID = p.piID --CR 41493
LEFT OUTER JOIN IDN_PI idn_p on idn_p.piID = p.piID
LEFT OUTER JOIN MHS_PI mhs_p on mhs_p.piID = p.piID
LEFT OUTER JOIN BrandName brName on brName.BrandNameId = p.BrandNameId
LEFT OUTER JOIN businessDivision b on p.businessdivisionid = b.businessdivisionid
LEFT OUTER JOIN dbo.SalesRange AS sr ON p.estimate_salesRangeID = sr.salesRangeID
LEFT OUTER JOIN dbo.Task AS t ON t.taskID = p.taskID_last
LEFT OUTER JOIN dbo.Task AS t2 ON t2.taskID = p.taskID_next
LEFT OUTER JOIN dbo.competitor_PI cp on cp.piID = p.piID
LEFT OUTER JOIN ABI_customerFlag ac on ac.abinum = a.abinum --ac.accountid = a.accountid
LEFT OUTER JOIN IMPORT_POWERRANKINGS pr on
a.abinum = pr.abinum and pr.businessDivisionID = @businessdivisionID
INNER JOIN
( Select * from dbo.ufn_splitToStringTable(@zipcodes)
UNION All
Select * from dbo.ufn_splitToStringTable(@zipcodes1)) zu On zu.itemlist = a.zipcode
where
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 12:11 pm
Thanks for your reply Barry. I tried it and yes, I got the required results 🙂 I'm using this particular query in at least 2 other functions and in dozen other places in this stored proc. So like I got a tip from another user, I'm placing this same UNION statement inside a table variable and calling that table variable every where I need to use this UNION. I tried that and that is working too. Thanks a lot for your help and patience Barry. I appreciate your help 🙂
Thanks and regards
September 5, 2008 at 12:26 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply