SQLTeam.com | Weblogs | Forums

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

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

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