FAH_
asked on
Creating another column however need it as a filter
Hi
Instead of hard coding I'm adding the username from the parameter and its creating another column.
before this query worked if I set the @p_user='prodaa3'
I'm getting this error
"Invalid column name'prodaa3'. [Database Vendor Code 207]"
declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20)
set @p_user ={?@p_user}
SET @p_business_segment_id ={?@p_business_segment_id}
set @p_year ={?@p_year}
set @p_business_segment_id = dbo.ufnParamOrNull(@p_busi ness_segme nt_id)
select ba_id, bu_id, bg_id, lob_id, pc_id
into #userbgs
from user_bgs
where award_year = @p_year and cycle = 12 and [user_name] = @p_user
and ba_id = (Case When @p_business_segment_id is null Then ba_id Else @p_business_segment_id End)
SELECT B_AREA_DESC , B_AREA, B_UNIT_DESC, [EXEC] Executive, RL Reporting_line, MICRO as Micro,MNGR_DESC, eiv.EMPLOYEE_ID, eiv.LAST_NAME, eiv.GIVEN_NAMES,
[dbo].[fn_getEmployeeIncen tivePlansF orYear](ei v.EMPLOYEE _PROFILE_I D, eiv.AWARD_YEAR) as CASH_PLAN,
[dbo].[fn_getEmployeeDefer ralPlansFo rYear](eiv .EMPLOYEE_ PROFILE_ID , eiv.AWARD_YEAR) as DEF_PLAN,
EXPAT_COUNTRY, EXPAT_TYPE, PS_START_DT,
r.NAME AS REGULATOR, rr.NAME AS REGULATOR_RULE, cs.CUSTOM_DEFERRAL_PCT, rr.DEFERRAL_PCT, cs.EFFECTIVE_DT, cs.COMMENTS as REGULATOR_COMMENTS
FROM dbo.CODE_STAFF AS cs INNER JOIN
dbo.REGULATOR AS r ON cs.REGULATOR_ID = r.REGULATOR_ID INNER JOIN
dbo.REGULATORY_RULE AS rr ON cs.REGULATORY_RULE_ID = rr.REGULATORY_RULE_ID inner join
EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID=cs .EMPLOYEE_ PROFILE_ID and eiv.AWARD_YEAR=@p_year and eiv.CYCLE = 12
inner join #userbgs ubgs on ubgs.pc_id = eiv.PRODUCT_ID
left join
( select rh.EMPLOYEE_PROFILE_ID, c.DESCRIPTION as EXPAT_COUNTRY, et.NAME as EXPAT_TYPE, PS_START_DT
from (select EMPLOYEE_PROFILE_ID, max(PS_START_DT) PS_START_DT1 from
RESIDENCE_HISTORY rh inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID
--where et.NAME not in ('Short-Term','Home')-- and PS_START_DT<'2011-04-01'
group by EMPLOYEE_PROFILE_ID
)rhm
inner join RESIDENCE_HISTORY rh on rhm.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID and PS_START_DT=PS_START_DT1
inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID
inner join dbo.COUNTRY c on c.COUNTRY_ID = rh.COUNTRY_ID
)rh on eiv.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID
DROP TABLE #userbgs
Instead of hard coding I'm adding the username from the parameter and its creating another column.
before this query worked if I set the @p_user='prodaa3'
I'm getting this error
"Invalid column name'prodaa3'. [Database Vendor Code 207]"
declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20)
set @p_user ={?@p_user}
SET @p_business_segment_id ={?@p_business_segment_id}
set @p_year ={?@p_year}
set @p_business_segment_id = dbo.ufnParamOrNull(@p_busi
select ba_id, bu_id, bg_id, lob_id, pc_id
into #userbgs
from user_bgs
where award_year = @p_year and cycle = 12 and [user_name] = @p_user
and ba_id = (Case When @p_business_segment_id is null Then ba_id Else @p_business_segment_id End)
SELECT B_AREA_DESC , B_AREA, B_UNIT_DESC, [EXEC] Executive, RL Reporting_line, MICRO as Micro,MNGR_DESC, eiv.EMPLOYEE_ID, eiv.LAST_NAME, eiv.GIVEN_NAMES,
[dbo].[fn_getEmployeeIncen
[dbo].[fn_getEmployeeDefer
EXPAT_COUNTRY, EXPAT_TYPE, PS_START_DT,
r.NAME AS REGULATOR, rr.NAME AS REGULATOR_RULE, cs.CUSTOM_DEFERRAL_PCT, rr.DEFERRAL_PCT, cs.EFFECTIVE_DT, cs.COMMENTS as REGULATOR_COMMENTS
FROM dbo.CODE_STAFF AS cs INNER JOIN
dbo.REGULATOR AS r ON cs.REGULATOR_ID = r.REGULATOR_ID INNER JOIN
dbo.REGULATORY_RULE AS rr ON cs.REGULATORY_RULE_ID = rr.REGULATORY_RULE_ID inner join
EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID=cs
inner join #userbgs ubgs on ubgs.pc_id = eiv.PRODUCT_ID
left join
( select rh.EMPLOYEE_PROFILE_ID, c.DESCRIPTION as EXPAT_COUNTRY, et.NAME as EXPAT_TYPE, PS_START_DT
from (select EMPLOYEE_PROFILE_ID, max(PS_START_DT) PS_START_DT1 from
RESIDENCE_HISTORY rh inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID
--where et.NAME not in ('Short-Term','Home')-- and PS_START_DT<'2011-04-01'
group by EMPLOYEE_PROFILE_ID
)rhm
inner join RESIDENCE_HISTORY rh on rhm.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID and PS_START_DT=PS_START_DT1
inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID
inner join dbo.COUNTRY c on c.COUNTRY_ID = rh.COUNTRY_ID
)rh on eiv.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID
DROP TABLE #userbgs
>set @p_user ={?@p_user}
What's the purpose of the {? } ?
I'm not familiar with this, and normally @p_user would be passed into the Stored Proc as a parameter. Also, T-SQL does not have any abilities to prompt the user for a value such as SET @p_user = 'Enter a user id here'
>Is there a way to combine /concatenate a string and a parameter value?
Yes. Like...
SET @some_string = @some_string + @some_other_string
or
SET @some_string = @some_string + ''' + @some_other_string + '''
What's the purpose of the {? } ?
I'm not familiar with this, and normally @p_user would be passed into the Stored Proc as a parameter. Also, T-SQL does not have any abilities to prompt the user for a value such as SET @p_user = 'Enter a user id here'
>Is there a way to combine /concatenate a string and a parameter value?
Yes. Like...
SET @some_string = @some_string + @some_other_string
or
SET @some_string = @some_string + ''' + @some_other_string + '''
ASKER
set@p_user ={?@p_user} is coming from crystal reports
?@p_user = paramter
if enter in the parameter prodaa3 it looks for a column and throws an error however if I enter 'prodaa3' in the parameter it works
So I was wondering if there is a way to add ' to the beginning of the parameter value and the end as well so the user should only enter
prodaa3 and then the system does the concatenate and puts this result into @p_user
so @p_user would be equal to 'prodaa3'
?@p_user = paramter
if enter in the parameter prodaa3 it looks for a column and throws an error however if I enter 'prodaa3' in the parameter it works
So I was wondering if there is a way to add ' to the beginning of the parameter value and the end as well so the user should only enter
prodaa3 and then the system does the concatenate and puts this result into @p_user
so @p_user would be equal to 'prodaa3'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm getting this error
Msg 207, Level 16, State 1, Line 9
Invalid column name 'prodaa3'.
declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20),
@p_user_temp varchar(20)
set @p_user_temp =prodaa3
SET @p_user = ''''+@p_user_temp + ''''
SET @p_business_segment_id = '2'
set @p_year = 2011
Select @p_user
Msg 207, Level 16, State 1, Line 9
Invalid column name 'prodaa3'.
declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20),
@p_user_temp varchar(20)
set @p_user_temp =prodaa3
SET @p_user = ''''+@p_user_temp + ''''
SET @p_business_segment_id = '2'
set @p_year = 2011
Select @p_user
ASKER
Is there a way to combine /concatenate a string and a parameter value?
SET @p_user = " ' " + {?@p_user} + " ' "