?
Solved

Errors in SQL Code

Posted on 2015-01-16
6
Medium Priority
?
95 Views
Last Modified: 2015-03-05
I'm trying to resolve some errors in a report.
I get 2 errors:
1. Conversion failed when converting the varchar value"TC1-ULSND" to data type int
2. Obeject #rpt0190 in the database.

Wanted to see if someone can give me pointers on how to fix that.

Here is my code:

USE [LawsonRPT];

--   @days_back INT    {?days_back}
-- Uncomment after testing
--DECLARE @DaysBack INT = {?DaysBack}
--DECLARE @DaysBack INT = 180

-- Comment before sent to production
DECLARE @DaysBack INT = 180

SELECT dtl.COMPANY AS Company,
       dtl.EMPLOYEE AS Employee,
       dtl.EMP_STATUS AS EmpStatus,
       dtl.FIRST_NAME AS FirstName,
       dtl.LAST_NAME AS LastName,
       dtl.JOB_CODE AS JobCode,
       dtl.[check] AS CheckDate,
       RTRIM(emp.PROCESS_LEVEL) AS ProcessLevel,
       RTRIM(pos.[DESCRIPTION]) AS PositionDesc,
       rtrim (sup.FIRST_NAME) + ',' + rtrim (sup.LAST_NAME) AS Supervisor
  --INTO #RPT0190
  FROM ( SELECT emp.COMPANY,
                emp.EMPLOYEE,
                emp.EMP_STATUS,
                RTRIM(emp.FIRST_NAME) AS FIRST_NAME,
                RTRIM(emp.LAST_NAME) AS LAST_NAME,
                RTRIM(emp.JOB_CODE) AS JOB_CODE,
                emp.SUPERVISOR,
                emp.LAST_DAY_PAID,
                MAX (pym.CHECK_DATE) AS [check]
          FROM  LSPROD.dbo.EMPLOYEE emp WITH (NOLOCK)
                LEFT JOIN LSPROD.dbo.PAYMASTR pym WITH (NOLOCK)
                       ON emp.COMPANY = pym.COMPANY
                      AND emp.EMPLOYEE = pym.EMPLOYEE
         WHERE  emp.EMP_STATUS NOT IN ('10', '11', '17')
           AND  emp.EMPLOYEE <> 5498
           AND  CAST ( REPLACE (RTRIM (emp.JOB_CODE), 'MEDICAL', 0) AS INT) > 299
           AND  emp.TERM_DATE = '01/01/1753'
         GROUP  BY emp.COMPANY,
                   emp.EMPLOYEE,
                   emp.EMP_STATUS,
                   emp.FIRST_NAME,
                   emp.LAST_NAME,
                   emp.JOB_CODE,
                   emp.SUPERVISOR,
                   emp.LAST_DAY_PAID
        ) dtl
       LEFT JOIN LSPROD.dbo.EMPLOYEE emp WITH (NOLOCK)
              ON dtl.COMPANY = emp.COMPANY
             AND dtl.EMPLOYEE = emp.EMPLOYEE
       LEFT JOIN LSPROD.dbo.PAPOSITION pos
              ON dtl.COMPANY = pos.COMPANY
             AND emp.R_POSITION = pos.R_POSITION
       LEFT JOIN LSPROD.dbo.HRSUPER hsu (NOLOCK)
              ON emp.COMPANY = hsu.COMPANY
             AND emp.SUPERVISOR = hsu.CODE
       LEFT JOIN LSPROD.dbo.EMPLOYEE sup (NOLOCK)
              ON emp.COMPANY = sup.COMPANY
             AND hsu.EMPLOYEE = sup.EMPLOYEE
 WHERE  dtl.[check] < getdate () - 180
 --@DaysBack
   AND  GETDATE () BETWEEN pos.EFFECT_DATE
                       AND CASE
                               WHEN pos.END_DATE = '01/01/1753' THEN
                                  '12/31/9999'
                               ELSE
                                  pos.EFFECT_DATE
                           END
 ORDER BY dtl.EMPLOYEE;

SELECT  Company,
        Employee,
        EmpStatus,
        FirstName,
        LastName,
        JobCode,
        CheckDate,
        ProcessLevel,
        PositionDesc,
        Supervisor,
        DB_NAME() AS DBName
  FROM  #RPT0190;

DROP TABLE #RPT0190;
0
Comment
Question by:metalteck
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40553857
Without table structure its really hard , anyway try commenting this line "AND  CAST ( REPLACE (RTRIM (emp.JOB_CODE), 'MEDICAL', 0) AS INT) > 299" , if its working then the Job code will have several values, need to have multiple replace statements
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40554301
Agree.

If you only want JObcodes that are Medicalxxxx then add a where clause that filters out the others

emp.JOB_CODE LIKE  '%MEDICAL%'

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40555037
Since you're explicitly trying to convert emp.JOB_CODE to an int, that seems the most likely place for the conversion error (as the others indicated).  So, JOB_CODE probably sometimes contains "TC1-ULSND", in which case you have to decide how you want to handle that (and possibly other non-numeric values in that column).  OTOH, there is also the possibility that there is an implicit conversion somewhere in the code (eg. trying to compare an int column and a varchar column), and that's where you're getting the error, but the Cast As Int is the obvious place to start.

 FWIW, you included the MS SQL topic, and I don't recognize that form of the Replace function.  It may just be a form that I'm not familiar with, or maybe your db is not really MS SQL (in which case the topic should probably be removed).  If your db really is MS SQL, you might want to double-check those Replace arguments and make sure they're correct.


 As for your second item, "Obeject #rpt0190 in the database" is not really an error message, but if you're getting an error on #RPT0190, you have the line that would fill it (--INTO #RPT0190) commented out, so you don't create it, and then you have a Select that tries to read it.  So I would expect an error.  You may only need to uncomment the Into line.

 James
0
 

Accepted Solution

by:
metalteck earned 0 total points
ID: 40584993
the issue with the report was because someone force a non-numeric value into the field and was causing the report to fail.

Thanks for all the help
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.
Suggested Courses

601 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