?
Solved

Errors in SQL Code

Posted on 2015-01-16
6
Medium Priority
?
85 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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