sql - Conversion failing when changing join constraint from list of varchars to subquery -


i'm trying update procedure run switching out list of static varchars within in statement (which in join condition) use subquery instead. however, i'm getting error saying conversion failed when converting varchar value 'normal' data type int.

i know means somewhere in query i'm trying convert value 'normal' int, in query there's not place should int - it's varchar or datetime, , subquery returns same values former static list of varchars. furthermore, there's not place anywhere in dataset value "normal", confuses me.

has else dealt before?

edit:
here's code (reduced show pieces causing error): 

select * #temptable     left join dbo.v_lookup_assumptions assumptions on      case when group3 in      --('value1', 'value2', 'value3')      (select lookupdetail_name  lookupdetail)     group3 else group2 end =assumptions.sector2     , asofdate between assumptions.startdate , assumptions.enddate     , shortname = assumptions.assumptionshortname

i can comment out subquery , uncomment list of values , works. also, if don't run "shortname" constraint when i'm using subquery, works (though doesn't give expected results, might expect). join constraints either varchar or date.

in addition, added 'normal' list of static values , ran expected. updated subquery select 'value1' lookupdetail , failed.

edit2:
, makes no sense, if limit original #temptable 2080 records, new subquery syntax works. however, if let go 2081 records fails - regardless of dataset. in other words, change dates around different data each time through , limit #temptable 2080 records, new syntax works fine. change 2081 records , fails. if run through 2081 record dataset , filter clause, can every row in 2081 record display long it's not 2081 @ same time.

it turns out execution plan changing when total number of records exceed 2080, , implicitly converting varchar field int. grabbed xml query plan small dataset sql server profiler , forced larger dataset query use option (use plan '<insert xml query plan here />').


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more