Hello
I'm a bit stumped here, I have a medium sized query with some joins and subqueries (below).
The problem is, If I use select * in the outer query then it executes fine and correctly returns all the
results. However, if I use anything other than select * (e.g. select 1, select '', select col1...), I get
an error (Msg 245, Level 16, State 1, Line 141
Conversion failed when converting the varchar value 'S1 ' to data type int.)
Anyone know what might cause this?
Here's the code
select
--all2.strippedNumber GIVES ERROR
--1 GIVES ERROR
--'' GIVES ERROR
* --WORK FINE
from
(
select *,row_number() over (order by all1.rn) as 'rn1'
from
(
select allpartitioned.strippedNumber,allpartitioned.part_rn,a.rn,a.id,a.tip,allPartitioned.pp,allpartitioned.en,
pp.pp_spp,pp.pp_id_pp,en.en_sen,en.en_id_en,allpartitioned.Yr from
(
select cast(tst0.strippedNumber as int) as 'strippedNumber',tst0.pp,tst0.en,row_number()
over (order by tst0.rn) - tSt2.rn as 'part_rn',tst0.rn,tst0.Yr from #fsTableAll tSt0 left outer join
(
select tSt1.strippedNumber as 'strippedNumber',
tst1.pp as 'pp',tst1.en as 'en',min(tSt1.rn) as 'rn',count(*) as 'ct' from
(
select strippedNumber,pp,en,row_number() over(order by rn) as 'rn' from #fsTableALl
)
tSt1 group by tSt1.strippedNumber,tst1.pp,tst1.en
)
tSt2 on tst0.strippedNumber = tst2.strippedNumber and tst0.pp =
tst2.pp and tst0.en = tst2.en
)
allPartitioned
left outer join #fstableall a on a.rn = allPartitioned.rn
left outer join
(
select pop_databaseid as 'pp_dbid',pop_sWorkplace as 'pp_spp',pop_id_workplace as
'pp_id_pp' from workplace where pop_databaseid = 0
)
pp on a.pp = pp.pp_id_pp left outer join
(
select ele_databaseid as 'en_dbid',ele_machine as
'en_sen',ele_id_machine as 'en_id_en' from machine where ele_databaseid = 0
)
en on a.en = en.en_id_en left outer join f_nums fst on
fst.FUST_DatabaseID = a.dbid and fst.fust_Number = allpartitioned.strippedNumber and
(
(
fst.fust_sWorkplace = 1000 and allpartitioned.pp = 0
)
or
(
fst.FUST_sWorkplace = pp.pp_spp
)
)
and
(
(
allpartitioned.en = 0 and fst.FUST_sMachine = 1000
)
or
(
allPartitioned.part_rn = 0 and
fst.FUST_sMachine = en.en_sen
)
or
(
allPartitioned.part_rn > 0 and fst.FUST_sMachine = 1000+allPartitioned.part_rn
)
)
where isnull(fst.FUST_SQLID,0) = 0
) all1
) all2;
Regards