SQL error-Conversion failed when converting the varchar value 'hireman' to data type int

Transact-SQL
1

Hi,

i'm using below query to fetch the HPQC defect with history while applying the project filter as RELEASE_CYCLES.RCYC_NAME in ('PrjectName1', 'ProjectName2')

however on running the query, i'm getting error message that Conversion failed when converting the varchar value 'hireman' to data type int.

Note:- i'm able to fetch the defect history successfully if i do no apply project filters.

SELECT AUDIT_LOG.AU_ENTITY_ID as "Defect ID",BUG.BG_RESPONSIBLE as "Assigned To", BUG.BG_USER_09 as "Developer", AUDIT_LOG.AU_TIME as "Date", AUDIT_PROPERTIES.AP_OLD_VALUE AS "Previous Status", AUDIT_PROPERTIES.AP_NEW_VALUE as "New Status", BUG.BG_USER_02 as "Company", BUG.BG_STATUS as "Final_Status", BUG.BG_USER_11 as "Root Cause", RELEASE_CYCLES.RCYC_NAME as "Release", BUG.BG_USER_08 as "Test Phase"

FROM AUDIT_LOG, AUDIT_PROPERTIES, BUG, RELEASE_CYCLES

WHERE AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS'
AND AUDIT_LOG.AU_ENTITY_ID = BUG.BG_BUG_ID

AND BUG.BG_Detected_IN_RCYC = RELEASE_CYCLES.RCYC_ID
AND RELEASE_CYCLES.RCYC_NAME in ('PrjectName1', 'ProjectName2')

Any help would be highly appreciated! thanks

2

hi this is a datatype mismatch issue ..

some where in your columns .
.. data is varchar ... which is being converted to int

it could be here in these joins
AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS'
AND AUDIT_LOG.AU_ENTITY_ID = BUG.BG_BUG_ID
AND BUG.BG_Detected_IN_RCYC = RELEASE_CYCLES.RCYC_ID

remove one join at a time and see if the error disappears
then which join if you comment the error disappears
look at the join columns datatypes and data ...

hope this helps :slight_smile: :slight_smile:
if i can have access to your database I can fix this !!!

1 Like