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
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