August 23, 2016 at 1:51 pm
I have the below script and when i run it i keep getting the error "Conversion failed when converting the varchar value '?' to data type int" and i am unable to determine which line is causing the error. Any thoughts?
SELECT
rm_office = replace(replace(replace(replace(rmoff.office_rpt,'reporting office',''),'reporting off',''),'Combined',''),'Office',''),
region = org.region_code,
client = CASE when eng.doc_user_status_code BETWEEN 31 and 34 then 'confidential' else cus.customer_name end,
ultimate_parent = CASE WHEN eng.doc_user_status_code BETWEEN 31 and 34 then 'Confidential' ELSE ult1.customer_name end,
position = CASE when eng.doc_user_status_code BETWEEN 31 and 34 then 'confidential' ELSE eng.position end,
position_country = cus.country_code,
position_state = cus.region_code,
position_city = cus.city,
final_salary = CASE when eng.placement_name IS NULL THEN '' else eng.final_salary end,
final_bonus = CASE when eng.placement_name IS NULL then '' else eng.final_bonus end,
salary_curr_code = CASE when eng.placement_name IS NULL THEN '' ELSE eng.salary_curr_code end,
engagement = eng.sales_doc_no,
level_code = eng.level_code,
eng.accnt_open_date,
eng.accnt_close_date,
eng.close_code,
cls.description,
eng.placement_name,
work_type = ord.order_reason,
center_expertise_code = cxp.center_expertise_code,
center_expertise_description = cxp.center_expertise,
market = eng.market_code,
market_sector = mse.market_sector,
sector = sec.sector_code,
sector_description = sec.sector,
rm_emp_id = rm.emp_id,
rm = rm.last_name + ', ' + rm.first_name,
c1 = c1.last_name + ', ' + c1.first_name,
c2 = c2.last_name + ', ' + c2.first_name,
ea = ea.last_name + ', ' + ea.first_name,
em = em.last_name + ', ' + em.first_name,
doc_type = edt.doc_type,
fee_est = esm.fee_estimate,
admin_fee = esm.float_estimate,
admin_pct = CASE when esm.fee_estimate = 0 then 0 ELSE (esm.float_estimate / esm.fee_estimate) end,
fees_billed = bil.[Fees Builled],
admin_billed = bil.[Admin Billed],
final_fee = esm.final_fee,
digital_expertise_flag = coalesce(eng.digital_expertise_flag,''),
private_equity_flag = coalesce(eng.private_equity_flag,''),
private_equity_1 = pe1.pe_desc,
private_equity_name_1 = pe1.pe_name,
private_equity_2 = pe2.pe_desc,
private_equity_name_2 = pe2.pe_name,
private_equity_3 = pe3.pe_desc,
private_equity_name_3 = pe3.pe_name,
conv_opp = case WHEN eng.ref_proposal_no is NULL then 'N' ELSE 'Y' end,
opp_created_on = prl.created_on_date,
gor.map_order_reason_code,
rm.region_code,
rm.sub_region_code,
rm.office_rpt_code
from dbo.spm_customer cus
join dbo.spm_engagement eng ON eng.customer_no_sold_to = cus.customer_no
LEFT outer JOIN dbo.spm_sales_org org ON org.sales_org = eng.sales_org
left outer JOIN dbo.spm_proposal prl on prl.sales_doc_no = eng.ref_proposal_no
LEFT OUTER JOIN dbo.spm_eng_summary esm on esm.sales_doc_no = eng.sales_doc_no and esm.currency_code = 'USD'
LEFT outer JOIN dbo.spm_eng_doc_type edt on edt.doc_type_code = eng.doc_type_code
left outer JOIN dbo.spm_eng_center_expertise cxp ON cxp.center_expertise_code = eng.center_expertise_code
left outer join dbo.goc_map_eng_order_reason gor ON gor.order_reason_code = eng.order_reason_code
LEFT outer JOIN dbo.spm_eng_order_reason ord on ord.order_reason_code = eng.order_reason_code
LEFT outer JOIN dbo.spm_eng_credit ecr1 ON ecr1.sales_doc_no = eng.sales_doc_no and ecr1.role_code = 'C1'
LEFT OUTER JOIN dbo.spm_employee c1 ON c1.emp_id = ecr1.emp_id
LEFT outer JOIN dbo.spm_eng_credit ecr2 ON ecr2.sales_doc_no = eng.sales_doc_no AND ecr2.role_code = 'C2'
left outer JOIN dbo.spm_employee c2 ON c2.emp_id = ecr2.emp_id
left outer JOIN dbo.spm_eng_credit ecr3 on ecr3.sales_doc_no = eng.sales_doc_no AND ecr3.role_code = 'EA'
left outer JOIN dbo.spm_employee ea ON ea.emp_id = ecr3.emp_id
left outer join dbo.spm_eng_credit ecr4 ON ecr4.sales_doc_no = eng.sales_doc_no AND ecr4.role_code = 'EM'
left outer join dbo.spm_employee em on em.emp_id = ecr4.emp_id
JOIN dbo.spm_employee rm ON rm.emp_id = eng.rev_manager_emp_id
left outer JOIN dbo.spm_eng_close cls ON cls.close_code = eng.close_code
left outer JOIN dbo.spm_office_rpt rmoff ON rmoff.office_rpt_code = rm.office_rpt_code
LEFT outer JOIN dbo.spm_market_sector mse ON mse.market_sector_code = eng.market_sector_code
left outer JOIN dbo.spm_sector sec on sec.sector_code = eng.sector_code
LEFT outer JOIN dbo.gsc_ult_parent ultp ON right('000000000' + cast(ultp.customer_olp_id as varchar(20)) ,10) = eng.customer_no_sold_to
LEFT outer JOIN dbo.spm_customer ult1 ON right('000000000' + cast(ult1.customer_no AS varchar(20)), 10) = right('000000000' + cast(ultp.customer_no_parent_olp_id as varchar(20)) ,10)
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZD')
pe1 on pe1.sales_doc_no = eng.sales_doc_no
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZE')
pe2 on pe2.sales_doc_no = eng.sales_doc_no
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZF')
pe3 on pe3.sales_doc_no = eng.sales_doc_no
left outer JOIN (SELECT inv.sales_doc_no
,[Fees Builled] = sum(fee_amount)
,[Admin Billed] = sum(float_amount)
FROM dbo.spm_engagement eng
JOIN dbo.spm_eng_invoice inv ON inv.sales_doc_no = eng.sales_doc_no
join dbo.spm_eng_billed bil ON bil.invoice_no = inv.invoice_no AND bil.currency_code = 'USD'
group by inv.sales_doc_no) bil on bil.sales_doc_no = eng.sales_doc_no
WHERE eng.rejection_status <> ' C'
ORDER by rm_office,engagement
August 23, 2016 at 1:58 pm
This construction is probably causing issues:
final_salary = CASE when eng.placement_name IS NULL THEN '' else eng.final_salary end,
because '' is not numeric.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 23, 2016 at 2:05 pm
tstagliano (8/23/2016)
I have the below script and when i run it i keep getting the error "Conversion failed when converting the varchar value '?' to data type int" and i am unable to determine which line is causing the error. Any thoughts?
SELECT
rm_office = replace(replace(replace(replace(rmoff.office_rpt,'reporting office',''),'reporting off',''),'Combined',''),'Office',''),
region = org.region_code,
client = CASE when eng.doc_user_status_code BETWEEN 31 and 34 then 'confidential' else cus.customer_name end,
ultimate_parent = CASE WHEN eng.doc_user_status_code BETWEEN 31 and 34 then 'Confidential' ELSE ult1.customer_name end,
position = CASE when eng.doc_user_status_code BETWEEN 31 and 34 then 'confidential' ELSE eng.position end,
position_country = cus.country_code,
position_state = cus.region_code,
position_city = cus.city,
final_salary = CASE when eng.placement_name IS NULL THEN '' else eng.final_salary end,
final_bonus = CASE when eng.placement_name IS NULL then '' else eng.final_bonus end,
salary_curr_code = CASE when eng.placement_name IS NULL THEN '' ELSE eng.salary_curr_code end,
engagement = eng.sales_doc_no,
level_code = eng.level_code,
eng.accnt_open_date,
eng.accnt_close_date,
eng.close_code,
cls.description,
eng.placement_name,
work_type = ord.order_reason,
center_expertise_code = cxp.center_expertise_code,
center_expertise_description = cxp.center_expertise,
market = eng.market_code,
market_sector = mse.market_sector,
sector = sec.sector_code,
sector_description = sec.sector,
rm_emp_id = rm.emp_id,
rm = rm.last_name + ', ' + rm.first_name,
c1 = c1.last_name + ', ' + c1.first_name,
c2 = c2.last_name + ', ' + c2.first_name,
ea = ea.last_name + ', ' + ea.first_name,
em = em.last_name + ', ' + em.first_name,
doc_type = edt.doc_type,
fee_est = esm.fee_estimate,
admin_fee = esm.float_estimate,
admin_pct = CASE when esm.fee_estimate = 0 then 0 ELSE (esm.float_estimate / esm.fee_estimate) end,
fees_billed = bil.[Fees Builled],
admin_billed = bil.[Admin Billed],
final_fee = esm.final_fee,
digital_expertise_flag = coalesce(eng.digital_expertise_flag,''),
private_equity_flag = coalesce(eng.private_equity_flag,''),
private_equity_1 = pe1.pe_desc,
private_equity_name_1 = pe1.pe_name,
private_equity_2 = pe2.pe_desc,
private_equity_name_2 = pe2.pe_name,
private_equity_3 = pe3.pe_desc,
private_equity_name_3 = pe3.pe_name,
conv_opp = case WHEN eng.ref_proposal_no is NULL then 'N' ELSE 'Y' end,
opp_created_on = prl.created_on_date,
gor.map_order_reason_code,
rm.region_code,
rm.sub_region_code,
rm.office_rpt_code
from dbo.spm_customer cus
join dbo.spm_engagement eng ON eng.customer_no_sold_to = cus.customer_no
LEFT outer JOIN dbo.spm_sales_org org ON org.sales_org = eng.sales_org
left outer JOIN dbo.spm_proposal prl on prl.sales_doc_no = eng.ref_proposal_no
LEFT OUTER JOIN dbo.spm_eng_summary esm on esm.sales_doc_no = eng.sales_doc_no and esm.currency_code = 'USD'
LEFT outer JOIN dbo.spm_eng_doc_type edt on edt.doc_type_code = eng.doc_type_code
left outer JOIN dbo.spm_eng_center_expertise cxp ON cxp.center_expertise_code = eng.center_expertise_code
left outer join dbo.goc_map_eng_order_reason gor ON gor.order_reason_code = eng.order_reason_code
LEFT outer JOIN dbo.spm_eng_order_reason ord on ord.order_reason_code = eng.order_reason_code
LEFT outer JOIN dbo.spm_eng_credit ecr1 ON ecr1.sales_doc_no = eng.sales_doc_no and ecr1.role_code = 'C1'
LEFT OUTER JOIN dbo.spm_employee c1 ON c1.emp_id = ecr1.emp_id
LEFT outer JOIN dbo.spm_eng_credit ecr2 ON ecr2.sales_doc_no = eng.sales_doc_no AND ecr2.role_code = 'C2'
left outer JOIN dbo.spm_employee c2 ON c2.emp_id = ecr2.emp_id
left outer JOIN dbo.spm_eng_credit ecr3 on ecr3.sales_doc_no = eng.sales_doc_no AND ecr3.role_code = 'EA'
left outer JOIN dbo.spm_employee ea ON ea.emp_id = ecr3.emp_id
left outer join dbo.spm_eng_credit ecr4 ON ecr4.sales_doc_no = eng.sales_doc_no AND ecr4.role_code = 'EM'
left outer join dbo.spm_employee em on em.emp_id = ecr4.emp_id
JOIN dbo.spm_employee rm ON rm.emp_id = eng.rev_manager_emp_id
left outer JOIN dbo.spm_eng_close cls ON cls.close_code = eng.close_code
left outer JOIN dbo.spm_office_rpt rmoff ON rmoff.office_rpt_code = rm.office_rpt_code
LEFT outer JOIN dbo.spm_market_sector mse ON mse.market_sector_code = eng.market_sector_code
left outer JOIN dbo.spm_sector sec on sec.sector_code = eng.sector_code
LEFT outer JOIN dbo.gsc_ult_parent ultp ON right('000000000' + cast(ultp.customer_olp_id as varchar(20)) ,10) = eng.customer_no_sold_to
LEFT outer JOIN dbo.spm_customer ult1 ON right('000000000' + cast(ult1.customer_no AS varchar(20)), 10) = right('000000000' + cast(ultp.customer_no_parent_olp_id as varchar(20)) ,10)
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZD')
pe1 on pe1.sales_doc_no = eng.sales_doc_no
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZE')
pe2 on pe2.sales_doc_no = eng.sales_doc_no
LEFT outer JOIN (SELECT sales_doc_no, pe_desc, pe_name from dbo.bobj_bac_private_equity where pe_code = 'ZF')
pe3 on pe3.sales_doc_no = eng.sales_doc_no
left outer JOIN (SELECT inv.sales_doc_no
,[Fees Builled] = sum(fee_amount)
,[Admin Billed] = sum(float_amount)
FROM dbo.spm_engagement eng
JOIN dbo.spm_eng_invoice inv ON inv.sales_doc_no = eng.sales_doc_no
join dbo.spm_eng_billed bil ON bil.invoice_no = inv.invoice_no AND bil.currency_code = 'USD'
group by inv.sales_doc_no) bil on bil.sales_doc_no = eng.sales_doc_no
WHERE eng.rejection_status <> ' C'
ORDER by rm_office,engagement
My first suspect is eng.doc_user_status_code. But you should check for any places where you have comparisons or operations involving integers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply