Creating a variable size cursor

[TallGuyinIT]

I have been asked to create a grid report which would list the SKUs in production across the top (columns) and the raw material items listed down the side (rows). The grid would contain the quantity needed of each raw material to manufacture the SKU.

My problem is that the column count will be different everytime the report is run depending upon the number of work orders in process. Is there a way to create an initial cursor and then add columns as needed?

I could create an array for the data, but I would have the same issue when declaring the array. I would either need to declare an array larger than what would ever be needed or quickly run through the database tables to calculate how large of an array would be needed.

Thanks,

 

[Mark826]

The ALTER TABLE command can be used for cursors to add columns.

Sample:
CREATE CURSOR test (cKey c(10))
ALTER TABLE test ADD COLUMN col1 c(11)

-Mark

 

[StewartUK]

Can you use a SQL select statement each time to create the cursor? Then you don't need to worry about the number of columns.

As far as the array goes, you can create the array of any size and then redimension it as necessary.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

If you have a flat table for example..

WorkOrderNo RawMaterial Qty

and then you want to create a report something like..

W/Order-1 w/Order-2 w/order3
RawMaterial1 Qty Qty Qty
RawMaterial2 Qty Qty Qty
RawMaterial3 Qty Qty Qty
..

What yo need to do is

SELECT WorkOrderNo RawMaterial SUM(Qty) AS qty FROM ..... ;
INTO CURSOR myCursor GROUP BY 1,2,3

Then do a crossTab to get your results table in the format shown above.

You can look here for a CrossTab utility written by me and download / use it.

http://www.winnersoft.coolfreepages.com/vfp/gsxtab1.htm

Note to use SET ENGINEBEHAVIOR 70 for that if needed.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/