0

I wrote the below query to join table Pro_C_C with Pol_C_C (I only picked up 6 fields from Pol_C_C).

Select *
From Pro_C_C x
Join
(Select 
[Policy Number],
LEFT([Primary Policyholder Forename],1) PInitial, [Primary Policyholder Surname] PrimPHSurname,[Primary Policyholder Dob] PrimPHDOB,
LEFT([Secondary Policyholder Forename],1) SInitial, [Secondary Policyholder Surname]SecPHSurname,[Secondary Policyholder Dob] SecPHDOB
From Pol_C_C) y
On x.[Policy Number] = y.[Policy Number]
Order By x.[Policy Number]

Then I wrote the below trying but I am getting the error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Select
[PN],[PT],[IPL1],[IPL2],[IPL3],[IPL4],[IPL5],[IPP],[IPO],[IPBA],[PID-UPRN],[PIS Date],[PIE Date],[PPF],[PPS],[PPD]
From(
Select * From Pro_C_C x
Join
(Select 
[PN],
LEFT([PPF],1) PPF, [PPS],[PPD],
LEFT([SPF],1) SPF, [SPS],[SPD]
From Pol_C_C) y
On x.[PN] = y.[PN]
Order By x.[PolPN]
)A

Anyone got any idea?

Ekim
  • 5
  • 1
  • 1
    What part of the error message do you not understand? It seems really clear. If you want the results in a particular order, then the `ORDER BY` needs to be associated with the *outer* `SELECT`. – Gordon Linoff Jul 24 '19 at 14:35
  • I am just not sure how to fix it, because even if I take the Order By out ofthe formula it is still don't work... – Ekim Jul 24 '19 at 15:39
  • what error do you get when you remove the order by? – cte6 Jul 24 '19 at 17:02
  • See my answer on **[SQL Server Query Error -ORDER BY clause is invalid in views](https://stackoverflow.com/questions/36697511/sql-server-query-error-order-by-clause-is-invalid-in-views/61702593#61702593)**. – Murat Yıldız May 09 '20 at 19:36

0 Answers0