运行以下代码时出错

问题描述

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.

推荐答案

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.

这篇关于运行以下代码时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！