Solved

Errors in SQL Code

Posted on 2015-01-16
6
82 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
[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
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 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

687 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