Subquery in select statement gives an error

Transact-SQL
1

Hi,

I have this query,

SELECT  0 AS [IsSelected]
    ,{LabEquipments}.[SNo] AS [TempNumber]
    , {LabEquipments}.[Id] AS [LabEquipmentId]
    , {LabEquipments}.[EquipmentInventoryNumber] AS [EquipmentInventoryNumber]
    , {CategoryOfEquipment}.[Id] AS [CategoryOfEquipment]
    , {CategoryOfEquipment}.[Name] AS [CategoryName]
    , {Manufacturer}.[Name] AS [Manufacturer]
    , {LabEquipments}.[Model]
    , {LabEquipments}.[Description]
    , {Status}.[Id] AS [Status]
    , {Status}.[Name] AS [StatusName]
    , {EquipmentPerformance}.[Name] AS [PerformanceStatus]
    , {ReservationStatus}.[Id] AS [ReservationStatusId]
    , {ReservationStatus}.[Label] AS [ReservationStatus]
    , {CalibrationSite}.[Id] AS [CalibrationSiteId]
    , {CalibrationSite}.[Name] AS [CalibrationSite]           
    , {LabEquipmentCalibration}.[CalibrationFrequency] AS [FrequencyId]
    , {CalibrationFrequency}.[Months] AS [Frequency]
    
    , ( SELECT {Calibration}.[NextCalibrationDate]
        from {Calibration}
        where 
                {Calibration}.[IsDeleted] = 0 and
                {LabEquipments}.[IsDeleted] = 0 and
                {Calibration}.[LastCalibrationDate] = ( select max({Calibration}.[LastCalibrationDate]) 
                                                        from {Calibration} 
                                                        where {Calibration}.[LabEquipmentId] = {LabEquipments}.[Id] and {Calibration}.[LastCalibrationDate] <> '01-01-1900 00:00:00'
                                                      )
       )

    , {LabEquipmentCalibration}.[CalibrationCost] AS [CalibrationCost]
    , {LabEquipmentCalibration}.[Currency] AS [CalibrationCostCurrencyId]
    , '' as [CalibrationCostCurrency]
    , {LabEquipmentVendorInformation}.[PurchasePrice] AS [ItemCost]
    , {LabEquipmentVendorInformation}.[CurrencyId] AS [ItemCostCurrencyId]
    , '' as [ItemCostCurrency]
    , {Region}.[Id],
      {Region}.[Name] AS [Site]
    , '' as [EquipmentPerformance]
    , '' as [Status2]
    , {CalibrationContractor}.[Name] as [MadeName]
    , {LabEquipmentVendorInformation}.[Purchase_Delivery_Date] as [PurchaseDate]
    , {LabEquipmentVendorInformation}.[VendorName] as [SupplierId]
    , {VendorName}.[Name] as [Supplier]

FROM {LabEquipments}
    LEFT JOIN {CategoryOfEquipment} ON {LabEquipments}.[CategoryOfEquipmentId] = {CategoryOfEquipment}.[Id]
    LEFT JOIN {Status} ON {LabEquipments}.[StatusId] = {Status}.[Id]       
    Left join {Manufacturer} on {LabEquipments}.[ManufacturerId] = {Manufacturer}.[Id]
    Left JOIN {EquipmentPerformance} on {LabEquipments}.[EquipmentPerformanceSurveyId] = {EquipmentPerformance}.[Id]
    LEFT JOIN {LabEquipmentsReservation} ON {LabEquipmentsReservation}.[LabEquipmentId] = {LabEquipments}.[Id]
    LEFT JOIN {ReservationStatus} ON {LabEquipments}.[ReservationStatusId] = {ReservationStatus}.[Id]
    LEFT JOIN {LabEquipmentCalibration} ON {LabEquipmentCalibration}.[LabEquipmentsId] = {LabEquipments}.[Id]
    LEFT JOIN {CalibrationSite} ON {LabEquipmentCalibration}.[CalibrationSite] = {CalibrationSite}.[Id]
    LEFT JOIN {LabEquipmentVendorInformation} ON {LabEquipmentVendorInformation}.[LabEquipmentId] = {LabEquipments}.[Id]
    LEFT JOIN {CalibrationFrequency} ON {LabEquipmentCalibration}.[CalibrationFrequency] = {CalibrationFrequency}.[Id]
    left join {Region} on {Region}.[Id] = {LabEquipments}.[Region]
    left join {LabEquipment_EquipmentPerformance} on {LabEquipment_EquipmentPerformance}.[LabEquipmentId] = {LabEquipments}.[Id]
    left join {LabEquipment_Status} on {LabEquipment_Status}.[LabEquipmentId] = {LabEquipments}.[Id]
    left join {Calibration} on {Calibration}.[LabEquipmentId] = {LabEquipments}.[Id]
    left join {CalibrationContractor} on {LabEquipmentCalibration}.[MadeBy] = {CalibrationContractor}.[Id]
    LEFT JOIN {VendorName} on {LabEquipmentVendorInformation}.[VendorName] = {VendorName}.[Id]
   
WHERE (
            {LabEquipments}.[IsDeleted] = 0 
        AND {LabEquipments}.[EquipmentInventoryNumber] != ''
        AND (
                {LabEquipments}.[EquipmentInventoryNumber] like '%' + @SearchKeyword + '%' or
                {LabEquipments}.[Model] like '%' + @SearchKeyword + '%' or
                {Region}.[Name] like '%' + @SearchKeyword + '%' or 
                {Manufacturer}.[Name] like '%' + @SearchKeyword + '%' or
                {LabEquipments}.[Description] like '%' + @SearchKeyword + '%' or
                {Status}.[Name] like '%' + @SearchKeyword + '%' or
                {ReservationStatus}.[Label] like '%' + @SearchKeyword + '%' or
                {CalibrationSite}.[Name] like '%' + @SearchKeyword + '%' or
                {CalibrationFrequency}.[Months] like '%' + @SearchKeyword + '%'                
            )

        AND (@PerformanceStatusId = '' OR @PerformanceStatusId = {LabEquipment_EquipmentPerformance}.[EquipmentPerformanceID] )
        AND (@EquipmentInventoryNo = '' OR @EquipmentInventoryNo = {LabEquipments}.[EquipmentInventoryNumber])
        AND (@CategoryId = 0 OR @CategoryId = {LabEquipments}.[CategoryOfEquipmentId])
        AND (@StatusId = 0 OR @StatusId = {LabEquipment_Status}.[StatusID])
        AND (@SiteId = 0 OR @SiteId = {LabEquipmentCalibration}.[CalibrationSite])
        AND (@Year = 0 OR @Year = Year({LabEquipments}.[Next]))
        AND (@VendorNameID = 0 or @VendorNameID = {LabEquipmentVendorInformation}.[VendorName])
        AND (@CalibrationCOntractorId = 0 or @CalibrationCOntractorId = {LabEquipmentCalibration}.[MadeBy])
 
      )

I have added this part in select statement

, ( SELECT {Calibration}.[NextCalibrationDate]
from {Calibration}
where
{Calibration}.[IsDeleted] = 0 and
{LabEquipments}.[IsDeleted] = 0 and
{Calibration}.[LastCalibrationDate] = ( select max({Calibration}.[LastCalibrationDate])
from {Calibration}
where {Calibration}.[LabEquipmentId] = {LabEquipments}.[Id] and {Calibration}.[LastCalibrationDate] <> '01-01-1900 00:00:00'
)
)

and after that it gives me an error

AdvanceQuery.png1311×699 18.4 KB

This query return first 2500 records correctly but if I increase the limit to 10000, it gives this error.

Please advice.

Thanks and Regards,
Suraj Borade

2

The error message pretty much describes what the issue is - the query you added is returning more than one row for a given row without the part you added. To test this, add a TOP (1) clause in the part you added.

....
, ( SELECT TOP (1) {Calibration}.[NextCalibrationDate]
....

That does not, obviously, resolve the issue, but lets you identify if that indeed is the problem. Then, you have to figure out what you want to do in cases where more than one row is returned.