Syntax error converting varchar value to col data type int 2

[azoe]

I have a union query that was working fine until I added one field. That's all I'll show here to simplify:

Select
Null as RenderHere
From
Where

Union All

Select
ppm.attending_ind as RenderHere
From
Where

I get the error: Syntax error converting the varchar value 'Y' to a column of data type int.

Both sides of the query work independently but together I get that error.

For the second half I tried: convert(varchar(1), ppm.attending_ind) as RenderHere

but I still got the same error. So I even tried: convert(varchar(1), Null) as RenderHere on the top half but I still get the same error.

The field is a char(1) so I tried converting to that too but I get the same error.

Any ideas? Thanks

 

[vongrunt]

Post complete query here... with all FROM/WHERE stuff.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

OK..
Rules for Unions
1. Column number must be then same
2. Column Datatypes must be the same ..

It is the first one that sets the datatypes for all others..
so..

If the issue is with the second query and it is returning 'text' data, and that is what you want, then you will need to cast the datatype in the first query...

However, i suspect you will need to show you actual statement to figure out what isn't working..

this does work (and is is basicly the same as your example)

Select
Null as RenderHere
Union All
Select 1

as does

Select
Null as RenderHere
Union All
Select 'abc'

but..

Select Null as RenderHere
Union All
Select 1
Union All
Select 'test'

fails as the 1 has set the datatype...

so

Select Null as RenderHere
Union All
Select cast(1 as varchar(300))
Union All
Select 'test'

works as the 1 is a text datatype.... (after the cast)

 

[azoe]

ok, here it is. thanks

SELECT 
pe.enc_id as Encounter,
td.source_id as AcctORencounter,
td.source_type as KeyType, 
pm.description as Rendering,
pm.external_id,  
Null as RenderHere,  --*********************THIS IS THE FIRST ONE
70 as RenderReferPercent,
(
		SELECT	[description]
		FROM	[Provider_mstr]
		WHERE	[Provider_ID] = [pe].[Refer_Provider_ID]
	) AS [Refer],

ChargeSIM.Service_Item_ID as SIM,
t.type as Type,
td.paid_amt as PaidAmt,
td.adj_amt as AdjRef, 
mlist.mstr_list_item_desc as department,
t.closing_date as TranClosingDT,
p.claim_type as ClaimType,
p.payer_name as PayerName


FROM

trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join patient_encounter pe on td.source_id = pe.enc_id  
left outer join payer_mstr p on t.payer_id = p.payer_id

left outer join (
SELECT	[Charges].[Charge_ID],
	[Charges].[amt],
	[Charges].[closing_date],
	[Charges].[Rendering_ID],
	[Charges].[Service_Item_ID],
	
	(
		SELECT	TOP 1 [Department]  
		FROM	[Service_Item_mstr] AS [Services] 
		WHERE	[Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
			AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
	) AS [Department]
FROM	[dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_item_id 
left outer join location_mstr lm on pe.location_id = lm.location_id


where

t.closing_date between '20050902' and '20051003'
and t.type <> 'A'


Union ALL  

SELECT 
pe.enc_id as Encounter, 
td.source_id as AcctORencounter, 
td.source_type as KeyType, 
pm.description as Rendering,
pm.external_id,
30 as RenderReferPercent,
convert(char(1), ppm.attending_ind) as RenderHere,  --*********************THIS IS THE SECOND ONE

Referpm.[description] as Refer,
ChargeSIM.Service_Item_ID as SIM,
t.type as Type,
td.paid_amt as PaidAmt,
td.adj_amt as AdjRef, 
mlist.mstr_list_item_desc as department,
t.closing_date as TranClosingDT,
p.claim_type as ClaimType,
p.payer_name as PayerName


FROM

trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join patient_encounter pe on td.source_id = pe.enc_id  
left outer join payer_mstr p on t.payer_id = p.payer_id

left outer join (
SELECT	[Charges].[Charge_ID],
	[Charges].[amt],
	[Charges].[closing_date],
	[Charges].[Rendering_ID],
	[Charges].[Service_Item_ID],
	
	(
		SELECT	TOP 1 [Department]  
		FROM	[Service_Item_mstr] AS [Services] 
		WHERE	[Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
			AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
	) AS [Department]
FROM	[dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_item_id 
left outer join location_mstr lm on pe.location_id = lm.location_id
left outer join provider_practice_mstr ppm on pm.provider_id = ppm.provider_id
left outer join provider_mstr Referpm on Referpm.provider_id = pe.Refer_Provider_ID
inner join provider_practice_mstr ReferPPM on Referpm.provider_id = ReferPPM.provider_id

where

t.closing_date between '20050902' and '20051003'
and t.type <> 'A'
and ChargeSIM.Service_Item_ID in ('93307', '93000', '93224', '95810', '95806', '95805', '95811', '94240', '94060', '94010')
and pe.Refer_Provider_ID is not null 
and pm.description is not null and ppm.attending_ind = 'Y' and pm.delete_ind <> 'Y'
and mlist.mstr_list_item_desc is not null   
and Referppm.attending_ind = 'Y'

 

[SQLDenis]

You have to cast 70 as RenderReferPercent, to varchar
convert(char(1), ppm.attending_ind) as RenderHere is at position 7 and Null as RenderHere is at position 6

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[NoCoolHandle]

I suspect you might have a line number referenced In the error message...

have you tried "double clicking" the errormessage in QA?
and which line did it land you on?

 

[NoCoolHandle]

and pm.description is not null and ppm.attending_ind = 'Y' and pm.delete_ind <> 'Y'
and mlist.mstr_list_item_desc is not null
and Referppm.attending_ind = 'Y'

I would take a look at each of these cols....(i also suspect that the double click trick will land you on one of these lines

Rob

 

[azoe]

SQLDenis and Rob: Thanks - I should have tried counting instead of so many other things.

Rob: I tried double-clicking the error in QA (hadn't ever tried that) and the word Select (the first one) was highlighted. I double-clicked again but no change.

Also, thanks for the info about "the first one that sets the datatypes for all others". Didn't know that either.

Thank you