CODEWITHSUNDEEP
sqlsql-server
Kirat
Kirat

Reputation: 141

Data type conversion in SQL query

I have the following query in which I am trying to add two columns which are of data type nvarchar. It is giving me only concatenation. Please have a look on query that is written below:

SELECT 
    T0.[ItmsGrpCod],
    T1.[Dscription],
    T3.[OnHand] as 'Opening balance',
    (T1.[U_TotalGraded]) + (T1.[U_TotalRej]),
    T1.[U_TotalGraded], T1.[U_TotalRej], 
    T2.[DocDate] 
FROM 
    OITM T0
INNER JOIN 
    IGN1 T1 ON T0.[ItemCode] = T1.[ItemCode] 
INNER JOIN 
    OIGN T2 ON T1.[DocEntry] = T2.[DocEntry] 
INNER JOIN 
    OITW T3 ON T0.[ItemCode] = T3.[ItemCode]

Now if T1.[U_TotalGraded] = 12 and T1.[U_TotalRej] = 3 then I am getting (T1.[U_TotalGraded])+(T1.[U_TotalRej]) = 123 instead of adding.

Upvotes: 0

Views: 82

Answers (1)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can convert it to INT using CAST in following:

   SELECT 
       T0.[ItmsGrpCod], 
       T1.[Dscription], 
       T3.[OnHand] as 'Opening balance',
       case when isnumeric(T1.[U_TotalGraded]) = 1 and 
                 isnumeric(T1.[U_TotalRej]) = 1 
            then cast(T1.[U_TotalGraded] as int) + cast(T1.[U_TotalRej] as int) 
            else 0 -- do something if can't be converted
       end,
       T1.[U_TotalGraded], 
       T1.[U_TotalRej], 
       T2.[DocDate] 
   FROM OITM T0  
   INNER JOIN IGN1 T1 ON T0.[ItemCode] = T1.[ItemCode] 
   INNER JOIN OIGN T2 ON T1.[DocEntry] = T2.[DocEntry] 
   INNER JOIN OITW T3 ON T0.[ItemCode] = T3.[ItemCode]

Also you can use ISNUMERIC to check If It can be converted to datatype INT.

Upvotes: 3

Related Questions