Types don't match between the anchor and the recur

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Types don't match between the anchor and the recur

Author

Topic

Camarate
Starting Member

2 Posts

Posted - 2010-06-16 : 11:56:17

Dear All,

I have a SQL Server Express 2005 database installed on a computer with Windows Vista. When I run the query below:

WITH AE_AIR_SYSTEM_RECURSIVE (SQ_SYSTEM, CL_SYSTEM_CLEAR) AS (
SELECT AISY_SQ_AIR_SYSTEM
, CAST('' AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM WHERE AISY_SQ_AIR_SYSTEM_FATHER = 137
UNION ALL
SELECT S.AISY_SQ_AIR_SYSTEM
, CASE WHEN CL_SYSTEM_CLEAR = '' THEN '' ELSE CL_SYSTEM_CLEAR + '/' END + CAST(S.AISY_NM_AIR_SYSTEM AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM S
INNER JOIN AE_AIR_SYSTEM_RECURSIVE SAI ON S.AISY_SQ_AIR_SYSTEM_FATHER = SQ_SYSTEM)
SELECT * FROM AE_AIR_SYSTEM_RECURSIVE

I get the following result:

SQ_SYSTEM CL_SYSTEM_CLEAR
138
139
143
144 General Exhaust
141 Main Power Generator
142 Secondary Power Generator
140 Main Boiler

But, when I installed the SQL Server Express 2005 on a computer with Windows 7,the same query returns the following message error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "CL_SYSTEM_CLEAR" of recursive query "AE_AIR_SYSTEM_RECURSIVE".

Why occurs this error? What do I do to solve it?

Thank you, Marcelo Camarate

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-06-16 : 23:54:10

it doesn't look like you have a need for varchar(max), but this should resolve the error.


;
WITH AE_AIR_SYSTEM_RECURSIVE 
(SQ_SYSTEM, CL_SYSTEM_CLEAR) 
AS ( 
SELECT AISY_SQ_AIR_SYSTEM
, CAST('' AS VARCHAR(MAX)) 
FROM 
	EHS.AE_AIR_SYSTEM WHERE 
	AISY_SQ_AIR_SYSTEM_FATHER = 137 
UNION ALL 
SELECT S.AISY_SQ_AIR_SYSTEM
, cast(CASE WHEN CL_SYSTEM_CLEAR = '' THEN '' ELSE CL_SYSTEM_CLEAR + '/' END as varchar(max)) + CAST(S.AISY_NM_AIR_SYSTEM AS VARCHAR(MAX)) 
FROM 
EHS.AE_AIR_SYSTEM S 
INNER JOIN AE_AIR_SYSTEM_RECURSIVE SAI ON S.AISY_SQ_AIR_SYSTEM_FATHER = SQ_SYSTEM) 
SELECT * FROM AE_AIR_SYSTEM_RECURSIVE

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Camarate
Starting Member

2 Posts

Posted - 2010-06-17 : 11:46:43

Dear Vinnie881,

Thanks for your reply, but I did what you suggested and still have the same error.

But surprise yourself. When I replaced the CAST ('' AS VARCHAR (MAX)) in the first SELECT to CAST (AISY_NM_AIR_SYSTEM AS VARCHAR (MAX)), as follows:

WITH AE_AIR_SYSTEM_RECURSIVE (SQ_SYSTEM, CL_SYSTEM_CLEAR) AS (
SELECT AISY_SQ_AIR_SYSTEM
, CAST(AISY_NM_AIR_SYSTEM AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM WHERE AISY_SQ_AIR_SYSTEM_FATHER = 137
UNION ALL
SELECT S.AISY_SQ_AIR_SYSTEM
, CASE WHEN CL_SYSTEM_CLEAR = '' THEN '' ELSE CL_SYSTEM_CLEAR + '/' END + CAST(S.AISY_NM_AIR_SYSTEM AS VARCHAR(MAX))
FROM EHS.AE_AIR_SYSTEM S
INNER JOIN AE_AIR_SYSTEM_RECURSIVE SAI ON S.AISY_SQ_AIR_SYSTEM_FATHER = SQ_SYSTEM)
SELECT * FROM AE_AIR_SYSTEM_RECURSIVE

no more error appears, although not show the expected result, as follows:

SQ_SYSTEM CL_SYSTEM_CLEAR
138 Athletics Stadium
139 Soccer Stadium
143 Multisport Gym
144 Multisport Gym/General Exhaust
141 Soccer Stadium/Main Power Generator
142 Soccer Stadium/Secondary Power Generator
140 Athletics Stadium/Main Boiler

because the red portion could not appear in the concatenated column.

To clarify, the structure of the EHS.AE_AIR_SYSTEM is as follows:

Column Name Data Type
AISY_SQ_AIR_SYSTEM Int
AISY_SQ_AIR_SYSTEM_FATHER Int
AISY_NM_AIR_SYSTEM varchar(50)
AISY_DS_AIR_SYSTEM varchar(1500)

I appreciate any other suggestions.

Thanks, Marcelo Camarate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources