troubleshooting Question

SQL Insert with Union All

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL Server 2008Microsoft SQL Server.NET Programming
4 Comments1 Solution412 ViewsLast Modified:
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
Ephraim Wangoya
Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros