Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Insert with Union All

My attached code works fine with one annoying exception

The data is not going into the table in the same order that my union all's are written.

No matter what I do...it is inserted with an ascii sort on the afsSourceDetail column

So...
Instead of it going in like this...which I absolutely need
P01-REG
P01-FED
P01-St
P01-CITY


It goes in like this.
P01-CITY
P01-FED
P01-REG
P01-ST

Any suggestions?
ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )  
AS 
--Get afsSource for return
Begin
	Declare @afsSource nvarchar(20)
	Set @afsSource = (Select afsSource from proc_cfa.dbo.P_Availableforsale where afsid = @afsid)
End

--Add taxes to Available for sale
Begin 
	--Get Summary Data  
   ;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsAmount, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction) 
   --from 
	AS (  Select round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2) [P_Fed],   
				 round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2) [P_St],  
				 round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2) [P_City],                 
				x.afsAmount	- (round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2))
							- (round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2)) 
							- (round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2)) [P_R1],  
				 x.afsAmount,  
				 x.afsSource,  
				 x.afsSourceDetail,  
				 x.afsDate,  
				 x.afsStatus,  
				 x.afsTransaction  
		  From proc_lt.dbo.p_deals p  
				Join	(Select top 1 SUBSTRING(afsSource,4,4) DealID, * 
						from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
						order by afsSource) x  
				on p.DealID = x.DealID  
	   ) 
   
   --use CTE Data and Insert all rows with one insert/ where each insert afsamount > 0 
	INSERT INTO PROC_CFA.dbo.P_AvailableForSale  
						   (afsSource  
						   ,afsSourceDetail  
						   ,afsDate  
						   ,afsAmount  
						   ,afsStatus  
						   ,afsTransaction) 
	SELECT afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
	FROM taxesCTE  where P_R1 > 0
	UNION
	SELECT afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
	FROM taxesCTE where P_Fed > 0 
	UNION
	SELECT afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
	FROM taxesCTE  where P_St > 0  
	UNION 
	SELECT afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
	FROM taxesCTE where P_City > 0 
    --Remove original afsid   
	Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid  
 
End 

Select @afsSource as afsSource

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or you can assign a value and use it for sorting

      SELECT 1 ROWNO, afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction  
      FROM taxesCTE  where P_R1 > 0
      UNION ALL
      SELECT 2 ROWNO, afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction  
      FROM taxesCTE where P_Fed > 0
      UNION ALL
      SELECT 3 ROWNO, afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction  
      FROM taxesCTE  where P_St > 0  
      UNION ALL
      SELECT 4 ROWNO, afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction  
      FROM taxesCTE where P_City > 0
Why does it matter?  When you access the data, you can order it bey whatever you please.  (You might want to try executing the SELECT statement in a query tool and looking at the order in which the data appears . . . it just may match the rder that the data is being put into the table. ;-)  On the other hand, have you tried adding an ORDER BY to force the issue?

You do realize that using UNION does not mean Union ALL, right?  UNION will cause the data to be sorted so that duplicates can be identified and removed.
Avatar of Larry Brister

ASKER

Excellent...thanks