April 27, 2009 at 9:33 pm
All,
I'm going nuts with this. I've been tasked with extracting the logic from a VB6 program and rewriting it using stored procedures. Obviously, the logic is complex and the program creates several connections to multiple databases. The Challenge......I need to retrieve data from many sources and return a single table (rowset) to a crystal report (CR2008).
Given the nature of the program logic, it is clear that I will need several cursors in my stored procedure(s). However, after the first cursor, there is no way that I know of to reset the value of @@FETCH_STATUS. I've tried building a cursor in a separate stored procedure and calling it, but that is the 'same connection' so the status remains -1. Declaring a cursor and performing a FETCH without an INTO will reset the value to 0, but it will also return a row.....and I need a single rowset.
Am I having trouble seeing the forest through the trees, or am I going about this all wrong? Thanks for your help,
Jim
April 27, 2009 at 10:08 pm
First, you probably don't need a cursor. Just based on your original post, however, there isn't much we can do to help.
First, I'd like to suggest that you read the first article that I have referenced below in my signature block. It will provide you with guidelines on how to post DDL (the CREATE TABLE statements), sample data (as INSERT UNION statements), and the code you may currently have attempted.
I also recommend reading this blog The Flip Side, as it will give you some insight about why we sometimes ask for the things we do.
In addition to the sample data, you should also post the expected results of the routine based on the sample data. You don't need a lot of data, just enough so that we can test any code we write and have something to check the results againat.
You should also post the logic of the VB code you are trying to port to SQL. That will also help us in trying to determine what needs to be accomplished.
April 28, 2009 at 3:38 pm
[font="Verdana"]So what is the nature of the logic such that it requires cursors? I think the more you can tell us about what you actually need the stored procedure to do, the more options we can give you. You may not need cursors at all.[/font]
April 29, 2009 at 9:10 am
I had something similar a time or two not too long ago. What I ended up doing was getting the whole thing over into SSIS and executing each piece by itself. Not the most efficient way to do it, but it helped with being able to visualize someone else's logic.
April 29, 2009 at 9:34 am
Sorry for the delay in responding. I am working on a proprietary MES system, and I need to report on production. The challenge is that the MES system consists of 3 data tables, Header, Batch and Phase of batch. Header and Batch have 1 UID, and Phase and Batch have a second UID (there is no way to combine Header and Phase without including Batch). Part of the report spec calls for flagging any value that is out-of-tolerance. Tolerances are stored in a separate database - Wonderware runtime if anyone is familiar with that.
After sleeping on, I think I can do what I need to do using pivots and joins quite liberally with a single exception....in the process of working with the tables, I found a bug in the MES system. Here is a small example of the first columns of the table:
HeaderID StartTime EndTime RecipeName
------------------------------------ ----------------------- ----------------------- -------------------
713B2BDF-40F2-4DE8-82AF-170AB6EC8DCD 2009-03-26 05:54:42.000 2009-03-26 06:07:12.000 90102 Fat Free CM
E6C5E791-4F2A-4DEA-8D1F-0A5750A60D74 2009-03-26 06:01:40.000 2009-03-26 06:32:59.000 90102 Fat Free CM
9DCDB307-CFFB-4340-A296-DC477D81F93B 2009-03-26 06:26:40.000 2009-03-26 06:39:00.000 90102 Fat Free CM
778B4995-382A-46AD-AE99-2E00D1313410 2009-03-26 06:33:17.000 2009-03-26 07:04:28.000 90101 Regular CM 10
ECE31E64-E0E3-4683-B579-4A2E31F6D79B 2009-03-26 07:03:07.000 2009-03-26 07:17:43.000 90101 Regular CM 10
7E20BA63-D612-428D-9EA7-40AB6DA0775B 2009-03-26 07:05:01.000 2009-03-26 07:36:48.000 90101 Regular CM 10
1A5C616D-7A2E-4C76-9979-BD182D2128FE 2009-03-26 07:38:46.000 2009-03-26 07:59:27.000 90101 Regular CM 10
7014CD63-96FB-4975-A512-457A55E1A848 2009-03-26 07:38:47.000 2009-03-26 08:12:12.000 90101 Regular CM 10
BAC3B4B0-5AEC-4EA3-BAA4-AF65A594D90F 2009-03-26 08:06:14.000 2009-03-26 08:23:56.000 90101 Regular CM 7%
9D590F93-F79A-4247-93ED-F4CD8CEC445B 2009-03-26 08:12:51.000 2009-03-26 08:41:07.000 90101 Regular CM 7%
The problem is with the RecipeName Column. On occasion, due to a timing error on the factory floor, the last step of one recipe gets the recipe of the next step and it is reported incorrectly. HeaderID is unique, so I cannot key on that. There is a 'UNIT' column, and it can be MIXER or HOPPER. Knowing that the last step of every batch must be a MIXER gives me something to key....If the UNIT is MIXER but the RecipeName has changed next use the previous recipe for this row. In order to execute that logic, I need to know the value from the previous row at all times, which as far as I know can only be done with a cursor.
I wish I could format this a little better! I'm not sure how, but I hope I got the point across. Sorry for the lengthy explaination, and thanks for your help,
Jim
April 29, 2009 at 10:13 am
I'd like to see a little more, including the DDL for the tables, some sample data for the tables (in a readily consumable format, not like how you posted the sample above), the processing requirements, and expected results based on the sample data.
I am getting the start of an idea on how to attack this problem, but will need the above to do more.
For help with this request, please read the first article referenced below in my signature block.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply