Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Replace the cursor?
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
01375460
Message ID:
01375499
Vues:
6
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--
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform