March 25, 2008 at 11:33 am
Trader Sam (3/25/2008)
Ahhh...the key to your solution, Matt, is to build the row string on the INSERT into the temp table, rather than building it when SELECTING from the temp tables.It is amazing how a concept as simple as that can make coding more difficult if you don't catch it.
Necessity is the mother of all invention... It's amazing how simple things are to remember when you have the battle scars to remind you....:)
But - thanks for the feedback nonetheless!!!!
----------------------------------------------------------------------------------
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?
March 25, 2008 at 11:46 am
Thanks for the replies.
I have some questions but they'll have to wait until after a conference call I have. Thanks again.
March 25, 2008 at 11:59 am
Trader Sam... I haven't run profiler yet but I'll try that. The update runs but it just doesn't actually update that row. I mentioned earlier that I did a blanket update and it updated all rows except the ones currently inserted from that cursor. Weird. I figured it had something do to with the transaction not committing till the cursor closed.
As far as not doing this with a cursor I just don't know how that can be done.
I have two procs for getting these details. Each proc calls probably 15 tables total and outputs the data.
s_HeaderDetail @Ordernumber, @Weight out,@Distance out,@Origin out etc...
s_LineItemDetail @TransactionID,@InvoiceNumber out,@Amount out etc....
The intial list that will populate the first cursor will be a view of some sort... maybe orders that are completed within a date range.
How do I take that list and pass it to the procs without using a cursor?
March 25, 2008 at 12:10 pm
AVB - we'd need to start with some better specs. Tell us how the file is to be built: what is in the file header, in the invoice header, what goes into the detail rows, etc...
Since you're saying you have the stuff "pre-processed" into two view, at least start by telling us the column layouts for the two views. We might need what makes up the views as well, but I'm not sure we have enough.
----------------------------------------------------------------------------------
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?
March 25, 2008 at 12:23 pm
Like Matt said, more details will help to provide an accurate solution.
Basically though, you can get the data in sets rather than row by row as Matt outlined in his earlier post.
Something like this...
1) Get all OrderNumbers that need to be processed
2) Get all HeaderData that has Order Numbers from (1)
3) Get all Line Item Data that has corresponds to HeaderData from (2)
You will have to rewrite several pieces and probably some of your existing sub procs, but the outcome will be a much better solution (faster, easier to maintain, less moving parts).
If you have time, give us more details.
If it was easy, everybody would be doing it!;)
March 25, 2008 at 2:15 pm
I'll get you some more details. I have plenty of cursors that operate with the same logic so if I can figure out how to eliminate this one I'm sure I can apply it to the others.
March 26, 2008 at 6:37 am
AVB,
You have many options as to how to construct this. I can't go into too much detail because I don't know your DDL, etc., but I'll give a few options and perhaps you can figure out which works best for your situation. The key is to think in 'SETS' of data rather than single rows.
For example, currently your SP s_HeaderDetail has @Ordernumber as an input param, and returns data for a single 'Order Number'. Why not get the header data for ALL the order numbers, rather than just one.
Modify s_HeaderDetail so that you can send all applicable 'Order Numbers' in together and return data for all of them. Different methods to do this...
1) pass in a comma-delimited string of Order Numbers,
a) parse the string data into a temp table (this can be also done in a user-defined function that returns a table, then use the function in your JOIN in the query)
b)use the Order Numbers in a 'where'....WHERE OrderNumber in (12345, 98765, 56784)
2) pass Order Numbers in using XML, parse data out of XML and use it like 1a or 1b.
3) move the code that gets the Order Numbers to s_HeaderDetail and if that is driven by some input (date, status, customerid, etc), pass that in instead
You can also bring the SQL from s_HeaderDetail into your main proc if that is an option.
The detail records will be handled similarly. The idea is to get all the appropriate data into the temp tables, then select from the temp tables to get your final string. As Matt showed in a previous post, you can build the EDI row fragments as you are INSERTING into the temp tables.
The other thing that hasn't been discussed is handling the 'error' records. You can either filter them out in the initial selects, or go ahead and bring them into your temp tables and then check them for errors and make your INSERTS into your error logs or tables (just make sure those rows don't make it into the final select).
This will take you a bit of time to get fully functional, but the exercise of doing it will be valuable, and you can apply the same ideas to other similar code you have. There have been a lot of posts and articles on this forum and SQL Server Central discussing many of the concepts I have mentioned (as well as on the Internet), so you will have many resources to help you.
If you don't have time to do this now, have you had any luck with your original issue of the updating not working? (sorry this post so long)
If it was easy, everybody would be doing it!;)
March 26, 2008 at 7:08 am
Thanks for the info.
Unfortunately I haven't had any luck with the updating. Maybe it's just a caveat of that type of Cursor. The update is called it just won't affect that paticular set. I don't have to update the status so I'll just do with out it for now.
I understand what you mean by the set based logic. I was thinking more about it and one way is to write a function that builds the strings although I'm not sure what kind of performance overhead it'll incur.
i.e.
Insert into #t_Temp (HeaderRecord,LineItemDetail)
select dbo.fn_HeadRecord(OrderNumber),dbo.fn_LineItemDetail(OrderNumber) from v_OrdersThatNeedProcessing
The headerRecord is easy cause it's one line per order. The LineItemDetail can be multiple lines per order so I'd have to figure that out.
I have to finish this project with the cursor because it needs to be completed soon. However, I've been forever wanting to rewrite my cursors with set based logic and this is a great oppotunity. I'll figure out a way with the suggestions that have been posted and let you know how I did it. Thanks for the help.
March 26, 2008 at 7:41 am
You can still use the stored procs to return the data, I was saying build a function to parse out a comma-delimited string, such as containing multiple Order Numbers.
For example, string param passed into stored proc...
@OrderNumbers varchar(100) --might look like (12345, 98765, 654320
Use a user-defined function that parses string and returns table of values from string in your query
select columns
from SomeTable ST
inner join StringParseFunction(@OrderNumbers) SPF
on ST.OrderNumber = SPF.OrderNumber
where yada, yada, yada
Since StringParseFunction returns a table, it can be joined into the query like above. Inside the function, you would have SQL using string functions to parse the string and insert it into a temp table which is returned at end of function. The reason to use a function here is that you can re-use it in other procs (it is a utility).
See this http://www.sqlservercentral.com/scripts/Miscellaneous/31913/ for example function.
Hope this helps!
[Edit: added link]
If it was easy, everybody would be doing it!;)
March 26, 2008 at 8:55 am
AVB (3/25/2008)
SET @LineItemBody = '"LI' +'","'+ --Record ID
.....
IF isnull(@LineItembody,'') = '' --Line Item Body will be null if an Order Number doesn't have any rows in the transaction tables thus returning no L1 data.
BEGIN
--Update Statment that doesn't work goes here
Not seeing your entire code, not sure if you are doing this, but be sure you are resetting @LineItemBody = '' before the next EDI cursor iteration (main cursor), because if it still contains a non-empty string from a previous iteration, you won't enter this 'if' branch, and your update won't get called.
If it was easy, everybody would be doing it!;)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply