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.
| Author |
Topic |
|
trento
Starting Member
13 Posts |
 Posted - 2009-10-21 : 22:20:23
|
| Hi,I'm trying to implement one logic for my library and wondering if anybody can suggest me better idea then I'm doing:I have a series transactions that related to each other thru relationship between SEQ and SEQ-OLd:e.g:... SEQ . SEQ-O #1.. 01... 00 * brand new record SEQ=01, SEQ-O=0(null)#2.. 02... 01 * rec #1 changed and became SEQ=2, tracked to #1 thru SEQ-O=1#3.. 03... 02#4.. 04... 03....#50.. 50... 49 *etc.. up to let say 50 first I composed SQL with 2 levels nested WHERE like below, but it still don't deep enough, let say for related change that have 49 entries it catches only 17, then I did 3 NESTs and still not enough: only got 26/49. And I decided to stop with NESTs, it already does'nt seem be reasonable.I can not do any biz change to table making any key or another id-flag. Only whay to catch the hole 49 records thru SEQ/SEQ-O relationship.Maybe there is some tricky thing that can SELECT all of related changes no matter how many of them.SELECT * FROM LIBHIST WHERE ( SEQ IN (SELECT DISTINCT SEQ FROM LIBHIST WHERE SEQ IN (SELECT DISTINCT SEQ FROM LIBHIST WHERE SEQ IN (SELECT DISTINCT SEQ FROM LIBHIST WHERE SEQ = 7 OR SEQ-O= 7) OR SEQ-O IN (SELECT DISTINCT SEQ FROM LIBHIST WHERE SEQ = 7 OR SEQ-O= 7)) OR SEQ-O IN ... etc. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-21 : 23:19:28
|
what version of SQL Server are you using ?Look into recursive CTE if you are using SQL 2005 or 2008
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
trento
Starting Member
13 Posts |
Posted - 2009-10-22 : 13:08:42
|
| Tx, I'm on 8.00.760, it should be 2000?And I can't do anything on sqlserver side, I do my sql inside small microfocus program, so will see see how to do this.Thanks againT |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-22 : 13:14:25
|
quote:
Originally posted by trento
Tx, I'm on 8.00.760, it should be 2000?And I can't do anything on sqlserver side, I do my sql inside small microfocus program, so will see see how to do this.Thanks againT
yup thats sql 2000. see below to get an idea on versions of sql serverhttp://www.sqlteam.com/article/sql-server-versions |
|
|
|
trento
Starting Member
13 Posts |
Posted - 2009-10-23 : 15:59:38
|
| Supplied links don't work to me, as they need to change db configuation, I was thinking only how to chagne sql, I don't have right to alter anything on db ojbects. And all data in single table.TxT |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-23 : 16:22:23
|
| The 2000 solution (of mine) that Visakh suggested above does not alter the database. If you can't figure out how to apply that technique then please post some sample data and expected output.Be One with the OptimizerTG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-24 : 04:49:03
|
quote:
Originally posted by trento
Supplied links don't work to me, as they need to change db configuation, I was thinking only how to chagne sql, I don't have right to alter anything on db ojbects. And all data in single table.TxT
there's no instruction to alter db configuration in links i posted. |
|
|
|
|
|
|
|
|
tricky nested select,