Solved

Errors in SQL Code

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

911 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

22 Experts available now in Live!

Get 1:1 Help Now