We help IT Professionals succeed at work.
Get Started

SQL Insert with Union All

410 Views
Last Modified: 2012-05-11
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

Comment
Watch Question
Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE