August 28, 2007 at 8:08 am
I wasn't advocating XML being your final product, but it be the replacement for the temp table/working table you're building.
One piece you haven't taken on yet is how to get the data you're putting into this table out of this table into a flat file, formatted the way the product wants it to be, and in the order it wants them in. Remember - just because you insert records into a table in a specific order doesn't mean that they will be extracted in that same order during an export process. If you don't have a way to FORCE them to be in the same order, chances are good that they won't be (remember - this is by default an *unordered* set). For this type of activity, in many ways, using a XML data item to create the data set, and then applying a XSLT transform to that XML on its way out will be easier than possibly having to deal with the formatting oddities trying to "dump" the contents of a table into a specific flat text file, with specific limitations on the format, etc....
Never mind the XML thing though - what's easiest for me might not be for you. I just strongly recommend you make sure you have an easy way to force the staging table to keep your data sorted (so you don't end up with some awful snafu when exporting and the detail records start floating around).
And again - even with your pseudo-code above - you really don't need the cursor. Cursors are nice when necessary, but they slow things down when not absolutely required. If you throw in some fields to keep your data organized (which you won't end up exporting), you will see you don't need the loop (or at least the code you're showing doesn't support that contention).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 28, 2007 at 11:49 am
The link between the hierarchy is the id which has the invoice then the transactions for that invoice. The table structure looks something like this
A B C
Invoice1 25 Invoicedesc
Item1 12.5 ItemDesc
Item2 12.5 ItemDesc
These are linked using two ids one is a systemgenerate Id at the time of insertion and the other is the InvoiceId.
My final SQL is something like this:
SELECT a,b,c ... FROM ARInvoiceBatch WHERE NewRecord = 1 ORDER BY ARId,InvoiceId
The SQL is loaded in a C# console app where I load a dataset into an exportfile object which creates the delimited file and puts it in a public folder for the user community.
I'm not really a big fan of Cursors, but I can't think of any other way to iterate to my new records. Secondly I haven't done these type of imports and such a long time I wanted something quick and dirty normally I would take advantage of Financial System API.
I'm not sure how to post the final code in readable fashion. If there is a more efficient I'll be willing to listen.
I don't want to post the (close to) final version until I can figure out how to post code properly.
Thanks,
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply