SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stuck
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maggie21620
Starting Member

USA
27 Posts

Posted - 02/14/2014 :  11:33:28  Show Profile  Reply with Quote
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
Yak Posting Veteran

53 Posts

Posted - 02/14/2014 :  11:38:57  Show Profile  Reply with Quote
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.
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 02/14/2014 :  13:10:05  Show Profile  Reply with Quote
can you explain about the value? I get multiple cannot bound errors when i add a declare statement

none
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 02/14/2014 :  13:40:37  Show Profile  Reply with Quote
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.
Go to Top of Page

maggie21620
Starting Member

USA
27 Posts

Posted - 02/19/2014 :  12:07:36  Show Profile  Reply with Quote
thank you all for the input, i got it to work on Friday

none
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000