I'm trying to port a MySQL query to SQL Server. The query works fine in MySQL, but in SQL Server, it fails with the following error:
An expression of non-boolean type specified in a context where a condition is expected
I suspect it is because SQL Server doesn't like the fact that I use an AND coupled with the IN operator in the left join conditional.
QUERY
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
I'm not sure why though, the use of AND in a LEFT JOIN is supported in SQL Server. What am I missing?
CodePudding user response：
The use of AND in JOINs is supported in SQL Server, but you can not match multiple columns. SQL server permits only one column.
Try changing your join with this:
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)
CodePudding user response：
As mentioned in the other answer, SQL Server does not support row-value comparators.
But to avoid repeating code, you can use an 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
I must say, this whole query looks like it could do with a good dose of window functions, especially this section above.