SQLTeam.com | Weblogs | Forums

Error when using select 1 vs no error when using select *


#1

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


#2

allpartitioned.en = 0 and fst.FUST_sMachine = 1000

This was the problem as allpartitioned.en is a varchar. But I still don't get why I was getting an error
when selecting specific columns, but no error with select *.

Regards