EDI parser

Anybody dealt with EDI parsing/composing?Iiiiiiick!

Which digi-gods did you tick off that you got that kind of an assignment ?!?! Surely you can play the piano down at the cat house until some decent work comes along!

-PatP|||Ooooooooooooooo. Ugh.

Yes I have. Are you trying to compose ANSI EDI or read it? Either way, I used to use a tool from EDIdev (www.edidev.com). Very functional and pluggable. It did not receive a lot of support from my org because it was perceived as too "custom". I think we ended up using a combination of Mercator and GenTran, plus one other tool.

I was very pleased with the EDIdev toolset however. A friend of mine swore that he could achieve the same results with XSL, but that would have required starting at ground zero and I wasn't willing to do that. FrEDI gives you a ready-built ActiveX framework from which to start; just be aware that there's still a lot of work to be done to make it functional.

Feel free to e-mail me if you want more info.

hmscott|||Icky-poo.

I worked on EDI projects for Sterling Commerce (makers of GENTRAN). It was my first intro to EDI, and it was blind-date ugly stuff. I didn't have to parse it, just dealt with the GENTRAN output, but the more I learned about it the more amazed I was that the whole thing worked at all. Kinda like watching sausage being made...|||Ooooooooooooooo. Ugh.

Yes I have. Are you trying to compose ANSI EDI or read it?
hmscott
Actually, bofem (both of them)

First I need to have an X12 intake and put the parsed results into their pathetic db, then I need to take their data that I pre-processed for NSF (Thank Codd!!!!) and create 837P/I to be submitted to Medicaid...

I thought after finishing NSF I'd never have to deal with it again...|||I'll stick by recommendation then.

1. I was able to get quick responses from them when I needed support;
2. It fit in nicely with the COM model we were using;
3. Making calls to the database from within the app was very straightforward;
4. The price was about right ($995 five years ago).

One thing we did to ease matters a bit was to create a "master" EDI database which stored things like delimiters, trading partner info and any other meta-data. Plus, we stored things like sessions, logs and sequence numbers. One or two calls to this database and we were off to the races. If we needed to change things once we hit the integration testing phase (such as flipping the flag from 'test' to 'production', it was a simple change in the database.

Mind you, this was five years ago. There may be lots of better things out there now.

Good luck!

hmscott

----------------------
When the stuff hits the fan, the president asks "where are the carriers?" Everyone else asks, "where are the backups?"

Actually, bofem (both of them)

First I need to have an X12 intake and put the parsed results into their pathetic db, then I need to take their data that I pre-processed for NSF (Thank Codd!!!!) and create 837P/I to be submitted to Medicaid...

I thought after finishing NSF I'd never have to deal with it again...|||After searching for hours, what you recommended still came up to be the only feasible tool, other than BizTalk (the client sent 3 of its guys for training, and they came back to me to actually implement it.)

When working on NSF I designed a db that would hold pre-processed collected info that is ready to be sent. The process was implemented through 1 stored procedure with 3 parameters: date, plan, and test/prod. Later I removed the 3rd because running for the same period/plan would overlay the previous results for as long as the final output is not generated. It still is a very light weight, so running against prod at 10AM does not even reveal itself to the users.

My final choice would probably be to use this EDI parser, which I'd through against NSF-designated db, instead of rewriting the whole thing from scratch.

Thanks hmscott.|||You're welcome. Glad to be of some help for a change!!!sql

dynamic SQL variable with Output ?

I know this has been dealt with a lot, but I would still really
appreciate help. Thanks.

I am trying to transform this table
YY--ID-Code-R1-R2-R3-R4...R40
2004-1-101--1--2-3-4
2004-2-101--2--3-4-2
...
2005-99-103-4-3-2-1

Into a table where the new columns are the count for 4-3-2-1 for every
distinct code in the first table based on year. I will get the year
from the user-end(Access). I will then create my report based on the
info in the new table. Here's what I've tried so far (only for 1st
column):

CREATE PROCEDURE comptabilisationDYN
@.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
AS

DECLARE @.SQLStatement varchar(8000)
DECLARE @.TotalNum4 int
DECLARE @.TotalNum3 int
DECLARE @.TotalNum2 int
DECLARE @.TotalNum1 int

SELECT SQLStatement = 'SELECT COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

INSERT INTO Comptabilisation(Total4) VALUES (@.TotalNum4)
GOPatrik (patrik.maheux@.umontreal.ca) writes:
> CREATE PROCEDURE comptabilisationDYN
> @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> AS
> DECLARE @.SQLStatement varchar(8000)
> DECLARE @.TotalNum4 int
> DECLARE @.TotalNum3 int
> DECLARE @.TotalNum2 int
> DECLARE @.TotalNum1 int
> SELECT SQLStatement = 'SELECT COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
> OUTPUT

You need:

SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

You also need to add @.year to the parameter list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank You, but i have tried your code before and again and it still
doesn't work. I call the procedure and it seems to work but it doesn't
write anything in the table. It doesn't recognize the ouput variable in
the INSERT line.

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > CREATE PROCEDURE comptabilisationDYN
> > @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> > AS
> > DECLARE @.SQLStatement varchar(8000)
> > DECLARE @.TotalNum4 int
> > DECLARE @.TotalNum3 int
> > DECLARE @.TotalNum2 int
> > DECLARE @.TotalNum1 int
> > SELECT SQLStatement = 'SELECT COUNT(*) FROM
> > dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> > EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> > OUTPUT
> You need:
> SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> OUTPUT
> You also need to add @.year to the parameter list.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Your personal narratives and pseudo-code are useless.|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Thank You, but i have tried your code before and again and it still
> doesn't work. I call the procedure and it seems to work but it doesn't
> write anything in the table. It doesn't recognize the ouput variable in
> the INSERT line.

Could you post the exact code you have now. Looking back on your post,
I see now that there will be a syntax error from the dynamic SQL.

Judging from the code you posted, you should always get a row insered,
even if only a NULL value.

I assume that "seems to work" does not mean that you don't get any
error messages. But maybe you should try running the procedure from
Query Analyzer, in case you have poor error handling in your Access
code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thank You, after reading Mr.Sommarskog article more profoundly again
over the week-end, I pick-up my mistake. I wasn't using the parameter
list, now it works fine. Here's my final code.

CREATE PROCEDURE ComptabilisationDYN
@.numsaisie int,
@.code int,
@.colonne varchar(3)
AS

DECLARE @.TotalNum4 int
DECLARE @.SQL1 nvarchar(1000)
DECLARE @.paramlist nvarchar(1000)

SELECT @.SQL1 ='SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE ' +@.colonne + '=''4'''

SELECT @.paramlist = '@.TotalNum4 int OUTPUT'
EXEC sp_executesql @.SQL1, @.paramlist, @.TotalNum4 OUTPUT

INSERT INTO comptabilisation(Num_Saisie, code, Total4) VALUES
(@.numsaisie,@.code, @.TotalNum4)
GO|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp