Two (related, really) questions.
The first is far more complicated.
1. I need to write, in essence, an EDI parser in SQL Server (probably as a DTS package). The one we're using now is written in Visual FoxPro, and works, but one client has sent us a file with about two million records; the VFP program chugged along for four days before I killed it and tried to find a way to do this in SQL. My company won't pay for an EDI parser, so I need to find someone who can walk me through the process of creating one. Actually, I have a
The way we handle separating out the rows on VFP is through a SCAN, looking for certain three-letter codes at the beginning of each line that denote the beginning of a new record. There is no SCAN command in SQL, but there's obviously a way to do it, or no one would have been able to write a commercial EDI parser that works completely within SQL. The kicker: I don't know VBA/VBScript, and would need walking through it (I'm a C++/C# guy when not data warehousing) if it's involved.
If you're unfamiliar with EDI, bless you, you have a life unhindered by this nightmare, and aside from that can safely ignore this question.
that I think, if I get answered, will get everything into place for me.
2. General SQL question, I think. I want to pull the max value from a column and store it in a variable. Can't do it in either VFP or SQL Server in any configuration of "variable = select max(column) from table." I know I've done it before, but can't find the program where I did it... so does anyone have a quick code snippet they can throw at me?
Thanks.
1. I need to write, in essence, an EDI parser in SQL Server (probably as a DTS package). The one we're using now is written in Visual FoxPro, and works, but one client has sent us a file with about two million records; the VFP program chugged along for four days before I killed it and tried to find a way to do this in SQL. My company won't pay for an EDI parser, so I need to find someone who can walk me through the process of creating one. Actually, I have a
The way we handle separating out the rows on VFP is through a SCAN, looking for certain three-letter codes at the beginning of each line that denote the beginning of a new record. There is no SCAN command in SQL, but there's obviously a way to do it, or no one would have been able to write a commercial EDI parser that works completely within SQL. The kicker: I don't know VBA/VBScript, and would need walking through it (I'm a C++/C# guy when not data warehousing) if it's involved.
If you're unfamiliar with EDI, bless you, you have a life unhindered by this nightmare, and aside from that can safely ignore this question.
that I think, if I get answered, will get everything into place for me.
2. General SQL question, I think. I want to pull the max value from a column and store it in a variable. Can't do it in either VFP or SQL Server in any configuration of "variable = select max(column) from table." I know I've done it before, but can't find the program where I did it... so does anyone have a quick code snippet they can throw at me?
Thanks.
