| Author |
Topic |
|
havek
Starting Member
8 Posts |
 Posted - 2010-06-10 : 17:26:20
|
| Help please. I have to read data sequencially or row by row from top to bottom. When a condition is met i must do something else then return to the original sequence and continue. The question is how to make it work. Working with "if's" which i think should cut the cheese has not given me a result. I know i am doing something wrong. Would anyone have a snippet? By the way this is all in SQL 2000 (sorry not my company, not my decision). I also do vbscript if you prefer or understand that is a better option. Thanks.Ex: Table1 column_A column_Brow1 10 yrow2 3 yrow3 L yrow4 n <--- if this is 'n' then get data from somewhere else. Then continue until end of data.row5 55 yrow6 36 y |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-10 : 17:59:16
|
| We'll need a lot more data than that to help you. Are you selecting data from a different table? Are you update column_b from two different places? What are you doing to/with the data when column_a <> 'n', etc.JimEveryday I learn something that somebody else already knew |
 |
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 06:53:47
|
| Thanks for your prompt reply. ok, first thing. I am migrating a macro from access 97 to sql 2000. Yes, after 12 years a newbie has the guts to finally "update" this logic. Basically I need to select data from 5 other tables when in table1 columnb = 'n' or 'z' or whatever. Depending on the value of table1 columnb I'll know which table I have to jump to. This is a sad story of linking 6 tables with left joins where there is no unique/primary keys (rather matching), the key constantly changes, and the only structure is that the data is read downwards from the main table. Now, the purpose is to create a flat file with the data that is being selected/read. No update, insert, convert, etc. is done to the data at any time. Just create a flat file based on the sequence. if you need more info please let me know. Thanks a million. |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 06:56:47
|
| Thanks for your prompt reply. ok, first thing. I am migrating a macro from access 97 to sql 2000. Yes, after 12 years a newbie has the guts to finally "update" this logic. Basically I need to select data from 5 other tables when in table1 columnb = 'n' or 'z' or whatever. Depending on the value of table1 columnb I'll know which table I have to jump to. This is a sad story of linking 6 tables with left joins where there is no unique/primary keys (rather matching), the key constantly changes, and the only structure is that the data is read downwards from the main table. Now, the purpose is to create a flat file with the data that is being selected/read. No update, insert, convert, etc. is done to the data at any time. Just create a flat file based on the sequence. if you need more info please let me know. Thanks a million. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-11 : 08:17:33
|
| can you provide more sample data? detail more than 1 input table and the expected output.I'd suspect you either need a CASE statement or a UNION ALL statement to merge 2 halves of a result set. |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 10:18:23
|
| sorry, i did not expect this many questions. But this is why i joined this forum, you people are thorough. ex:table1 columna columnb columnc(source) columnd(target column)row1 10 y table1 nullrow2 LD y table1 nullrow3 00 n * table2 * date_valuerow4 55 y table1 nullrow5 f4 z * table3 * info_id row6row7So not only is table1 dictating where the data is but it also tells me what data to get. By the way the data i am selecting in table1 is columna. I hope this time i explained better. Once again, thanks.* shows when i have to jump to another table and column. |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 10:34:19
|
| Lost format. Retrying to display example properly.table1 columna columnb columnc (source) columnd (target column)row1 10 y table1 nullrow2 LD y table1 nullrow3 OO n table2 date_valuerow4 55 y table1 nullrow5 f4 z table3 info_idrow6 2d y table1 nullrow7 3a y table1 null |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 11:13:43
|
| Sorry forget to include a practical example. Also corrected an error.table1 columna columnb (source) (target column) (match_id)row1 10 y table1 nullrow2 LD y table1 nullrow3 n * table2 * date_value 289h2hhhrow4 y55r y table1 nullrow5 z * table3 * info_id nedue89row6 x9 y table1 nulltable2 (date_value) (match_id)row1 20100513 289h2hhh ......row10 20101231 yyu765getable3 (info_id) (match_id)row1 rq34kq 106588......row75 3uwenwd77 nedue89 So the flat file would result in something like this:10LD20100513y55r 3uwenwd77x9 |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-06-11 : 12:42:37
|
| I have tried unions but the result set is not sequencial and data also starts to duplicate. I hate to say this but i think a cursor is the way to go and worse a second cursor within the first. I have tried while loops but cant get it right either.Normally, i do stuff like:--if (select datepart(dw,getdate())='Monday')--begin--select this, that, etc--end--else--some other if or just an else--endI just cant get it to work with what i need. Much less try to loop it. By the way I am just giving you a heads up of what i've used. So you dont think i am trying to leech off you. Again, vbscript is another way i am willing to go. Problem is it explodes on me when it validates columnb or columnc. If i stay within table1 it works. Maybe just missing another loop. Again, thanks a million. |
|
|
|
havek
Starting Member
8 Posts |
Posted - 2010-07-01 : 16:30:14
|
| Since I was able to solve my problem on my own I will post my solution for those who run into something like this. I did it in vbscript since SQL scripts alone are not capable of solving it. (any programming language would have also been good).What I need to do is iterate thru tables2 row by row from top to bottom looking for matches that come from table. Then write to a flat file matched values. Oh yeah, in the process of this all I am converting columns to rows. Ex" tables2 column1 01 02 03 AA BB CCis output to the flat file like so 010203AABBCC. Tricky but turned out to be fun. Again, only matching values. The output could also be like so if no matches were found. 0102 BBCC or 0102 AA CCI assume you know how to create connections, recordsets, write to files, make your declarations, etc.rs1.open "select fields from table ", dbconnrs2.open "select fields2 from table2 ",dbconn2rs1.movefirstdo while not rs1.eof rs2.movefirst do while not rs2.eofif RTrim(rs2.fields("fields2").Value) = "A" thenrts.write(rtrim(rs2.fields("values").Value)) 'stringelseif rs2.fields("fields2").Value = "B" thenrts.write rs1.fields("values").Valueelseif rtrim(rs2.fields("fields2").Value) = "End" thenrts.write vbCrLfend ifrs2.movenextlooprs2.movenextlo1prs2.closers1.closeconn.closerts.close |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
|
|
|
Looping thru data