Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating another column however need it as a filter

Posted on 2014-03-18
5
Medium Priority
?
176 Views
Last Modified: 2014-04-14
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_business_segment_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_getEmployeeIncentivePlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as CASH_PLAN,      
[dbo].[fn_getEmployeeDeferralPlansForYear](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
0
Comment
Question by:FAH_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:FAH_
ID: 39937081
I have looked into the issue and i have to add ' in the parameter values.so in the paramter i have to enter 'prodaa3'.

Is there a way to combine /concatenate a string and a parameter value?

SET @p_user = " ' " + {?@p_user} + " ' "
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39937162
>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 + '''
0
 

Author Comment

by:FAH_
ID: 39937170
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'
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39937198
>So I was wondering if there is a way to add ' to the beginning of the parameter value and the end as well
Yes, it would be four single-quote characters ( I had three in the previous example .. my bad)
SET @some_string = @some_string + '''' + @some_other_string + ''''

SELECT 'WHERE fruit_type = ' + '''' + 'banana' + ''''

Open in new window

0
 

Author Comment

by:FAH_
ID: 39937217
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
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question