CODEWITHSUNDEEP
sqlsql-servert-sql
gotmike
gotmike

Reputation: 1615

T-SQL Query using WHERE clause with CONVERT or CAST

I'm having trouble with a specific T-SQL Query generated in code.

Below is the code, the first DECLARE block is sent from code, so those values will change.

The XML business has to do with turning the CSV NVARCHAR into a temporary table.

-- DECLARE BLOCK IS SENT BY CODE FROM DIALOG BOX
DECLARE @IdString NVARCHAR(MAX) ='F6LC1'
DECLARE @pm_StartDate DATETIME ='20180201'
-- END DECLARE BLOCK

DECLARE @listOfIds table(entityId nvarchar(50));

DECLARE @x XML 

SELECT @x = CAST('<A>'+ REPLACE(REPLACE(@IdString,' ',''),',','</A><A>')+ '</A>' AS XML)

INSERT INTO @listOfIds
SELECT t.value('.', 'NVARCHAR(50)') AS inVal
FROM @x.nodes('/A') AS x(t)

DECLARE @pm_StartDateTrim NVARCHAR(MAX) = LEFT(CONVERT(NVARCHAR(MAX),@pm_StartDate,112),6)
DECLARE @intStartDate INT = CAST(@pm_StartDateTrim AS INT)

SELECT @pm_StartDate
SELECT @pm_StartDateTrim
SELECT @intStartDate

SELECT   gl.[ACCTNUM]
        ,acc.[ACCTNAME]
        ,gl.[ENTITYID]
        ,CONVERT(INT,gl.[PERIOD]) AS periodConverted
        ,gl.[ACTIVITY]
        ,bldg.[BLDGNAME]
        ,bldg.[BLDGGLA]
        ,gl.[BALFOR]
FROM [dbo].[GLSUM] AS gl
        INNER JOIN [dbo].[BLDG] AS bldg ON gl.[ENTITYID] = bldg.[BLDGID]
        INNER JOIN [dbo].[GACC] AS acc ON gl.[ACCTNUM] = acc.[ACCTNUM]
WHERE gl.ENTITYID IN (SELECT entityId FROM @listOfIds)
        AND gl.BASIS = 'A'
        AND gl.ACTIVITY != 0
        AND gl.PERIOD NOT LIKE '%[^0-9]%'
        AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate
ORDER BY gl.PERIOD ASC

I'm TRYING To only include those records that have a PERIOD which is greater-than or equal-to the provided StartDate.

The data in the DB for PERIOD is just YYYYMM, so like 201502.

The data provided for the StartDate is DATETIME, so I'm converting to YYYYMM to do the comparison.

I'm TRYING to make them both INT so I can compare using < or > or = etc.

So if I leave in the 2nd to last row AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate then i get Conversion failed when converting the varchar value 'R2W274' to data type int.

The problem is that there is no value like that in my data.

When I comment out that line, I get data and R2W274 is not anywhere to be found in any field of any record.

I believe the R2W274 value is actually in the gl.ENTITYID field but not in any of my records, just generally in the DB.

So, I'm not sure why this query is even attempting to convert the gl.ENTITYID in the first place.

Any help?

UPDATE:

I added the recommended addition to the WHERE clause above. I still get the same error.

Upvotes: 1

Views: 1572

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7960

Please make sure there is no data returning from:

select * from [dbo].[GLSUM] where [Period] = 'R2W274'

Adding a try/catch block like that will help:

BEGIN TRY  
    -- Generate divide-by-zero error.  
    your select query comes here.  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    print 'error';
END CATCH;

Try/catch block reference: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

What I would do is to eliminate such data inside where clause:

WHERE gl.ENTITYID IN (SELECT entityId FROM @listOfIds)
        AND gl.BASIS = 'A'
        AND gl.ACTIVITY != 0
        AND gl.[PERIOD] not like '%[^0-9]%'
        AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate

Edit: I was expecting the change in where clause would fix it, surprised why it is not working. Please make sure you added the filter before the one including the conversion. Maybe it would be safer to use a temptable like:

select * 
into #temp_glsum
from [dbo].[GLSUM] gl
WHERE gl.[PERIOD] not like '%[^0-9]%'

and use #temp_glsum in your main query instead of [dbo].[GLSUM]

Upvotes: 3

Related Questions