SQLTeam.com | Weblogs | Forums

Error converting data type varchar to bigint


#1

Hello everyone,
My objective is to bring back 1 record for an account that has moved/transferred to a new location. For the specific account I'm looking at, I continue to bring back two records, because there are two order numbers that are unique but were created and closed on the same dates. So the sub-queries that I'm using to bring back the max value doesn't work in this scenario. I've now tried to had min and max functions to the To order number, and the From order number which is now bringing me back the error message within the subject line, The code has been pasted below:

IF OBJECT_ID('TEMPDB..#tmp1o') IS NOT NULL DROP TABLE #tmp1o IF OBJECT_ID('TEMPDB..#tmp2o') IS NOT NULL DROP TABLE #tmp2o IF OBJECT_ID('TEMPDB..#tmp3o') IS NOT NULL DROP TABLE #tmp3o

select distinct
t.SYS_SBT, t.LS_CHG_DTE_SBT,
ts.RES_NAME_SBB ToSubName,
fs.RES_NAME_SBB FromSubName,
ts.CUST_ACCT_NO_SBB ToCust,
fs.CUST_ACCT_NO_SBB FromCust,
t.SUB_ACCT_NO_SBT To_Acct_No,
t.XREF_ACCT_NO_SBT From_Acct_No,
ts.HSE_KEY_SBB ToHouseKey,
fs.HSE_KEY_SBB FromHouseKey,
th.addr1 ToAdd1,
fh.addr1 FromAdd1,
th.HouseActivity ToHouseActivity,
fh.HouseActivity FromHouseActivity,
td.DwellingCatName ToDwellingCatName,
fd.DwellingCatName FromDwellingCatName,
td.GeographyKey ToGeographyKey,
fd.GeographyKey FromGeographyKey,

case
when ts.ext_stat_sbb = ' ' then 'Normal'
when ts.ext_stat_sbb = 'C' then 'Voluntary Disconnect'
when ts.ext_stat_sbb = 'E' then 'Non-Pay Disconnect'
when ts.ext_stat_sbb = 'Z' then 'Charged Off'
else ' ' end as 'ToSubStatus',

case
when fs.ext_stat_sbb = ' ' then 'Normal'
when fs.ext_stat_sbb = 'C' then 'Voluntary Disconnect'
when fs.ext_stat_sbb = 'E' then 'Non-Pay Disconnect'
when fs.ext_stat_sbb = 'Z' then 'Charged Off'
else ' ' end as 'FromSubStatus'

into #tmp1o

from externaluser.vantage.SBT_ACCT_TRANS as t (nolock) inner join externaluser.vantage.SBB_BASE as ts (nolock)
on t.SUB_ACCT_NO_SBT = ts.SUB_ACCT_NO_SBB

inner join WISDM.dim.House as th (nolock)
on ts.HSE_KEY_SBB = th.CSGHouseKey

join wisdm.dim.Dwellingflat as td (nolock)
on th.DwellingKey = td.DwellingKey
and th.GeographyKey = td.GeographyKey

inner join externaluser.vantage.SBB_BASE as fs (nolock)
on fs.sub_acct_no_sbb = t.xref_acct_no_sbt

inner join WISDM.dim.House as fh (nolock)
on fs.HSE_KEY_SBB = fh.CSGHouseKey

inner join wisdm.dim.Dwellingflat as fd (nolock)
on th.DwellingKey = fd.DwellingKey
and th.GeographyKey = fd.GeographyKey

where t.ACCT_TRANS_FLG_SBT = 'F'
and t.SYS_SBT = '8155'
and t.SUB_ACCT_NO_SBT = '8155200090956386'

--select * from #tmp1o

select *

into #tmp2o

from #tmp1o

where (left(FromAdd1,10) = left(ToAdd1,10)
and right(FromAdd1,5) <> right(ToAdd1,5) ----change this to = to find same address
)

and (FromDwellingCatName = 'COMMERCIAL' OR ToDwellingCatName = 'Commercial')

--select * from #tmp2o

select distinct b.*, min(oc.[ORDER_NO_OCR]) as ToOrderNumber, max(oc2.[ORDER_NO_OCR]) as FromOrderNumber, oc.CREATE_DTE_OCR ToOrderCreateDate, oc.[COMPL_DTE_OCR] ToOrderCompleteDate,
oc2.CREATE_DTE_OCR FromOrderCreateDate, oc2.[COMPL_DTE_OCR] FromOrderCompleteDate, wt.[PERNR] as ToPernr, wt.[NTLOGIN] as ToNTLogin, wt.[ROLE] AS Torole, sp2.name as Toname

into #tmp3o

from #tmp2o b inner join ExternalUser.[iCoMP].[t_WSTCBSTransactionData] wt (nolock)
on convert(varchar(255),b.To_Acct_No) = wt.[ACCOUNT_NUMBER]

inner join ExternalUser.[EMS].[SAPCurrent] sp2 (nolock)
on wt.[PERNR] = sp2.[PERNR]

inner join [Vantage].[OCR_ORDER_COMP] oc (nolock)
on b.ToCust = oc.cust_acct_no_ocr
and b.ToHouseKey = oc.hse_key_ocr

inner join [Vantage].[OCR_ORDER_COMP] oc2 (nolock)
on b.FromCust = oc2.cust_acct_no_ocr
and b.FromHouseKey = oc2.hse_key_ocr

--where oc2.[COMPL_DTE_OCR] = (select max([COMPL_DTE_OCR]) as [COMPL_DTE_OCR] from [Vantage].[OCR_ORDER_COMP] oc3 (nolock)
-- where oc2.cust_acct_no_ocr = oc3.cust_acct_no_ocr
-- and oc2.hse_key_ocr = oc3.hse_key_ocr)

--and oc.[COMPL_DTE_OCR] = (select max([COMPL_DTE_OCR]) as [COMPL_DTE_OCR] from [Vantage].[OCR_ORDER_COMP] oc4 (nolock)
-- where oc.cust_acct_no_ocr = oc4.cust_acct_no_ocr
-- and oc.hse_key_ocr = oc4.hse_key_ocr)

where oc2.[COMPL_DTE_OCR] between '2017-09-01' and '2017-09-30'
and oc.[ORDER_NO_OCR] = 'S'
and oc2.[ORDER_NO_OCR] = 'S'

group by b.SYS_SBT, b.LS_CHG_DTE_SBT,
b.ToSubName,
b.FromSubName,
b.ToCust,
b.FromCust,
b.To_Acct_No,
b.From_Acct_No,
b.ToHouseKey,
b.FromHouseKey,
b.ToAdd1,
b.FromAdd1,
b.ToHouseActivity,
b.FromHouseActivity,
b.ToDwellingCatName,
b.FromDwellingCatName,
b.ToGeographyKey,
b.FromGeographyKey,
b.ToSubStatus,
b.FromSubStatus,
oc.[ORDER_NO_OCR], oc2.[ORDER_NO_OCR], oc.CREATE_DTE_OCR, oc.[COMPL_DTE_OCR], oc2.CREATE_DTE_OCR, oc2.[COMPL_DTE_OCR], wt.[PERNR], wt.[NTLOGIN], wt.[ROLE], sp2.name

select * from #tmp3o

Any and all help is greatly appreciated. Thank you.

D.


#2

Can you just use

SELECT TOP 1 ...
FROM #tmp2o
ORDER BY tie-break-sort-order

i.e. instead of using MAX etc. - so you only get just the one record?


#3

You have database and application problems, requiring a re-write.

Tell your boss the code is terrible and maintenance might be more expensive than rewriting it. That code is guaranteed to be buggy.

My recommendation:
Use this to create an n-tier separation. Leave the database alone, create new stored procedures and perform this logic in the application (c#): http://systemdotpersistence.blogspot.com