Plz help me on CTE - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Plz help me on CTE

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 01:25:33

I have to count data of all levels
plz help me :

WITH CatTree ( Level,totalNo)
AS
(
SELECT 1 As [Level] , count(aid) As totalNo
FROM members Level
where isID = 'U100000000'

UNION ALL

SELECT Level + 1 ,count(aid)as totalNo
FROM members
where level<5
)

SELECT * FROM CatTree Order By Level

it give me errors as:

Msg 207, Level 16, State 1, Line 14
Invalid column name 'level'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Level'.

Ved Prakash Jha

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-04 : 01:32:57

Try this

WITH CatTree ( Level,totalNo)
AS
(
SELECT 1 As [Level] , count(aid) As totalNo
FROM members Level
where isID = 'U100000000'

UNION ALL

SELECT Level + 1 ,count(totalno)as totalNo
FROM cattree
where level<5
)

SELECT * FROM CatTree Order By Level

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 02:13:42

WITH CatTree ( Level,totalNo)
AS
(
SELECT 1 As [Level] , count(aid) As totalNo
FROM members Level
where isID = 'U100000000'

UNION ALL

SELECT Level + 1 ,count(totalno)as totalNo
FROM cattree
where level<5
)

SELECT * FROM CatTree Order By Level

its give me errors as:

Msg 8120, Level 16, State 1, Line 2
Column 'CatTree.Level' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 467, Level 16, State 1, Line 2
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'CatTree'.

Then I change my qyery as:

WITH CatTree ( Level,totalNo)
AS
(
SELECT 1 As [Level] , count(aid) As totalNo
FROM members Level
group by [Level]
having isID = 'U100000000'

UNION ALL

SELECT Level + 1 ,count(totalno)as totalNo
FROM cattree
INNER JOIN CatTree ON
CatTree.aid= C.isid
group by Level+1
having level<5
)

SELECT * FROM CatTree Order By Level

eroor as:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Level'.
Msg 253, Level 16, State 1, Line 7
Recursive member of a common table expression 'CatTree' has multiple recursive references.

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 02:28:43

can you explain what you're trying to do inside CTE? is this what you want?

;WITH CatTree ( Level,aid)
AS
(
SELECT 1 As [Level] , aid 
FROM [members Level]
where isID = 'U100000000'

UNION ALL

SELECT Level + 1 ,m.aid 
FROM [members Level] m
INNER JOIN CatTree c ON
C.aid= m.isid)

SELECT Level,COUNT(aid)AS TotalNo
FROM CatTree 
WHERE Level<5 
GROUP BY Level

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 03:03:56

I want to count total Data , Level wise
as
I(ABC) have sponsor(>) A and B
A >c
B>D

ABC>e
ABC>f

c>g
d>h
it will show for ABC as
Abc
a b e f Level1
c d Level2
g h Level3

Theb Result would be for ABC:
Leve1=4
Leve2=2
Leve3=2

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 03:14:10

doesnt my solution give same result?

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 03:18:20

sorry..
Forget to tell one thing....
whenever Any Person join company taking different Packages and having their Different Points

P1=1
p2 =6
p3=12

as

I want to count total Data , Level wise
as
I(ABC) have sponsor(>) A (p1) and B(p1)
A >c(p2)
B>D(p3)

ABC>e(p2)
ABC>f(p1)

c>g(1)
d>h(p3)
it will show for ABC as
Abc
a b e f Level1
c d Level2
g h Level3

Theb Result would be for ABC:
Leve1=9
Leve2=18
Leve3=13

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 03:21:49

show some sample data and output you expect out of them. your explanation doesnt make much sense.

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 03:38:46

Package1=1 point
Package2=6 point
Package3=12 point

If A Sponsor B with Package1
A Sponsor C with Package3
A Sponsor D with Package2
A Sponsor E with Package1

B Sponsor F with Package1
C Sponsor G with Package2
D Sponsor H with Package3

A;;;;;;
B;C;D;E;;;;;;;;Level1
F;G;H;;;;;;;;;;;;;;;Level2

For A:
Level1=20
Level2=19

for B:
Level1=1

For C:
Level1=6

For D:
Level1=12

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 03:54:27

please response

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-10-04 : 04:20:09

In the previous Time I were asked about CTE,
Problem is solved, but having different output means it is correct but i want it in no duplicacy in levels.
Query is:

;WITH CatTree ( Level,aid)
AS
(
SELECT 1 As [Level] , aid
FROM [members]
where aid not like 'R%' and isID = 'U100000075'

UNION ALL

SELECT Level + 1 ,m.aid
FROM [members] m

INNER JOIN CatTree c ON
C.aid= m.isid
where m.aid not like 'R%'
)

SELECT Level,(COUNT(aid)* p.iPoint) AS TotalNo
FROM CatTree c1
join scratch s on c1.aid=s.vat_id
join package p on s.vpackage=p.vpackage
WHERE Level<=5
GROUP BY Level,p.ipoint

Output is:

1 4
2 13
3 76
4 211
5 280
3 12
4 42
5 72
2 12
3 60
4 48
5 204

There is Duplicacy in levels I want remove that . it comes due to grpup by clause. If I write
"GROUP BY Level,p.ipoint" instead of "GROUP BY Level" then it gives me error as

error:

Msg 8120, Level 16, State 1, Line 2
Column 'package.iPoint' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

plz help me

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 06:51:32

without seeing some sample data its really hard to understand how tables are related and what you're expecting as output. whats preventing you from posting data and output as repeatedly asked. Know how to post a question by refering below link.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources