Getting the error when i run the below code

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

SQL

DECLARE
         @DivisionId                INT,
         @InsuranceAuthorityID    INT,
         @Fromdate                DATETIME,
         @Todate                    DATETIME

SELECT @DivisionId        = NULL    ,
        @InsuranceAuthorityID     = 39,
		@Fromdate            = '2012-06-01 00:00:00.000'    ,
        @Todate            = NULL

         SELECT @ToDate = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE())) WHERE 
@ToDate IS NULL

             SELECT DISTINCT
                 D.CalculatePremiumId,
                 --c.PersonalID,
                 A.EmployeeId,
                 D.Name AS 'Employee Name',
                 D.RelationShip,
                 REPLACE((CONVERT(VARCHAR(11), A.DateofBirth, 106)),'','-')AS DateOfBirth,
                 B.Description AS 'SumInsured',
                 (NumberOfPersonsInsured) AS 'Beneficiary',
                 D.HighestAge,
                 d.BasicPremium,
                 d.AdditionalMemberLoading    ,
                 d.MembersPremium,
                 d.AdditionalPremiumForExtraCovers,
                 d.TotalPremium,
                 d.NetPremium,
                 d.ServiceTax,
                 d.FinalPremium
             INTO #FinalDataMedicalInsurance
             FROM OAS_Employee_Info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty AS A WITH(NOLOCK)
              LEFT JOIN
                 OAS_PERSONAL_INFO.dbo.tbl_Personal_PersonalMaster AS FD 
WITH(NOLOCK) ON FD.PersonalID = A.PersonalId
                                                                 AND 
ISNULL(fd.activeyn,1) = 1
                                                                 AND 
ISNULL(fd.deletedyn,0) = 0
             LEFT JOIN
  oas_incometax.dbo.tbl_PaySlip_MedicalInsuranceCalculatePremium_Details 
D  WITH(NOLOCK) ON A.EmployeeId =D.EmployeeID
  AND ISNULL(D.ActiveYN,1) = 1
  AND ISNULL(D.DeletedYN,0) = 0
             LEFT JOIN
                 oas_employee_info.dbo.tbl_EmpInfo_familydetails C 
  WITH(NOLOCK) ON C.PersonalID = A.PersonalId
  AND ISNULL(C.ActiveYN,1) =1
  AND ISNULL(C.DeletedYN,0) = 0
             LEFT JOIN OAS_Admin.dbo.tbl_Common_LookUp_Details B 
WITH(NOLOCK)    ON B.DetailId = D.SumInsuredId
  AND ISNULL(C.ActiveYN,1) =1
  AND ISNULL(C.DeletedYN,0) = 0

             WHERE --A.ActiveYN = 1 AND
              (@DivisionId IS NULL OR A.DivisionId = @DivisionId)
             AND D.InsuranceAuthorityId =@InsuranceAuthorityID
             AND (D.FromDate = '2012-06-01 00:00:00.000')
             --AND (D.ToDate BETWEEN @Fromdate AND @Todate)


                 UNION ALL
         SELECT DISTINCT
             MIC.CalculatePremiumId,
             EJ.EmployeeId,
             MCP.Name,
             MCP.RelationShip,
             MCP.DateofBirth,
             '','','','','','','','','','',''

         FROM
  tbl_PaySlip_MedicalInsuranceCalculatePremiumChild_Details AS MCP 
WITH(NOLOCK)
         INNER JOIN
             dbo.tbl_PaySlip_MedicalInsuranceCalculatePremium_Details AS 
MIC  WITH(NOLOCK) ON MIC.CalculatePremiumId = MCP.CalculatePremiumId
      AND ISNULL(MIC.ActiveYN,1) = 1
      AND ISNULL(MIC.DeletedYN,0) = 0
         INNER JOIN
             OAS_Employee_Info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty AS EJ 
WITH(NOLOCK) ON EJ.EmployeeId = MCP.EmployeeId

         WHERE
             MIC.InsuranceAuthorityId = @InsuranceAuthorityId
             --AND (@EmployeeId IS NULL OR @EmployeeId = MCP.EmployeeId)
             AND ISNULL(MCP.ActiveYN,1) = 1
             AND ISNULL(MCP.DeletedYN,0) = 0
             AND (@DivisionId IS NULL OR EJ.DivisionId = @DivisionId)
             AND (MIC.FromDate = '2012-06-01 00:00:00.000')
             --AND (MIC.ToDate BETWEEN @Fromdate AND @Todate)
         ORDER BY  D.CalculatePremiumId DESC

         SELECT                 CalculatePremiumId,    EmployeeId, 
  [Employee Name],   RelationShip,    DateOfBirth    ,SumInsured 
  ,Beneficiary    ,HighestAge    ,BasicPremium, 
  AdditionalMemberLoading,    MembersPremium, 
  AdditionalPremiumForExtraCovers,    TotalPremium,    NetPremium, 
  ServiceTax    ,FinalPremium
  FROM #FinalDataMedicalInsurance WHERE RelationShip = 'Self'
         UNION ALL
         SELECT                 CalculatePremiumId,    EmployeeId, 
  [Employee Name],   RelationShip  ,    DateOfBirth    ,SumInsured 
  ,Beneficiary    ,HighestAge    ,BasicPremium, 
  AdditionalMemberLoading,    MembersPremium, 
  AdditionalPremiumForExtraCovers,    TotalPremium,    NetPremium, 
  ServiceTax    ,FinalPremium
  FROM #FinalDataMedicalInsurance WHERE RelationShip <> 'Self'
  
    ORDER BY EmployeeId desc
			,case when [Relationship] = 'self' then 1 else 2 end

Error: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Posted 24-May-13 0:16am

Member 9848727

Updated 24-May-13 0:20am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ClsssOne · Accepted Answer · 2013-05-24T01:19:00

XML

Hi

 This error is coming due to your last UNION statement. you are near to your answer. Now u need to "unionised"  the query like

  SELECT *
FROM
(
    SELECT A, B, C
    FROM Table1
    UNION
    SELECT D, E, F
    FROM Table2
) Table4
ORDER BY A, case when a = 1 then 1 else 2

I am giving  a example :

SELECT * FROM (SELECT USER_PK, FIRST_NAME, FK_SECURITY_QUESTION FROM [USERS]  WHERE FK_SECURITY_QUESTION = 3

UNION

SELECT USER_PK, FIRST_NAME, FK_SECURITY_QUESTION FROM [USERS] WHERE FK_SECURITY_QUESTION <> 3) USERS

ORDER BY USER_PK DESC, CASE  WHEN FK_SECURITY_QUESTION = 3  THEN 1 ELSE 2 END

your last select statement will be like this

SELECT * FROM ( SELECT CalculatePremiumId, EmployeeId,
  FROM #FinalDataMedicalInsurance WHERE RelationShip = 'Self'
  UNION ALL
  SELECT CalculatePremiumId,  EmployeeId,
  FROM #FinalDataMedicalInsurance WHERE RelationShip <> 'Self') #FinalDataMedicalInsurance
    ORDER BY EmployeeId desc
            ,case when [Relationship] = 'self' then 1 else 2 end


let me know if u have any problem .

Thanks
Bimal.