help me with this query. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help me with this query.

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-08-25 : 03:43:31

i have a table named table_1.

it has two columns acct_sub_gp(numeric(6)), acct_ssid(numeric(6))
the table has following type of data.

acct_sub_gp acct_ssid
----------- ---------
1. 1020 null
2. 1031 1020
3. 1051 1020
4. 1061 1020
5. 1085 1061

the query should return all the rows that is linked with 1020, which will be an input.

that is, if the user input is acct_sub_gp = 1020, then all the rows whose acct_ssid = 1020 and any other rows that is linked with 1020 is to be returned, but not the 1020 row itself, where acct_ssid = null.

with the above data, rows 2 to 5 will be returned. row 5 will be returned because, 1061 is related to 1020, so every row that is again related to 1061 will be returned, like a recursive function.

i have been trying to write a correlated sub-query but couldn't succed until now.

plz ask me if you need any clarification.

thanks and regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 03:51:45

use a recursive CTE like below

;With Your_CTE (acct_sub_gp,acct_ssid,parent_sub_gp,parent_acct_ssid) AS 
(SELECT acct_sub_gp,acct_ssid,null,null
FROM YourTable
WHERE acct_ssid IS NULL
AND acct_sub_gp=YourValue
UNION ALL
SELECT t.acct_sub_gp,t.acct_ssid,c.acct_sub_gp,c.acct_ssid
FROM YourTable t
INNER JOIN Your_CTE c
ON c.acct_sub_gp=t.acct_ssid
)

SELECT * FROM your_CTE WHERE acct_ssid IS NOT NULL

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-08-25 : 03:56:40

what is CTE / your_CTE?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 04:00:36

see
http://msdn.microsoft.com/en-us/library/ms190766.aspx?wt.slv=RightRail
http://msdn.microsoft.com/en-us/library/ms186243(sql.90).aspx

KH
[spoiler]Time is always against us[/spoiler]

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-08-25 : 04:54:46

the above query returns the following errors when executed:

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

i have replaced yourtable with table_1 and your value with 1020

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 05:16:29

whats the datatype of columns acct_sub_gp,c.acct_ssid?

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-08-25 : 05:31:28

numeric(6,0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 05:36:22

Modify like this & try:-

;With Your_CTE (acct_sub_gp,acct_ssid,parent_sub_gp,parent_acct_ssid) AS 
(SELECT acct_sub_gp,acct_ssid,CAST(null AS Numeric(6,0)),CAST(null AS Numeric(6,0))FROM YourTable
WHERE acct_ssid IS NULL
AND acct_sub_gp=YourValue
UNION ALL
SELECT t.acct_sub_gp,t.acct_ssid,c.acct_sub_gp,c.acct_ssid
FROM YourTable t
INNER JOIN Your_CTE c
ON c.acct_sub_gp=t.acct_ssid
)

SELECT * FROM your_CTE WHERE acct_ssid IS NOT NULL

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-08-25 : 05:40:59

it is working, seems to be ok.

i have got huge data, let me check and thank you so much.

i didn't know about CTE, new concept. thanks so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources