Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Replace the cursor?
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01375460
Message ID:
01375499
Views:
12
You can order result set on PONO and Record type.
BTW, you don't have to create a table variable. You can use the first select as a derived table in the second select
	INSERT INTO @myTable
	SELECT CAST(
		cast('01' AS char(2)) +
		cast(PONO AS char(9)) +
		<snip>
		cast(space(296) AS char(296))
	AS char(424)) AS data
	from PurchaseOrders
	WHERE Batched = 1 and DateBatched = @ProcessDate)

	UNION ALL

	
	SELECT CAST(
		cast('02' AS char(2)) +
		cast(PONO AS char(9)) +
		<snip>
		cast(space(322) AS char(322))
	AS char(424)) AS data
	from PurchaseOrderDetail 
	WHERE PONO IN (SELECT PONo from PurchaseOrders WHERE Batched = 1 and DateBatched = @ProcessDate)

	UNION ALL

	SELECT CAST(
		cast('03' AS char(2)) +
		cast(PONO AS char(9)) +
		<snip>
	AS char(424)) AS data
	from PurchaseOrderComments a
	WHERE PONO IN (SELECT PONo from PurchaseOrders WHERE Batched = 1 and DateBatched = @ProcessDate)

SELECT * from @myTable ORDER BY (SUBSTRING(data, 3, 9), LEFT(data(2)
>
>The following SP gathers Purchase Orders data for processing them into the mainframe system.
>
>Multiple POs could be processed in one upload file. Upload file needs to be in the following order:
>
>record type '01' - line from PO header file (PurchaseOrders)
>record type '02' - all detail lines from PO detail file (PurchaseOrderDetail)
>record type '03' - all comment lines from PO comment file (PurchaseOrderComments)
>
>As you can see from the code below the only reason I used the cursor is to order the records in the above mentioned way.
>Is there a way to accomplish the same without the cursor?
>
>DAniel
>
<snip>
--sb--
Previous
Reply
Map
View

Click here to load this message in the networking platform