Common Table Expression (CTE) query problem

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

with t as
(
select name,Id,SrID,'avik' as [senior] from sal where SrID=0
union all
select sal.name,sal.Id,sal.SrID,sal.name as [senior] from sal inner join t on sal.SrID= t.Id
)
select * from t

Error :

C#

Types don't match between the anchor and the recursive part in column "senior" of recursive query "t".

Table : Sal (Id,Name,Sal,SrId)

Please help .

Posted 27-Jan-16 23:05pm

Avik Ghosh22

Updated 27-Jan-16 23:37pm

Andy Lanng

v2

Add a Solution

Comments

Member 11137292 28-Jan-16 5:20am

can you post the full table definition for [sal]

Herman<T>.Instance 28-Jan-16 5:23am

what is the datatype of sal.name?

Avik Ghosh22 28-Jan-16 5:58am

CREATE TABLE [dbo].[sal](
[name] [varchar](50) NULL,
[sal] [decimal](18, 0) NULL,
[Id] [int] NULL,
[SrID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'Avik', CAST(100 AS Decimal(18, 0)), 1, 0)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'ASD', CAST(200 AS Decimal(18, 0)), 2, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'DSA', CAST(200 AS Decimal(18, 0)), 3, 2)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'QWE', CAST(400 AS Decimal(18, 0)), 4, 3)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'EFG', CAST(400 AS Decimal(18, 0)), 5, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'PQR', CAST(500 AS Decimal(18, 0)), 6, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'qqq', CAST(111 AS Decimal(18, 0)), 7, 3)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'rrr', CAST(222 AS Decimal(18, 0)), 8, 2)

Avik Ghosh22 28-Jan-16 5:59am

It is a test table so I did not set any primary key .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2016-01-27T23:46:00

Solution 1

The problem is in constant 'avik' because of data type/size difference. Assuming that sal.name is of type varchar(100), will conflict of 'avik' which is of varchar(4). Problem would resolve if you match the both types like cast('avak' as VARCHAR(100)).'

Try this.

SQL

DECLARE @SAL TABLE
(
	NAME VARCHAR(100),
	ID   INT,
	SRID INT
)


INSERT INTO @SAL (NAME, ID, SRID)
SELECT 'avik', 1, NULL
UNION ALL
SELECT 'B', 2, 1
UNION ALL
SELECT 'C', 3, 2
;


with t as
(
select name,Id,SrID,cast('avik' as varchar(100)) as [senior] from @SAL where SrID IS NULL
union all
select S.name,S.Id,S.SrID,T.name as [senior] from @SAL S inner join t on S.SrID = t.Id
)
select * from t

Posted 27-Jan-16 23:46pm

_Asif_

Comments

Avik Ghosh22 28-Jan-16 6:11am

Thanks asif .your query works fine but when i am trying this from my table
got same problem.

CREATE TABLE [dbo].[sal](
[name] [varchar](50) NULL,
[sal] [decimal](18, 0) NULL,
[Id] [int] NULL,
[SrID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'Avik', CAST(100 AS Decimal(18, 0)), 1, 0)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'ASD', CAST(200 AS Decimal(18, 0)), 2, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'DSA', CAST(200 AS Decimal(18, 0)), 3, 2)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'QWE', CAST(400 AS Decimal(18, 0)), 4, 3)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'EFG', CAST(400 AS Decimal(18, 0)), 5, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'PQR', CAST(500 AS Decimal(18, 0)), 6, 1)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'qqq', CAST(111 AS Decimal(18, 0)), 7, 3)
INSERT [dbo].[sal] ([name], [sal], [Id], [SrID]) VALUES (N'rrr', CAST(222 AS Decimal(18, 0)), 8, 2)