October 10, 2013 at 6:05 am
I have a error message in my query
Msg 245, Level 16, State 1, Line 72
Conversion failed when converting the nvarchar value 'H-00001' to data type int.
After using this,
CAST(columnname as NVARCHAR(1000))
the error message persists.
Please help.
October 10, 2013 at 6:12 am
Could you send the complete query ? Are you updating or inserting data in a table ?
October 10, 2013 at 6:21 am
This is the query part:
; with BaseProjects (
materialItemCode,
hazardCode)
as (
select
Mi.materialItemCode,
CAST(H.hazardCode as NVARCHAR(1000)) as hazardCode
from
local_MaterialsItems MI
LEFT JOIN local_MaterialsItemsHazards MH
ON (MH.materialItemIncId = MI.materialItemIncId AND MH.materialItemHazardSqlId = MI.materialItemSqlId AND MH.isDeleted=0x0)
LEFT JOIN local_Hazards H
ON (H.hazardIncId = MH.hazardIncId AND H.hazardSqlId = MH.hazardSqlId AND H.isDeleted=0x0)
October 10, 2013 at 6:32 am
Junglee_George (10/10/2013)
fromlocal_MaterialsItems MI
LEFT JOIN local_MaterialsItemsHazards MH
ON (MH.materialItemIncId = MI.materialItemIncId AND MH.materialItemHazardSqlId = MI.materialItemSqlId AND MH.isDeleted=0x0)
LEFT JOIN local_Hazards H
ON (H.hazardIncId = MH.hazardIncId AND H.hazardSqlId = MH.hazardSqlId AND H.isDeleted=0x0)
Check the datatypes of columns in join. Seems there is any column which has different datatype in two tables yet are joined (nvarchar in one and int in second).
October 10, 2013 at 6:35 am
Junglee_George (10/10/2013)
This is the query part:; with BaseProjects (
materialItemCode,
hazardCode)
as (
select
Mi.materialItemCode,
CAST(H.hazardCode as NVARCHAR(1000)) as hazardCode
from
local_MaterialsItems MI
LEFT JOIN local_MaterialsItemsHazards MH
ON (MH.materialItemIncId = MI.materialItemIncId AND MH.materialItemHazardSqlId = MI.materialItemSqlId AND MH.isDeleted=0x0)
LEFT JOIN local_Hazards H
ON (H.hazardIncId = MH.hazardIncId AND H.hazardSqlId = MH.hazardSqlId AND H.isDeleted=0x0)
; -- CTEs don't start with statement terminators.
-- Previous statements to a CTE must be terminated with a statement terminator.
WITH BaseProjects (materialItemCode, hazardCode) AS (
SELECT Mi.materialItemCode, CAST(H.hazardCode AS NVARCHAR(1000)) AS hazardCode
FROM local_MaterialsItems MI
LEFT JOIN local_MaterialsItemsHazards MH ON (MH.materialItemIncId = MI.materialItemIncId AND -- What is the data type of these columns?
MH.materialItemHazardSqlId = MI.materialItemSqlId AND -- What is the data type of these columns?
MH.isDeleted = 0x0 -- What is the data type of this columns?
)
LEFT JOIN local_Hazards H ON (H.hazardIncId = MH.hazardIncId AND -- What is the data type of these columns?
H.hazardSqlId = MH.hazardSqlId AND -- What is the data type of these columns?
H.isDeleted = 0x0 -- What is the data type of this columns?
)
/* What is here ?? */
October 10, 2013 at 8:14 am
Hi,
I am posting my query here. Please help me to find a solution. Thanks.
SELECT *
INTO #Activities
FROM (
select
MIC.materialItemContainerCode,
Mi.materialItemCode,
MIC.receptionDate,
OP.operatorName as receivedBy,
MIC.supplier,
MIC.grossAmount,
MIC.netAmount,
UG.unitName as grossunit,
UN.unitName as netunit,
S.siteName,
MIC.otherReference,
-----------
MI.materialItemName,
MIC.batchNumber,
TM.typeOfMaterialItemName,
MIC.expiryDate,
-----------
---MaterialsItems.usedInGlpStudies,
MI.color,
AP.appearanceName,
-----------
MI.isSafetyDataSheetAttached,
H.signalWord,
NULL AS hazardCode,
MI.otherHazard,
NULL AS precautionaryStatementCode,
MT.storageTemperatureName,
MI.storageCondition,
MI.isCertificateOfAnalysisAttached,
MI.certificateOfAnalysisDate
FROM
local_MaterialsItemsContainers MIC
INNER JOIN local_MaterialsItems MI
ON (MIC.materialItemIncId = MI.materialItemIncId AND MIC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)
INNER JOIN local_TypesOfMaterialsItems TM
ON (TM.typeOfMaterialItemIncId = MI.typeOfMaterialItemIncId AND TM.typeOfMaterialItemSqlId = MI.typeOfMaterialItemSqlId AND TM.isDeleted=0x0)
LEFT JOIN Operators AS OP
ON (OP.operatorSqlId=MIC.receivedBySqlId AND OP.operatorIncId=MIC.receivedByIncId AND OP.isDeleted=0x0)
LEFT JOIN Units AS UG
ON (UG.unitSqlId=MIC.grossAmountUnitSqlId AND UG.unitIncId=MIC.grossAmountUnitIncId AND UG.isDeleted=0x0)
LEFT JOIN Units AS UN
ON (UN.unitSqlId=MIC.netAmountUnitSqlId AND UN.unitIncId=MIC.netAmountUnitIncId AND UN.isDeleted=0x0)
LEFT JOIN Sites AS S
ON (S.siteSqlId=MIC.siteSqlId AND S.siteIncId=MIC.siteIncId AND S.isDeleted=0x0)
LEFT JOIN local_Appearances AS AP
ON (AP.appearanceSqlId=MI.appearanceSqlId AND AP.appearanceIncId=MI.appearanceIncId AND AP.isDeleted=0x0)
LEFT JOIN local_MaterialsItemsHazards MH
ON (MH.materialItemIncId = MI.materialItemIncId AND MH.materialItemHazardSqlId = MI.materialItemSqlId AND MH.isDeleted=0x0)
LEFT JOIN local_Hazards H
ON (H.hazardIncId = MH.hazardIncId AND H.hazardSqlId = MH.hazardSqlId AND H.isDeleted=0x0)
LEFT JOIN local_MaterialsItemsPrecautionaryStatements MP
ON (MP.materialItemIncId = MI.materialItemIncId AND MP.materialItemSqlId = MI.materialItemSqlId AND MP.isDeleted=0x0)
LEFT JOIN local_PrecautionaryStatements P
ON (P.precautionaryStatementIncId = MP.precautionaryStatementIncId AND P.precautionaryStatementSqlId = MP.precautionaryStatementSqlId AND P.isDeleted=0x0)
LEFT JOIN local_StoragesTemperatures MT
ON (MT.storageTemperatureIncId = MI.storageTemperatureIncId AND MT.storageTemperatureSqlId = MI.storageTemperatureSqlId AND MT.isDeleted=0x0)
WHERE MIC.isDeleted=0x0
) AS SourceTable
; with BaseProjects (
materialItemCode,
hazardCode
) as (
select
distinct Mi.materialItemCode,
H.hazardCode as hazardCode
from
local_MaterialsItems MI
LEFT JOIN local_MaterialsItemsHazards MH
ON (MH.materialItemIncId = MI.materialItemIncId -----int datatype
AND MH.materialItemHazardSqlId = MI.materialItemSqlId --- smallint datatype
AND MH.isDeleted=0x0)--- bit datatype
LEFT JOIN local_Hazards H
ON (H.hazardIncId = MH.hazardIncId -----int datatype
AND H.hazardSqlId = MH.hazardSqlId --- smallint datatype
AND H.isDeleted=0x0) --- bit datatype
WHERE MI.isDeleted=0x0 --- bit datatype
)
update #Activities set
hazardCode = bp.hazardCode
from
BaseProjects bp
inner join #Activities k
on (bp.materialItemCode = k.materialItemCode)
SELECT * FROM #Activities
October 11, 2013 at 12:44 am
I have made a break through. I found that the setting hazard code to NULL in the first select statement, is the where the problem occurs.
i.e. NULL AS hazardCode,
The error message is
Conversion failed when converting the nvarchar value 'H-00001' to data type int.
Is there any way to rewrite my query to pass the NULL to hazardcode?
The datatype of hazardCode is nvarchar(100).
October 13, 2013 at 9:47 pm
CONVERT(nvarchar(100), NULL ) AS hazardCode
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply