I'm having a SORTING problem with data from 4 temp tables

Question

I am working with SQL Server 2016 and needing to create a FLAT FILE output for a vendor. I have 4 temp tables that have different number of columns in each and therefore I cannot UNION these tables. I've used the ROW_NUMBER function each temp table and I was hoping to use that in order to CREATE a NEW ROW NUMBER for each RECORD type that corresponds to the POLICY NUMBER of each table in a specific order. I am struggling to get the record type order and matching POLICY NUMBER for each transaction. Any help/direction would be greatly appreciated. Thank you.

Here is the SQL code that I tried but didn't work:

SELECT RIGHT(REPLICATE ('0' , 9) + CAST(ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT)+1 as varchar(10)), 9) as [Record Number] 
    ,polrf.* 
    ,propf.* 
    ,prp1f.* 
    ,subjf.* 
--INTO #tempFinal
FROM #tempPOLR_FINAL polrf
LEFT JOIN #tempPROP_FINAL propf
ON polrf.[Policy Number] = propf.[Policy Number]
LEFT JOIN #tempPRP1_FINAL prp1f
ON propf.[Policy Number] = prp1f.[Policy Number]
AND propf.[VIN] = prp1f.[VIN]
LEFT JOIN #tempSUBJ_FINAL subjf
ON prp1f.[Policy Number] = subjf.[Policy Number]
AND prp1f.[VIN] = subjf.[VIN]
ORDER BY polrf.[Policy Number], polrf.[Record Type], propf.[Policy Number], propf.[Record Type] asc, prp1f.[Policy Number], prp1f.[Record Type] asc, subjf.[Policy Number], subjf.[Record Type]

The following is how my output should look:

From the image, I am needing to CREATE a NEW record/row for the policy number and record type that matches on [Policy Number]: POLR, PROP, PRP1, SUBJ

Here are the SELECT statements of my created temp tables:

SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date] 
    ,[NAIC Code], [Company Name], [Risk Class], [Policy Class], [Policy Inception Date], [Policy Term Begin Date]
    ,[Policy Term End Date], [Policy Term Cancellation Date], [Mailing Address: Type], [Line 1], [Line 2], [City]
    ,[State], [Postal Code], [Country], [Home Phone Area Code], [Home Phone Number], [Cell Phone Area Code]
    ,[Cell Phone Number], [Business Phone Area Code], [Business Phone Number], [Business Phone Extension]
    ,[Transaction Code], [Policy Premium], [Premium Payment Plan], [Premium Method of Payment], [Transaction Processing Date]
    ,[Producer's Number], [Producer's FEIN], [Producer's Name], [Producer's Address: Type], [Producer's Address: Line 1]
    ,[Producer's Address: Line 2], [Producer's Address: City], [Producer's Address: State], [Producer's Address: Postal Code]
    ,[Producer's Address: Country Code], [Producer's Phone: Area Code], [Producer's Phone: Number],[Producer's Email Address]
    ,[Client’s Unique Transaction ID], [Customer Alternate Policy Number], [Customer Group ID]
    ,RN = ROW_NUMBER() OVER (PARTITION BY [Policy Number] ORDER BY [Policy Number])
INTO #tempPOLR_FINAL
FROM #tempPOLR -- SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#tempPOLR'); 49 columns
ORDER BY [Policy Number] asc;

-- SELECT * FROM #tempPOLR_FINAL ORDER BY [Policy Number] asc, [RN] asc;     

SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date] 
    ,[Sequence Number],[VIN],[Make], [Model], [Model Year], [Property Address Type]
    ,[Property Address: Line 1], [Property Address: Line 2], [Property Address: City] 
    ,[Property Address: State], [Property Address: Postal Code], [Property Address: Country] 
    ,[Business Use], [Lien Holder Name], [Lien Holder Address: Type]
    ,[Lien Holder Address: Line 1], [Lien Holder Address: Line 2], [Lien Holder Address: City], [Lien Holder Address: State], [Lien Holder Address: Postal Code]
    ,[Lien Holder Address: Country Code], [Lien Holder Tax I.D. Number], [Lien Holder Loan Number], [Anti-theft Device], [ISO Automobile Class Code]
    ,[ISO Main Policy Form #], [Construction Code], [Year Built], [Public Protection Plan], [Building Code Effectiveness Grade], [Territory Code]
    ,[Square Footage], [Coverage Type, 1], [Coverage Type, 2], [Coverage Type, 3], [Coverage Type, 4], [Coverage Type, 5], [Coverage Type, 6], [Coverage Type, 7]
    ,[Coverage Type, 8], [Coverage Type, 9], [Coverage Type, 10], [Replaced Vehicle VIN], [Replaced Vehicle Effective Date], [Vehicle Inception Date]
    ,[License Plate Matching] 
    ,RN = ROW_NUMBER() OVER (PARTITION BY [Policy Number] ORDER BY [Policy Number], [VIN])
INTO #tempPROP_FINAL
FROM #tempPROP -- SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#tempPROP'); 83 columns
ORDER BY [Policy Number] asc;

-- SELECT * FROM #tempPROP_FINAL ORDER BY [Policy Number] asc, [RN] asc;     

SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date] 
    ,[VIN],[Vehicle Registration State], [Leased Vehicle], [Certificate Date], [Prior Policy (if changed)], [Registration Address: Type]
    ,[Registration Address: Line 1], [Registration Address: Line 2], [Registration Address: City], [Registration Address: State]
    ,[Registration Address: Postal Code], [Registration Address: Country],[Fleet Count], [Out-of-State Insurance], [Gray Flag]
    ,[Filler], [Vehicle Garage Town], [Premium town], [Vehicle License Plate Type], [Vehicle License Plate Color], [Unpaid Premium]
    ,[Binder Number], [Interested Party 1: Type], [Interested Party 1: Name], [Interested Party 1 Address: Type], [Interested Party 1 Address: Line 1]
    ,[Interested Party 1 Address: Line 2], [Interested Party 1 Address: City], [Interested Party 1 Address: State], [Interested Party 1 Address: Postal Code]
    ,[Interested Party 1 Address: Country Code], [Interested Party 1: Tax I.D. Number], [Interested Party 1: Loan Number]
    ,[Interested Party 2: Type], [Interested Party 2: Name], [Interested Party 2 Address: Type], [Interested Party 2 Address: Line 1]
    ,[Interested Party 2 Address: Line 2], [Interested Party 2 Address: City], [Interested Party 2 Address: State], [Interested Party 2 Address: Postal Code]
    ,[Interested Party 2 Address: Country Code], [Interested Party 2: Tax I.D. Number], [Interested Party 2: Loan Number]
    ,[Interested Party 3: Type], [Interested Party 3: Name], [Interested Party 3 Address: Type], [Interested Party 3 Address: Line 1]
    ,[Interested Party 3 Address: Line 2], [Interested Party 3 Address: City], [Interested Party 3 Address: State], [Interested Party 3 Address: Postal Code]
    ,[Interested Party 3 Address: Country Code], [Interested Party 3: Tax I.D. Number], [Interested Party 3: Loan Number]
    ,[Vehicle Registrant Entity Type], [Vehicle Registrant Driver's License Number], [Vehicle Registrant Driver's License State], [Vehicle Registrant SSN]
    ,[Vehicle Registrant Federal ID], [Vehicle License Plate Number], [Vehicle Registration Number], [Vehicle Premium State], [Vehicle NAIC State]
    ,[Invalid VIN Flag], [Filler (Reserved for Future Use)]
    ,RN = ROW_NUMBER() OVER (PARTITION BY [Policy Number] ORDER BY [Policy Number], [VIN])
INTO #tempPRP1_FINAL
FROM #tempPRP1 -- SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#tempPRP1'); 72 columns
ORDER BY [Policy Number] asc;

-- SELECT * FROM #tempPRP1_FINAL ORDER BY [Policy Number] asc, [RN] asc;     

SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date] 
    ,[Relationship to Policyholder], [Last Name], [First Name], [Middle Initial/Name], [Name Suffix], [Date of Birth]
    ,[Social Security Number], [Sex], [Driver's License Number], [Driver's License State], [Driver Relationship to Insured]
    ,[Name: Type], [Full Name], [Federal ID], [State ID]
    ,[VIN], [Marital Status], [Subject Entity Type], [Filler (Reserved for Future Use)]
    ,RN = ROW_NUMBER() OVER (PARTITION BY [Policy Number] ORDER BY [Policy Number], [VIN])
INTO #tempSUBJ_FINAL
FROM #tempSUBJ -- SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#tempSUBJ'); 25 columns
ORDER BY [Policy Number] asc;

If you want the results ordered, you *have* to specify all the criteria in the `ORDER BY` clause. There's no implicit ordering. If you want ordering by row number you'll have to include that in the ORDER BY clause, eg :`ORDER BY [Record Number] ...` — Panagiotis Kanavos, Jan 09 '19 at 14:25
Aside: "I have 4 temp tables that have different number of columns in each and therefore I cannot join these tables." Number of columns isn't an issue for `join`, but is problematic for `union`. — HABO, Jan 09 '19 at 14:59

LukStorms · Accepted Answer · 2019-01-10T09:02:14.393

1

If it's used for an INSERT, and there's no IDENTITY column?

Then there's no reason to have an ORDER BY in the final query.

But to generate [Record Number], you can use that order in the ROW_NUMBER instead.

And you might be looking for a UNION ALL

SELECT 
 FORMAT(row_number() over(order by [POLICY NUMBER], [RECORD TYPE])+ 1,'000000000') as [RECORD NO],
 q.*
INTO #tempFinal
FROM 
(
  SELECT 
   [Record Type] AS [RECORD TYPE], 
   [AM Best Number] AS [AMBEST], 
   [Policy Number] AS [POLICY NUMBER], 
   [Line of Business (LOB)] AS [LOB], 
   [Transaction Effective Date] AS [TRANS EFF DT]
  FROM #tempPOLR_FINAL polrf

  UNION ALL

  SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date]
  FROM #tempPROP_FINAL propf

  UNION ALL

  SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date]
  FROM #tempPRP1_FINAL prp1f

  UNION ALL

  SELECT [Record Type], [AM Best Number], [Policy Number], [Line of Business (LOB)], [Transaction Effective Date]
  FROM #tempSUBJ_FINAL subjf
) q;

edited Jan 10 '19 at 09:02

answered Jan 09 '19 at 14:41

LukStorms

28,916
5
31
45

Hi LukStorms, thanks you for the edited portion. I did have another question though if you don't mind. The 5 columns you have listed are in all 4 temp files but I'm needing all the columns from each temp table. Any suggestions? In addition to creating the record number the problem I'm having is getting all the columns listed after each record type. This is how the vendor is wanting this information. Again any help is appreciated. Thank you. – Melinda Jan 09 '19 at 20:46
1

When using UNION then the amount and types of columns must be the same in each unioned query. And what often happens is that a NULL is used at the place in a query that doesn't have a certain field in another. But you have so many different fields in those temp tables. That's gonna be a lot of NULL placeholders. Or would it be fine to concatinate the other fields together in 1 large string? – LukStorms Jan 09 '19 at 22:53
@Melinda An example of UNION with different columns [here](https://stackoverflow.com/q/40378914/4003419) – LukStorms Jan 10 '19 at 09:33
Thanks LukStorms, I did find out from the vendor that I can CONCATENATE all other fields to 1 column for the fixed width file I'm needing to create from this data. I'm trying that this morning. Thanks again for your time and your help. – Melinda Jan 10 '19 at 12:50

I'm having a SORTING problem with data from 4 temp tables

1 Answers1