Solved

Errors in SQL Code

Posted on 2015-01-16
6
69 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
6 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 100

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 34

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now