#sql-server #tsql
Вопрос:
Я пытаюсь перенести запрос MySQL на SQL Server. Запрос отлично работает в MySQL, но в SQL Server он завершается ошибкой со следующей ошибкой:
Выражение не булева типа, заданное в контексте, в котором ожидается условие
Я подозреваю, что это связано с тем, что SQL Server не нравится тот факт, что я использую AND оператор в сочетании с IN оператором в условном соединении слева.
запрос
SELECT *
FROM
(SELECT dol.Id AS 'log_id',
c.Id AS 'computer_id',
COALESCE(r.processor_test, 'Skipped') as 'processor_test',
COALESCE(r.display_test, 'Skipped') as 'display_test',
COALESCE(r.storage_test, 'Skipped') as 'storage_test',
COALESCE(r.network_test, 'Skipped') as 'network_test',
COALESCE(r.keyboard_test, 'Skipped') as 'keyboard_test',
COALESCE(r.mouse_test, 'Skipped') as 'mouse_test',
COALESCE(user_fields.customer_id, 'None') AS 'customer_id',
COALESCE(user_fields.order_no, 'None') AS 'order_no',
COALESCE(user_fields.technician_id, 'None') AS 'technician_id',
CONCAT('Customer ID: ', customer_id, '<br>Order No: ', order_no, '<br>Technician ID: ', technician_id) AS 'userfields',
hd.Id AS 'hard_disk_id',
dol.StartTime AS 'start_time',
hd.Serial AS 'hard_disk_serial',
CONCAT(hd.Gigabytes, ' GB') AS 'gigabytes',
CONCAT('<b>Vendor: </b>', hd.Vendor, '<br><br><b>Model: </b>', hd.Product, '<br><br><b>Serial: </b>', hd.Serial, '<br><br><b>Size: </b>', hd.Gigabytes, ' GB<br><br><b>Pre-wipe SMART health status: ', dol.SmartOverallPre, '<br><br><b>Post-wipe SMART health status: ', dol.SmartOverallPost) AS 'target_drive_detailed',
CONCAT('Vendor: ', hd.Vendor, '<br><br>Product: ', hd.Product, '<br><br>Serial: ', hd.Serial, '<br><br>Drive Media Type: ', hd.DriveMediaType , '<br><br>Size: ', hd.Gigabytes, ' GB<br><br><b>Interface: ', hd.Interface) AS 'drive_detailed',
CONCAT(hd.Serial, ' (',hd.Gigabytes, ' GB)') AS 'target_drive',
CASE
WHEN dol.PatternName = 'NIST 800-88r1 (recommended)' THEN 'NIST 800-88r1'
ELSE dol.PatternName
END AS 'pattern_name',
CASE
WHEN dol.Success = '1' THEN 'SUCCESS'
WHEN dol.Success = '0' THEN 'FAILED'
END AS 'wipe_status',
dol.Errors AS 'errors',
dol.DirtySectors AS 'dirty_sectors',
dol.Tool AS 'tool',
dol.Kernel AS 'kernel',
dol.JobUUID AS 'job_uuid',
dol.UUID AS 'uuid',
CONVERT(VARCHAR(20), dol.EndTime, 120) AS 'end_time',
substring(CONVERT(VARCHAR(20), dol.EndTime, 120), 1, 11) AS 'end_time_short',
dol.NumPasses AS 'num_passes',
dol.TrimPasses AS 'trim_passes',
dol.SectorsOverwritten AS 'sectors_overwritten',
dol.SectorsNotOverWritten AS 'sectors_not_overwritten',
dol.SectorsVerified AS 'sectors_verified',
dol.CustomField AS 'custom_field_legacy',
CASE
WHEN dol.NISTMethodTypeId = '1' THEN 'Unknown'
WHEN dol.NISTMethodTypeId = '2' THEN 'Clear'
WHEN dol.NISTMethodTypeId = '3' THEN 'Purge'
END AS 'nis_method_type',
CASE
WHEN dol.DCOFound = '1' AND dol.DcoRemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ')
WHEN dol.DCOFound = '0' AND dol.DcoRemoved = '0' THEN CONCAT('No /</td><td> No', ' ')
WHEN dol.DCOFound = '1' AND dol.DcoRemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ')
WHEN dol.DCOFound = '0' AND dol.DcoRemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ')
END AS 'dco_foundremoved',
CASE
WHEN dol.DCOLocked = '1' THEN 'Yes'
WHEN dol.DCOLocked = '0' THEN 'No'
END AS 'dco_locked',
CASE
WHEN dol.HPAFound = '1' AND dol.HPARemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ')
WHEN dol.HPAFound = '0' AND dol.HPARemoved = '0' THEN CONCAT('No /</td><td> No', ' ')
WHEN dol.HPAFound = '1' AND dol.HPARemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ')
WHEN dol.HPAFound = '0' AND dol.HPARemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ')
END AS 'hpa_foundremoved',
CASE
WHEN dol.AMAXFound = '1' AND dol.AMAXRemoved = '1' THEN CONCAT('Yes /</td><td> Yes', ' ')
WHEN dol.AMAXFound = '0' AND dol.AMAXRemoved = '0' THEN CONCAT('No /</td><td> No', ' ')
WHEN dol.AMAXFound = '1' AND dol.AMAXRemoved = '0' THEN CONCAT('Yes /</td><td> No', ' ')
WHEN dol.AMAXFound = '0' AND dol.AMAXRemoved = '1' THEN CONCAT('No /</td><td> Yes', ' ')
END AS 'amax_foundremoved',
c.Vendor AS 'vendor',
c.Model AS 'computer_model',
c.ComputerSerial AS 'computer_serial',
CONCAT('<b>Vendor: </b>', c.Vendor, '<br><br><b>Model: </b>', c.Model, '<br><br><b>Serial: </b>', c.ComputerSerial) as 'computer_summary',
CONCAT('<b>Vendor: </b>', c.MotherboardVendor, '<br><br><b>Model: </b>', c.MotherboardModel) as 'motherboard_summary',
CONCAT(first_cpu.Name, ' ', first_cpu.Speed) as 'cpu_summary',
CONCAT(first_nic.Vendor, '<br><br>', first_nic.Product) as 'nic_summary',
CONCAT(first_vc.Vendor, '<br><br>', first_vc.Product) as display_summary,
CONCAT('<b>USB1 Ports: </b>', c.UsbPorts, '<br><br><b>USB2 Ports: </b>', c.Usb2Ports, '<br><br><b>USB3 Ports: </b>', c.Usb3Ports) as usb_ports,
c.Memory AS 'computer_memory',
hd.DriveMediaType AS 'hard_disk_drive_media_type',
hd.Product AS 'hard_disk_product',
hd.Vendor AS 'hard_disk_vendor'
FROM DiskOperationLog dol
LEFT JOIN
(SELECT juf.JobUUID AS 'juf_job_uuid',
COALESCE(max(CASE
WHEN juf.FieldName = 'Customer ID' THEN juf.FieldValue
END), 'None') AS customer_id,
COALESCE(max(CASE
WHEN juf.FieldName = 'Order No' THEN juf.FieldValue
END), 'None') AS order_no,
COALESCE(max(CASE
WHEN juf.FieldName = 'Technician ID' THEN juf.FieldValue
END), 'None') AS technician_id
FROM DiskOperationLog dol
INNER JOIN JobUserFields juf ON dol.JobUUID = juf.JobUUID
GROUP BY juf.JobUUID) user_fields ON dol.JobUUID = user_fields.juf_job_uuid
LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId and (hd.Id, dol.EndTime) IN (select max(hd.Id) as Id, max(dol.EndTime) as EndTime from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)
LEFT JOIN Computer c ON c.Id = hd.ComputerId
LEFT JOIN
(select t.computer_id,
MAX(
CASE
WHEN Type='Processor'
THEN Result
ELSE NULL
END
) AS 'processor_test',
MAX(
CASE
WHEN Type='Display'
THEN Result
ELSE NULL
END
) AS 'display_test',
MAX(
CASE
WHEN Type='Network'
THEN Result
ELSE NULL
END
) AS 'network_test',
MAX(
CASE
WHEN Type='Keyboard'
THEN Result
ELSE NULL
END
) AS 'keyboard_test',
MAX(
CASE
WHEN Type='Mouse'
THEN Result
ELSE NULL
END
) AS 'mouse_test',
MAX(
CASE
WHEN Type='Storage'
THEN Result
ELSE NULL
END
) AS 'storage_test'
from (
select c.Id as computer_id,
hst.Type,
case min(
case hst.Result
when'Fail' then 1
when'Skipped' then 2
when'Pass' then 3
end)
when 1 then 'Fail'
when 2 then 'Skipped'
when 3 then 'Pass'
end Result
from Computer c
inner join HardwareTest ht on c.Id = ht.ComputerId
inner join HardwareSubTest hst on ht.Id = hst.HardwareTestId
group by c.Id, hst.Type
) t
group by computer_id) r on c.Id = r.computer_id
JOIN
(SELECT *
FROM Cpu cpu
WHERE Id in
(SELECT min(Id)
FROM Cpu
GROUP BY ComputerId)) AS first_cpu ON c.Id = first_cpu.ComputerId
JOIN
(SELECT *
FROM Nic nic
WHERE Id in
(SELECT min(Id)
FROM Nic
GROUP BY ComputerId)) AS first_nic ON c.Id = first_nic.ComputerId
JOIN
(SELECT *
FROM VideoCard vc
WHERE Id in
(SELECT min(Id)
FROM VideoCard
GROUP BY ComputerId)) AS first_vc ON c.Id = first_vc.ComputerId) AS query_result
Я не уверен, почему, однако, использование AND in a LEFT JOIN поддерживается в SQL Server. Что я упускаю?
Комментарии:
1. Извините, сегодня утром у нас закончился кофе 😀 добавил запрос.
2. Проблема в том, что вы пытаетесь сопоставить несколько столбцов.
3. Я вижу, нормально ли, что я все еще получаю ошибку , если пытаюсь сопоставить один столбец? Я просто проверял, сработает ли это при сопоставлении одного столбца.
4. На самом деле я тоже не думаю, что это сработает, потому что это не скалярный запрос. Я на своем телефоне, иначе я бы набрал его.
5. Не спеши, спасибо
Ответ №1:
Использование И в соединениях поддерживается в SQL Server, но вы не можете сопоставлять несколько столбцов. SQL server разрешает только один столбец.
Попробуйте изменить свое соединение с помощью этого:
LEFT JOIN HardDisk hd
ON hd.Id = dol.HardDiskId
and hd.Id in (select max(hd.Id) from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)
AND dol.EndTime in (select max(dol.EndTime) from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id)
Комментарии:
1. Спасибо @RXolio, похоже, это не работает .
2. Я изменил свой ответ, когда подзапрос возвращает более 1 значения, которые вы можете использовать В
3. Это, кажется, работает, спасибо! Я проверю результат как можно скорее.
4. Было
APPLYбы лучше, так как вам не нужно повторять5. В лучшем случае вся эта логика объединения полностью избыточна. Хуже того, эта логика конечного времени может даже исключать столбцы HD из результатов. Не видя данных, я не могу этого определить.
Ответ №2:
Как упоминалось в другом ответе, SQL Server не поддерживает компараторы значений строк.
Но чтобы избежать повторения кода, вы можете использовать APPLY
OUTER APPLY (
select
MaxId = max(hd.Id),
MaxEndTime = max(dol.EndTime)
from DiskOperationLog dol2
join HardDisk hd on hd.Id = dol2.HardDiskId
group by hd.Id
) dol2
LEFT JOIN HardDisk hd
ON hd.Id = dol.HardDiskId
and hd.Id = dol2.MaxId
AND dol.EndTime = dol2.MaxEndTime
Я должен сказать, что весь этот запрос выглядит так, как будто он мог бы работать с хорошей дозой оконных функций, особенно в этом разделе выше.
Ответ №3:
Прочитав очень внимательно, я уверен, что эта часть вашего запроса абсолютно ничего не дает. Я не уверен, какова была предполагаемая цель в попытке соответствовать dol.EndTime , но это неправильный способ сделать это.
LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId and (hd.Id, dol.EndTime) IN (
select max(hd.Id) as Id, max(dol.EndTime) as EndTime
from DiskOperationLog dol join HardDisk hd on hd.Id = dol.HardDiskId group by hd.Id
)
Я бы посоветовал вам перейти на приведенное ниже или изучить, какова цель, и обновить свой вопрос:
LEFT JOIN HardDisk hd ON hd.Id = dol.HardDiskId