CODEWITHSUNDEEP
sql-server
user1030181
user1030181

Reputation: 2015

Error converting data type varchar to numeric for this below query

Error converting data type varchar to numeric for this below query

Please suggest what can i modify to fix

    Update BUDTANKS
 set    BUDTANKS.SHELLCAPACITY = 0.0000000 --Numeric Column
       ,BUDTANKS.WORKINGCAPACITY =  0.0000000 --Numeric Column
       ,BUDTANKS.LEASEDCAPACITY = 0.0000000 --Numeric Column
       ,BUDTANKS.VERSIONID = 1
       ,BUDTANKS.STATUSID = 1
       ,BUDTANKS.IMPORTEDRECORD = 1
       ,BUDTANKS.LastModifiedUserID = 'Import'
       ,BUDTANKS.LASTMODIFIEDDATETIME = GETDATE()
FROM BUDTANKS BTANK WITH(NOLOCK)
INNER JOIN BUDTERMINALS BT WITH(NOLOCK) ON BT.TERMINALID = BTANK.TERMINALID 
INNER JOIN External_Liq_Itm_Tanks ELIT WITH(NOLOCK) ON ELIT.TerminalName = BT.MBFTERMINALNAME AND ELIT.TankName = BTANK.TANKNAME
Where   (ISNULL(BTANK.SHELLCAPACITY, '') <> ISNULL(0.0000000, '')
     OR ISNULL(BTANK.WORKINGCAPACITY, '') <> ISNULL(0.0000000, '')
     OR ISNULL(BTANK.LEASEDCAPACITY, '') <> ISNULL(0.0000000, '')
     OR (BTANK.STATUSID <> 1)
     OR (BTANK.IMPORTEDRECORD <> 1))

Upvotes: 0

Views: 161

Answers (1)

A_Sk
A_Sk

Reputation: 4630

I think your problem with this query is in the Where condition:

Where   (ISNULL(BTANK.SHELLCAPACITY, '') <> ISNULL(0.0000000, '')
     OR ISNULL(BTANK.WORKINGCAPACITY, '') <> ISNULL(0.0000000, '')
     OR ISNULL(BTANK.LEASEDCAPACITY, '') <> ISNULL(0.0000000, '')
     OR (BTANK.STATUSID <> 1)
     OR (BTANK.IMPORTEDRECORD <> 1))

You need to change it to:

Where   (ISNULL(BTANK.SHELLCAPACITY, 0) <> 0)
     OR ISNULL(BTANK.WORKINGCAPACITY,0) <> 0)
     OR ISNULL(BTANK.LEASEDCAPACITY, 0) <> 0)
     OR (BTANK.STATUSID <> 1)
     OR (BTANK.IMPORTEDRECORD <> 1))

or

 Where  BTANK.SHELLCAPACITY Is Not Null
         OR BTANK.WORKINGCAPACITY Is Not Null
         OR BTANK.LEASEDCAPACITY Is Not Null
         OR (BTANK.STATUSID <> 1)
         OR (BTANK.IMPORTEDRECORD <> 1))

Upvotes: 1

Related Questions