ADO Error fejl i når jeg forsøger mig med UNION ALL

Jeg skal lige huske og skrive at det den SP det drejer sig om er noget i stil med:

select name
from provider
where date > getdate()

union all

select name
from provider
where state = az

det er samme info, men forskellige where statements

kan ikke umiddelbart se noget galt med din query udover du bryder navnestandard. Hvis du vil bruge en kolonne der hedder name skal din query hedde:

select [name]from provider etc..

har du kørt den i query analyzer og se om den kører der ?

det er bare en eksempel, min query er MEGET større, derfor skrev jeg den lille der. Men jeg kan ikek engang gemme, så kommer den med fejlmeddelelsen. Og nej, jeg har ikke kørt den gennem query analyzer. Faktisk så er det første gang jeg beskæftiger mig med UNION så jeg tog bare en kopi af den øverste query i den tro at hvis de er ens så skal det nok køre. Jeg tester lige i analyzer.

det samme fejl får jeg også i analyzer :(

Den siger jo faktisk at du har en ORDER BY med et kolonnenavn der ikke findes i selectlisten. Passer det ikke?

jeps, prøv at smide hele query over så vi kan se den i fuld længde. Kan ikke rigtigt hjælpe mere før vi ser den.

oki doki... her kommer den :)

/*   
    Created:    Haris Kamenica    03-11-2005           
*/

ALTER PROCEDURE dbo.SinglePurchaseDeal_GetSPDListPassTerminationDateFromProviderManagerID
(
    @ID int
)

AS

DECLARE @ServicePurchaseStartDates Table( StartDate datetime, SerialNumber nvarchar(18))
DECLARE @ServicePurchaseEndDates Table( EndDate datetime, SerialNumber nvarchar(18))


/*select earliest startdate from the bought service purchases. This is used as creation date for first ProgresionReport*/
INSERT INTO @ServicePurchaseStartDates
SELECT
    MIN(SP.StartDate),
    SP.SerialNumber
FROM   
    SinglePurchaseDeal SPD INNER JOIN
    ServicePurchase SP ON SPD.SerialNumber = SP.SerialNumber INNER JOIN
    SinglePurchaseDealStatus SPDS ON SPDS.SerialNumber = SPD.SerialNumber INNER JOIN
    ProviderManager PM ON SPD.ProviderID = PM.ProviderID AND PM.ProviderManagerID = @ID     
   
GROUP BY
    SP.SerialNumber
ORDER BY
    MIN(SP.StartDate) ASC     

/*select latest enddate from the bought service purchases. This is used to check if active SPD should have been terminated */
INSERT INTO @ServicePurchaseEndDates
SELECT
    MAX(SP.EndDate),
    SP.SerialNumber
FROM   
    SinglePurchaseDeal SPD INNER JOIN
    ServicePurchase SP ON SPD.SerialNumber = SP.SerialNumber INNER JOIN
    SinglePurchaseDealStatus SPDS ON SPDS.SerialNumber = SPD.SerialNumber INNER JOIN
    ProviderManager PM ON SPD.ProviderID = PM.ProviderID AND PM.ProviderManagerID = @ID 
   
GROUP BY
    SP.SerialNumber
ORDER BY
    MAX(SP.EndDate) DESC


    SELECT Distinct
        SPD.SerialNumber,           
        SPD.CitizenID,       
        SPDS.Status,
        SPDS.ApproveDate3,               
        CD.CitizenName,
        CD.CitizenSSN,       
    /* finds the last date a progressionreport was filled - if none the date of earlist servicepurchase startdate */
    ISNULL((
        SELECT TOP 1   
            CreateDate
        FROM       
            dbo.ProgressionReport
        WHERE   
            (SerialNumber = SPD.SerialNumber)
        ORDER BY
            CreateDate DESC
    ),
   
    (SELECT top 1 S.StartDate
    from @ServicePurchaseStartDates S
    where S.SerialNumber = spd.serialNumber)
   
    ) AS LastDate,
    /* Is the last report locked? */
    ISNULL((
        SELECT TOP 1   
            Locked
        FROM       
            dbo.ProgressionReport
        WHERE   
            (SerialNumber = SPD.SerialNumber)
        ORDER BY
            CreateDate DESC
    ),0) as Locked,
    /* number of days until next report is required */
    DATEDIFF(dd,ISNULL((
                    SELECT TOP 1   
                        CreateDate
                    FROM       
                        dbo.ProgressionReport
                    WHERE   
                        (SerialNumber = SPD.SerialNumber)
                    ORDER BY
                        CreateDate DESC
                        ),SPDS.ApproveDate3), GETDATE()) - SPR.ReportInterval as DueDays,
/* the date the next report has to be filled */
    DATEADD(dd,SPR.ReportInterval,ISNULL((
                                        SELECT TOP 1   
                                            CreateDate
                                        FROM       
                                            dbo.ProgressionReport
                                        WHERE   
                                            (SerialNumber = spd.SerialNumber)
                                        ORDER BY CreateDate DESC
                                            ),
                                           
                                            (SELECT top 1 S.StartDate
                                            from @ServicePurchaseStartDates S
                                            where S.SerialNumber = spd.serialNumber)                                           
                                           
                                            )) As DueDate,
        SPED.EndDate AS LastServicePurchaseDate
   

    FROM
        SinglePurchaseDeal SPD INNER JOIN
        SinglePurchaseDealStatus SPDS ON SPD.SerialNumber = SPDS.SerialNumber INNER JOIN
        CitizenData CD ON SPD.CitizenID = CD.CitizenID INNER JOIN
        ProviderManager PM ON SPD.ProviderID = PM.ProviderID AND PM.ProviderManagerID = @ID LEFT OUTER JOIN
        UserInfo UI ON SPD.ResponsibleProviderEmployeeID = UI.UserID LEFT OUTER JOIN
        ProviderEmployee PE ON PE.ProviderEmployeeID = SPD.ResponsibleProviderEmployeeID INNER JOIN
        SinglePurchaseReporting SPR ON SPR.SinglePurchaseReportMethodID = SPD.SinglePurchaseReportMethodID INNER JOIN
        ServicePurchase SP ON SPD.SerialNumber = SP.SerialNumber INNER JOIN
        KOMMAProject KP ON SP.KOMMAProjectID = KP.KOMMAProjectID INNER JOIN
        ProviderProjects PP ON KP.KOMMAProjectID = PP.KOMMAProjectID INNER JOIN
        Provider P ON PP.ProviderID = P.ProviderID INNER JOIN
        @ServicePurchaseEndDates SPED ON SPED.SerialNumber = SPD.SerialNumber
   
    WHERE
        SPDS.Status = 3 AND SPED.EndDate < GETDATE()
   
    UNION ALL

   
SELECT Distinct
        SPD.SerialNumber,           
        SPD.CitizenID,       
        SPDS.Status,
        SPDS.ApproveDate3,
        SPDS.ClosedDate,       
        CD.CitizenName,
        CD.CitizenSSN,       
    /* finds the last date a progressionreport was filled - if none the date of earlist servicepurchase startdate */
    ISNULL((
        SELECT TOP 1   
            CreateDate
        FROM       
            dbo.ProgressionReport
        WHERE   
            (SerialNumber = SPD.SerialNumber)
        ORDER BY
            CreateDate DESC
    ),
   
    (SELECT top 1 S.StartDate
    from @ServicePurchaseStartDates S
    where S.SerialNumber = spd.serialNumber)
   
    ) AS LastDate,
    /* Is the last report locked? */
    ISNULL((
        SELECT TOP 1   
            Locked
        FROM       
            dbo.ProgressionReport
        WHERE   
            (SerialNumber = SPD.SerialNumber)
        ORDER BY
            CreateDate DESC
    ),0) as Locked,
    /* number of days until next report is required */
    DATEDIFF(dd,ISNULL((
                    SELECT TOP 1   
                        CreateDate
                    FROM       
                        dbo.ProgressionReport
                    WHERE   
                        (SerialNumber = SPD.SerialNumber)
                    ORDER BY
                        CreateDate DESC
                        ),SPDS.ApproveDate3), GETDATE()) - SPR.ReportInterval as DueDays,
/* the date the next report has to be filled */
    DATEADD(dd,SPR.ReportInterval,ISNULL((
                                        SELECT TOP 1   
                                            CreateDate
                                        FROM       
                                            dbo.ProgressionReport
                                        WHERE   
                                            (SerialNumber = spd.SerialNumber)
                                        ORDER BY CreateDate DESC
                                            ),
                                           
                                            (SELECT top 1 S.StartDate
                                            from @ServicePurchaseStartDates S
                                            where S.SerialNumber = spd.serialNumber)                                           
                                           
                                            )) As DueDate
   

    FROM
        SinglePurchaseDeal SPD INNER JOIN
        SinglePurchaseDealStatus SPDS ON SPD.SerialNumber = SPDS.SerialNumber INNER JOIN
        CitizenData CD ON SPD.CitizenID = CD.CitizenID INNER JOIN
        ProviderManager PM ON SPD.ProviderID = PM.ProviderID AND PM.ProviderManagerID = @ID LEFT OUTER JOIN
        UserInfo UI ON SPD.ResponsibleProviderEmployeeID = UI.UserID LEFT OUTER JOIN
        ProviderEmployee PE ON PE.ProviderEmployeeID = SPD.ResponsibleProviderEmployeeID INNER JOIN
        SinglePurchaseReporting SPR ON SPR.SinglePurchaseReportMethodID = SPD.SinglePurchaseReportMethodID INNER JOIN
        ServicePurchase SP ON SPD.SerialNumber = SP.SerialNumber INNER JOIN
        KOMMAProject KP ON SP.KOMMAProjectID = KP.KOMMAProjectID INNER JOIN
        ProviderProjects PP ON KP.KOMMAProjectID = PP.KOMMAProjectID INNER JOIN
        Provider P ON PP.ProviderID = P.ProviderID
   
    WHERE
        (SPDS.Status > 3 AND DATEDIFF(dd, SPDS.ClosedDate, GETDATE()) <= 30)
       
   
RETURN

holy long query batman

Det eneste ORDER BY du har er i nogle subqueries, og det burde ikke gøre noget:

SELECT TOP 1 Locked
FROM       
dbo.ProgressionReport
WHERE   
SerialNumber = SPD.SerialNumber
ORDER BY CreateDate DESC

Den burde kunne skrives sådan:

SELECT TOP 1 Locked
FROM       
dbo.ProgressionReport
WHERE   
SerialNumber = SPD.SerialNumber
AND CreateDate = (SELECT Max(CreateDate)
FROM dbo.ProgressionReport
WHERE   
SerialNumber = SPD.SerialNumber)

Et skud i tågen, men prøv det alle steder der står ORDER BY

lukker