Level for Users - 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

Level for Users

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 04:20:52

Case study:
Table members:
Associate SponsorID

U1 U0
U2 U1
U3 U1
U4 U2
U5 U3
U6 U4
U7 U4
U8 U5

Table Scratch

Package Vat_id

P1 U0
P2 U1
P2 U2
P2 U3
P2 U4
P1 U5
P1 U6
P2 U7
P1 U8

If I m checking Levels of id U1
then it will give me result as

DownLine Details Of U1 is:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Level1:
Associate SponsorID Package

U2 U1 P2
U3 U1 P2

Level 2:

Associate SponsorID Package

U4 U2 P2
U5 U3 P1

Level 3:

Associate SponsorID Package

U6 U4 P1
U7 U4 P2
U8 U5 P1

as... fetch the result till Their 5 level .

Programmitically I have done this.

But Online its processing is too slow.
Session Time-out during this Process in online.

plz help me to fetch values by the query...

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 05:02:24

if you're using SQL 2005

;With CTE(Associate SponsorID,Package,level)AS
(
SELECT m.Associate,m.SponsorID,s.Package,0
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.SponsorID
WHERE SponsorID='U0'
UNION ALL
SELECT m.Associate,m.SponsorID,s.Package,c.Level+1
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.SponsorID
INNER JOIN CTE c
ON c.Associate=m.SponsorID
WHERE SponsorID='U0'
)

SELECT *
FROM CTE
WHERE Level=@Level

@Level is level you want to retrive

and if its sql 2000 use the logic used in link below:-

http://support.microsoft.com/kb/248915

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 06:51:54

what is CTE?

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 06:57:12

I have used this query get an error as

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.

I m not getting the first line plz make understand me

I m using SQL 2005

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 08:59:01

I have used yhe query as:

SELECT m.aid,m.isid,s.vPackage
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.isid
WHERE isid='U100000021'
UNION ALL
SELECT m.aid,m.isid,s.vPackage
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.isid

WHERE isid=m.aid

got the result as

Associate Sponsor Package No Column as Level

U100000022 U100000021 Package1 0
U100000023 U100000021 Package1 0
U100000024 U100000021 Package1 0
U100000025 U100000021 Package1 0
U100000026 U100000021 Package1 0
U100000027 U100000021 Package1 0
U100000028 U100000021 Package1 0
U100000029 U100000021 Package1 0
U100000030 U100000021 Package1 0
U100000031 U100000021 Package1 0
U100000032 U100000021 Package1 0

What i need that:

Associate Sponsor Package No Column as Level

?x U100000022 ? 1
?y U100000023 ? 1

Associate Sponsor Package No Column as Level

?A ?x ? 2
?B ?y ? 2

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 10:07:34

quote:
Originally posted by vedjha

what is CTE?

Ved Prakash Jha

CTE is Common Table Expression

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 10:09:29

quote:
Originally posted by vedjha

I have used this query get an error as

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.

I m not getting the first line plz make understand me

I m using SQL 2005

Ved Prakash Jha

show your full query used.Also check if your db compatibility level is 90 use below to check it

sp_dbcmptlevel 'yourdatabasename'

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 11:11:46

The current compatibility level is 90.

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-30 : 11:55:14

The current compatibility level is 90.

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 14:09:57

quote:
Originally posted by vedjha

The current compatibility level is 90.

Ved Prakash Jha

then it should work.can you post full query used?

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-31 : 02:53:45

With CTE(aid,isid,vPackage,level)AS
(
SELECT m.aid,m.isid,s.vPackage,0
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.isid
WHERE isid='U100000000'
UNION ALL
SELECT m.aid,m.isid,s.vPackage,c.Level+1
FROM Members m
INNER JOIN Scratch s
ON s.Vat_id=m.isid
INNER JOIN CTE c
ON c.aid=m.isid
WHERE m.isid='U100000000'
)

SELECT *
FROM CTE
WHERE Level=1

on line it works
ut don't give any result

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-08-31 : 07:15:06

Thanks Vikash It is working.

Ved Prakash Jha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources