SQL Server: Boolean expressions with subquery in WHERE-clause

Question

Hi!

I have a subquery within a WHERE-clause within a subquery. Notwithstanding the design issues of the database (not my job), I am getting some strange errors when trying to extend the top level WHERE-clause in this expression. The example below WORKS.

        LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationID 
                        = (SELECT TOP 1 CargoVoyageLocation.VoyageLocationID FROM CargoVoyageLocation
                                JOIN Cargo on CargoVoyageLocation.CargoID = Cargo.CargoID
                                JOIN CargoType on Cargo.CargoTypeID = CargoType.CargoTypeID
                                WHERE

                (CargoType.SystemName = 'HAZMAT' OR CargoType.SystemName = 'BUNKERS' OR Cargo.IsBunkers = 1)
                    AND 
                        CargoVoyageLocation.VoyageLocationID 
                                = (SELECT VoyageLocationAttribute.VoyageLocationID FROM VoyageLocationAttribute 
                                        JOIN VoyageLocationAttributeName 
                                            ON VoyageLocationAttribute.VoyageLocationAttributeNameID = VoyageLocationAttributeName.VoyageLocationAttributeNameID
                                            WHERE VoyageLocationAttribute.VoyageLocationAttributeNameID = (SELECT VoyageLocationAttributeNameID FROM VoyageLocationAttributeName WHERE SystemName = 'PREVIOUS_VOYAGE_ID')
                                                AND VoyageLocationAttribute.AttributeInt = v.VoyageID) ORDER BY CargoVoyageLocation.ModifiedDate DESC)

Then I add a simple boolean expression and parenthesis:

            LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationID 
                        = (SELECT TOP 1 CargoVoyageLocation.VoyageLocationID FROM CargoVoyageLocation
                                JOIN Cargo on CargoVoyageLocation.CargoID = Cargo.CargoID
                                JOIN CargoType on Cargo.CargoTypeID = CargoType.CargoTypeID
                                WHERE

                (CargoType.SystemName = 'HAZMAT' OR CargoType.SystemName = 'BUNKERS' OR Cargo.IsBunkers = 1)
                    AND 
Here --->                       (1=0 OR CargoVoyageLocation.VoyageLocationID 
                                = (SELECT VoyageLocationAttribute.VoyageLocationID FROM VoyageLocationAttribute 
                                        JOIN VoyageLocationAttributeName 
                                            ON VoyageLocationAttribute.VoyageLocationAttributeNameID = VoyageLocationAttributeName.VoyageLocationAttributeNameID
                                            WHERE VoyageLocationAttribute.VoyageLocationAttributeNameID = (SELECT VoyageLocationAttributeNameID FROM VoyageLocationAttributeName WHERE SystemName = 'PREVIOUS_VOYAGE_ID')
                                                AND VoyageLocationAttribute.AttributeInt = v.VoyageID) ORDER BY CargoVoyageLocation.ModifiedDate DESC))

This produces the following error:

1. Incorrect syntax near the keyword 'ORDER'.
2. Incorrect syntax near 'v'.

I'm stumped. What is going on here?

Answer 1

That ORDER BY clause is being used by the TOP 1 (line 2), so I suspect your bracketing is wrong, and the last line should be:

AND VoyageLocationAttribute.AttributeInt = v.VoyageID)) ORDER BY CargoVoyageLocation.ModifiedDate DESC)