April 4, 2011 at 11:22 pm
hi,
i need to pass multiple parameters in the data set.
and my query is as below.
i have used REPLACE function in order to separate the multiple values.
when i use this query in data set and preview the report its throwing error
REPLACE Function needs 3 arguments.
but my doubt is this query is working fine in work bench but y its throwing error in report? and how to solve it.
-------------------------------------------------------------------
DECLARE @SQLFields1 AS varchar(8000)
DECLARE @SQLFilter1 AS varchar(8000)
set @SQLFields1='
select
Mu.om_muname as Region ,
cu.om_countryidname as country_name,
Ia.om_internationalaccountname as GCU ,
Ka.om_customerunitidname as CU,
Ka.om_kaname as CRG,
opp.name as oppname,
opp.opportunityid as oppid,
cu.name as cusname ,
opp.owneridname as ACR,
opp.om_salesstagename as statusselling,
Opp.om_currencyidname as currency,
opp.om_statusofbusiness as buisnessstatus,
opp.om_priorityname as priorityname,
opp.om_pobq1_base as quarter1,
opp.om_pobq2_base as quarter2,
opp.om_pobq3_base as quarter3,
opp.om_pobq4_base as quarter4
'
IF @SelectedEntity=1
BEGIN
SET @SQLFilter1 ='
from FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid=cu.accountid
join FilteredOm_Country as Co
on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON (Mu.om_marketunitid = Co.om_marketunitid)
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid=Cu.om_keyaccountid
where Mu.om_muname =('''+@Region+''')
and cu.om_countryidname in ('''+REPLACE(@Country,',',''',''')+''')'+ '
'
end
if @SelectedEntity=1
BEGIN
exec (@SQLFields1 + @SQLFilter1 )
END
April 4, 2011 at 11:26 pm
This will be a whole lot easier to troubleshoot if you print the contents of the two variables that hold the dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2011 at 11:46 pm
i tried even that.
Altered query
DECLARE @SelectedEntity as int
DECLARE @Region AS nvarchar(4000)
DECLARE @Country AS nvarchar(4000)
set @Region='A4037F-1067-DF11-84EF-0017A4776866'
set @Country='577A9867-1967-DF11-84EF-0017A4776866,577A9867-1967-DF11-84EF-0017A4776866'
SET @SelectedEntity =1
DECLARE @SQLFields1 AS varchar(8000)
DECLARE @SQLFilter1 AS varchar(8000)
set @SQLFields1='
select
Mu.om_muname as Region ,
cu.om_countryidname as country_name,
Ia.om_internationalaccountname as GCU ,
Ka.om_customerunitidname as CU,
Ka.om_kaname as CRG,
opp.name as oppname,
opp.opportunityid as oppid,
cu.name as cusname ,
opp.owneridname as ACR,
opp.om_salesstagename as statusselling,
Opp.om_currencyidname as currency,
opp.om_statusofbusiness as buisnessstatus,
opp.om_priorityname as priorityname,
opp.om_pobq1_base as quarter1,
opp.om_pobq2_base as quarter2,
opp.om_pobq3_base as quarter3,
opp.om_pobq4_base as quarter4
'
IF @SelectedEntity=1
BEGIN
SET @SQLFilter1 ='
from FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid=cu.accountid
join FilteredOm_Country as Co
on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON (Mu.om_marketunitid = Co.om_marketunitid)
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid=Cu.om_keyaccountid
where Mu.om_muname =('''+@Region+''')
and cu.om_countryidname in ('''+REPLACE(@Country,',',''',''')+''')'+ '
'
end
if @SelectedEntity=1
BEGIN
select (@SQLFields1 + @SQLFilter1 )
END
i just replaced EXEC by SELECT
and i got the correct result too
Result
select Mu.om_muname as Region , cu.om_countryidname as country_name,
Ia.om_internationalaccountname as GCU , Ka.om_customerunitidname as CU,
Ka.om_kaname as CRG, opp.name as oppname, opp.opportunityid as oppid, cu.name as cusname ,
opp.owneridname as ACR, opp.om_salesstagename as statusselling, Opp.om_currencyidname as currency,
opp.om_statusofbusiness as buisnessstatus, opp.om_priorityname as priorityname,
opp.om_pobq1_base as quarter1, opp.om_pobq2_base as quarter2, opp.om_pobq3_base as quarter3,
opp.om_pobq4_base as quarter4 from FilteredOpportunity as opp join FilteredAccount as Cu
on opp.accountid=cu.accountid join FilteredOm_Country as Co on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu ON (Mu.om_marketunitid = Co.om_marketunitid)
join filteredom_InternationalAccount Ia ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka on Ka.om_keyaccountid=Cu.om_keyaccountid
where Mu.om_muname =('A4037F-1067-DF11-84EF-0017A4776866')
and cu.om_countryidname in ('577A9867-1967-DF11-84EF-0017A4776866','577A9867-1967-DF11-84EF-0017A4776866')
But don't know why its throwing error in report
April 12, 2011 at 11:50 am
I causually looked at your code and it appears to be correct. What is the error that is being thrown?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2011 at 11:59 am
Just for the sake of readability
DECLARE @SelectedEntity as int
DECLARE @Region AS nvarchar(4000)
DECLARE @Country AS nvarchar(4000)
set @Region = 'A4037F-1067-DF11-84EF-0017A4776866'
set @Country = '577A9867-1967-DF11-84EF-0017A4776866,577A9867-1967-DF11-84EF-0017A4776866'
SET @SelectedEntity = 1
DECLARE @SQLFields1 AS varchar(8000)
DECLARE @SQLFilter1 AS varchar(8000)
set @SQLFields1 = '
select
Mu.om_muname as Region ,
cu.om_countryidname as country_name,
Ia.om_internationalaccountname as GCU ,
Ka.om_customerunitidname as CU,
Ka.om_kaname as CRG,
opp.name as oppname,
opp.opportunityid as oppid,
cu.name as cusname ,
opp.owneridname as ACR,
opp.om_salesstagename as statusselling,
Opp.om_currencyidname as currency,
opp.om_statusofbusiness as buisnessstatus,
opp.om_priorityname as priorityname,
opp.om_pobq1_base as quarter1,
opp.om_pobq2_base as quarter2,
opp.om_pobq3_base as quarter3,
opp.om_pobq4_base as quarter4
'
IF @SelectedEntity = 1
BEGIN
SET @SQLFilter1 = '
from FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid=cu.accountid
join FilteredOm_Country as Co
on Co.om_countryid=Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON (Mu.om_marketunitid = Co.om_marketunitid)
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid=Cu.om_keyaccountid
where Mu.om_muname =(''' + @Region + ''')
and cu.om_countryidname in (''' + REPLACE(@Country , ',' , ''',''') + ''')'
+ '
'
end
if @SelectedEntity = 1
BEGIN
select
( @SQLFields1 + @SQLFilter1 )
END
select
Mu.om_muname as Region
, cu.om_countryidname as country_name
, Ia.om_internationalaccountname as GCU
, Ka.om_customerunitidname as CU
, Ka.om_kaname as CRG
, opp.name as oppname
, opp.opportunityid as oppid
, cu.name as cusname
, opp.owneridname as ACR
, opp.om_salesstagename as statusselling
, Opp.om_currencyidname as currency
, opp.om_statusofbusiness as buisnessstatus
, opp.om_priorityname as priorityname
, opp.om_pobq1_base as quarter1
, opp.om_pobq2_base as quarter2
, opp.om_pobq3_base as quarter3
, opp.om_pobq4_base as quarter4
from
FilteredOpportunity as opp
join FilteredAccount as Cu
on opp.accountid = cu.accountid
join FilteredOm_Country as Co
on Co.om_countryid = Cu.om_countryid
join FilteredOm_MarketUnit Mu
ON ( Mu.om_marketunitid = Co.om_marketunitid )
join filteredom_InternationalAccount Ia
ON Cu.om_internationalaccountid = Ia.om_internationalaccountid
join FilteredOm_KeyAccount Ka
on Ka.om_keyaccountid = Cu.om_keyaccountid
where
Mu.om_muname = ( 'A4037F-1067-DF11-84EF-0017A4776866' )
and cu.om_countryidname in ( '577A9867-1967-DF11-84EF-0017A4776866' ,
'577A9867-1967-DF11-84EF-0017A4776866' )
April 16, 2011 at 1:11 pm
Jeff Moden (4/12/2011)
I causually looked at your code and it appears to be correct. What is the error that is being thrown?
Looks like the OP either sussed his own problem or has no interest. Moving on... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply