sql server - QUOTENAME is causing conversion errors in SQL? -
edit:
ok throwing me off now, have query working twice , no reason stops working again , give me different errors each time. getting error:
msg 8115, level 16, state 8, procedure gsp_rpt_custom_extract_haven_test_2, line 12 arithmetic overflow error converting varchar data type numeric. it inconsistent, can take out joins , query runs join irrelevant error , work absolutly fine independantly. confused one.
end edit
i have query, lengthy one. using query bcp command why looks odd. trying add text qualifier (as bcp cannot it) using quotename(), query runs fine without quote name reason keep getting error:
msg 245, level 16, state 1, procedure gsp_rpt_custom_extract_test, line 12 conversion failed when converting varchar value '05-01-2015 12:49:05' data type smallint. as can see query have commented out columns have not got yet part of diagnosis. columns work ok until last 1 (the case statement) whats causing problem. said above, querty runs fine when remove quotenames.
any appreciated.
thanks
full code:
with quest_cte ( select companyid company_id, q.questionid, questiontext, cast(q.answer varchar) answer questionnaire q join callcentre_web.dbo.questionnaire_questions qq on q.questionid = qq.questionid join dbo.profilemapping pm on pm.profilemappingid = q.profilemappingid join campaignmapping cm on pm.mapid = cm.mapid questiontypeid = 0 , qq.goldenfield = 1 , campaignid = 18 union select companyid company_id, q.questionid, questiontext, cast(qa.answer varchar) questionnaire q join callcentre_web.dbo.questionnaire_questions qq on q.questionid = qq.questionid join callcentre_web.dbo.questionnaire_answers qa on q.answer = qa.answerid join dbo.profilemapping pm on pm.profilemappingid = q.profilemappingid join campaignmapping cm on pm.mapid = cm.mapid questiontypeid = 1 , qq.goldenfield = 1 , campaignid = 18 union select companyid company_id, q.questionid, questiontext, cast(stuff((select ',' + e.answer questionnaire cross apply dbo.splitstring(a.answer,',') b join callcentre_web.dbo.questionnaire_answers e on b.s = e.answerid isnumeric(b.s) = 1 , questiontypeid = 2 , a.questionnaireid = q.questionnaireid xml path('')),1,1,'') varchar) answer questionnaire q join dbo.profilemapping pm on pm.profilemappingid = q.profilemappingid join campaignmapping cm on pm.mapid = cm.mapid join callcentre_web.dbo.questionnaire_questions qq on q.questionid = qq.questionid questiontypeid = 2 , qq.goldenfield = 1 , campaignid = 18 ) select cast(quotename('gs id',char(34))as varchar(255)), cast(quotename('companyname', char(34))as varchar(255)), cast(quotename('address', char(34))as varchar(255)), cast(quotename('country', char(34))as varchar(255)), cast(quotename('sicdescription', char(34))as varchar(255)), cast(quotename('sector', char(34))as varchar(255)), cast(quotename('employees', char(34))as varchar(255)), cast(quotename('website', char(34))as varchar(255)), cast(quotename('companyturnover', char(34))as varchar(255)), cast(quotename('phone', char(34))as varchar(255)), cast(quotename('mobilephone', char(34))as varchar(255)), cast(quotename('fax', char(34))as varchar(255)), cast(quotename('salutation', char(34))as varchar(255)), cast(quotename('forename', char(34))as varchar(255)), cast(quotename('surname', char(34))as varchar(255)), cast(quotename('jobtitle', char(34))as varchar(255)), cast(quotename('emailaddress', char(34))as varchar(255)), cast(quotename('leadsource', char(34))as varchar(255)) --cast(quotename('callresult', char(34))as varchar(255)), --cast(quotename('what company registration number?', char(34))as varchar(255)) --cast(quotename('who electricity supplier?', char(34))as varchar(255)), --cast(quotename('number of sites', char(34))as varchar(255)), --cast(quotename('mpan number', char(34))as varchar(255)), --cast(quotename('do use half hourly meters?', char(34))as varchar(255)), --cast(quotename('what average use electricity? (in gwh)', char(34))as varchar(255)), --cast(quotename('do use fixed or flexible contract electricity? (if on 20gwh)', char(34))as varchar(255)), --cast(quotename('what average spend on electricity?', char(34))as varchar(255)), --cast(quotename('do have involvement tpi?', char(34))as varchar(255)), --cast(quotename('if so, tpi supplier?',char(34))as varchar(255)), --cast(quotename('electricity contract expiry date',char(34)) varchar(255)) union select quotename(cast(c.companyid varchar(255)),char(34)), quotename(companyname, char(34)), quotename(isnull(addressline1,'') + case when addressline1 null '' else ', ' end + isnull(addressline2,'') + case when addressline2 null '' else ', ' end + isnull(addressline3,'') + case when addressline3 null '' else ', ' end + isnull(town,'') + case when town null '' else ', ' end + isnull(county,'') + case when county null '' else ', ' end + isnull(region,'') + case when region null '' else ', ' end + postcode, char(34)), quotename('uk', char(34)), quotename(sic.sicdescription, char(34)), quotename(sec.sector, char(34)), cast(quotename(isnull(numberofemployees,''),char(34)) varchar(255)), cast(quotename(isnull(website,''),char(34)) varchar(255)), cast(quotename(isnull(companyturnover,''),char(34)) varchar(255)), quotename(isnull(maintel.phonenumber,''),char(34)) , quotename(isnull(mobtel.phonenumber,''),char(34)), quotename(isnull(faxtel.phonenumber,''), char(34)), quotename(isnull(salutation,''), char(34)), quotename(isnull(forename,''), char(34)), quotename(isnull(surname,''), char(34)), quotename(isnull(jobtitle,''),char(34)), quotename(isnull(emailaddress,''),char(34)), case when [what average use electricity? (in gwh)] >= 1.5 or [what average spend on electricity?] >= 150000 quotename('gs1',char(34)) when [what average use electricity? (in gwh)] < 1.5 or [what average spend on electricity?] < 150000 quotename('gs1 sme',char(34)) else quotename('gs1',char(34)) end --quotename(callresult,char(34)) --quotename(isnull([what company registration number?],''),char(34)) --case when isnull([who electricity supplier?],'') = 'other' isnull([if other please state (electric)],'') --else isnull([who electricity supplier?],'') end, --quotename(isnull([number of sites],''),char(34)) --isnull([mpan number],''), --isnull([do use half hourly meters?],'') , --isnull([what average use electricity? (in gwh)],''), --isnull([do use fixed or flexible contract electricity? (if on 20gwh)],''), --isnull([what average spend on electricity?],''), --isnull([do have involvement tpi?],''), --isnull([if so, tpi supplier?],''), --cast(isnull(replace(convert(varchar(10),[electricity contract expiry date],103),'-','/'),'') varchar(20)) company c join callcentre_web.dbo.lk_sic sic on sic.sicid = c.sicid , sic.sictype = c.sictype join callcentre_web.dbo.lk_sector sec on sec.sectorid = sic.sectorid , sic.sictype = sec.sectortype left join ( select * ( select row_number() on (partition companyid order c.companyid) row, companyid, surname , forename , s.salutation, emailaddress, jobtitle contacts c join callcentre_web.dbo.lk_salutation s on c.salutation = s.salutationid join contactdetails cd on cd.contactid = c.contactid , campaignid = 18 responsibilityid = 3 /* primary contact */ ) x row = 1 ) con on con.companyid = c.companyid left join ( select * ( select row_number() on (partition cn.companyid order cn.companyid) row, cn.companyid, phonenumber contactnumbers cn join company c on c.companyid = cn.companyid join campaignmapping cm on cm.companyid = c.companyid phonetypeid not in (3,4,5)--mobile, tps, fax , campaignid = 18 ) x row = 1 ) maintel on maintel.companyid = c.companyid left join ( select * ( select row_number() on (partition cn.companyid order cn.companyid) row, cn.companyid, phonenumber contactnumbers cn join company c on c.companyid = cn.companyid join campaignmapping cm on cm.companyid = c.companyid phonetypeid = 3--mobile , phonenumber '07%' , campaignid = 18 ) x row = 1 ) mobtel on mobtel.companyid = c.companyid left join ( select * ( select row_number() on (partition cn.companyid order cn.companyid) row, cn.companyid, phonenumber contactnumbers cn join company c on c.companyid = cn.companyid join campaignmapping cm on cm.companyid = c.companyid phonetypeid = 5--fax , campaignid = 18 ) x row = 1 ) faxtel on faxtel.companyid = c.companyid join campaignmapping cm on cm.companyid = c.companyid , campaignid = 18 join calldetails cd on cd.mapid = cm.mapid left join callcentre_web..lk_callresults lkc on lkc.callresultid = cd.callresultid left join ( select * ( select company_id, questiontext, answer quest_cte ) source pivot ( max(answer) questiontext in ([what company registration number?], [who electricity supplier?],[number of sites],[if other please state (electric)], [mpan number],[do use half hourly meters?], [do use fixed or flexible contract gas? (if on 20gwh)], [what average use electricity? (in gwh)], [do use fixed or flexible contract electricity? (if on 20gwh)], [what average spend on gas?],[what average spend on electricity?], [do have involvement tpi?],[if so, tpi supplier?], [gas contract expiry date],[electricity contract expiry date]) ) pvt ) pv on pv.company_id = c.companyid
Comments
Post a Comment