stuck - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stuck

Author

Topic

maggie21620
Starting Member

27 Posts

Posted - 2014-02-14 : 11:33:28

Hi all,
I have a qry written to pull information pertaining to an employee either by operator id or by their teamleader. I had it working until i started to tweek it:/ Please help:

DECLARE @name int;

SELECT DISTINCT
dv.LAST_NAME ,
dv.FIRST_NAME ,
dv.LOGIN_ID,
dv.PERNR ,
dv.WORK_EMAIL ,
dv.SUPERVISOR_NAME ,
dv.JOB_DESCR ,
dv.WORK_LOCATION ,
dv.WORK_LOCATION_CITY,
dv.EMPL_STATUS ,
dv.EMPL_STATUS_DESCR,
dv.EMPLID ,
dv.ORIGINAL_HIRE_DT ,
dv.WEEKLY_HOURS ,
dv.AD_LAST_LOGON ,
fb.MSOOPRID ,
fb.DEPT ,
fb.OPR ,
up.PHONENBR

FROM [freedomBilling].[dbo].[IDST_74_OPERATOR_ID] JOIN [HR_STORE].[dbo].[V_DIVISIONS] dv ON fb.MSOOPRID COLLATE Latin1_General_BIN = LEFT(dv.LOGIN_ID,9)
LEFT JOIN spitfire.comtrac.userDataPernr up ON up.CLASSIFY = CAST(dv.PERNR AS VARCHAR(100))
--JOIN freedom.dbo.corpList c ON c.corpNum = fb.ACCTCORP

WHERE fb.MSOOPRID <> '3RD%'
AND c.corpName = 'FREEDOM REGION'
--AND ID.DATASET = 'SYS1A'AND ORG_HIERARCHY_LEVEL1_DESCR LIKE 'NORTHEAST DIVISION%'
AND ORG_HIERARCHY_LEVEL2_DESCR in ('Freedom Region','Beltway Region','Keystone Region')

AND FB.STAT = '1' --ACTIVE MSOs--
AND DV.EMPL_STATUS IN ( '1', '3' )
AND DV.SUPERVISOR_NAME LIKE ('%' + @NAME +'%')
AND FB.OPR LIKE ('%' + @OPR +'%')
--AND DV.SUPERVISOR_NAME = 'GRAY'
ORDER BY DV.SUPERVISOR_NAME

I received the follwing error message:
Msg 137, Level 15, State 2, Line 38
Must declare the scalar variable "@OPR".

so I put a declare in the top of this program but it still is not working.

none

mandm
Posting Yak Master

120 Posts

Posted - 2014-02-14 : 11:38:57

Does the error change when you add a declare for @OPR? Also you would need to set @name and @OPR to some values to get the query to work.

maggie21620
Starting Member

27 Posts

Posted - 2014-02-14 : 13:10:05

can you explain about the value? I get multiple cannot bound errors when i add a declare statement

none

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-14 : 13:40:37

Let's take step back.

1. Did you add a DECLARE @OPR <DataType> before the select statement?
2. You have declared @Name as INT. That doesn't look right. If it is, then you should CAST/CONVERT it in your predicate.
3. For the value, Mandm is saying that they query won't work right if you do not set/specify a value for the variables you created. They will be NULL.

If you think you have your query to a point where is should execute, but it is still generating an error. Then post the complete query along with the error and we might be able to help.

maggie21620
Starting Member

27 Posts

Posted - 2014-02-19 : 12:07:36

thank you all for the input, i got it to work on Friday

none

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources