0

I am having issues getting my subquery to run properly. Everything runs quickly and correctly until I add the subquery. I get the error: "Msg 4145, Level 15, State 1, Line 66 An expression of non-boolean type specified in a context where a condition is expected, near ','."

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND (FP.Age, FP.PersonType) IN
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

I've also tried EXISTS, but it returns records that are outside the Age criteria.

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND EXISTS
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))
2

The AND (a,b) IN (SELECT x,y FROM ... WHERE) is standard SQL but not yet implemented in SQL Server. You can replace it with a correlated EXISTS subquery:

AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b)

Your EXISTS subquery is not correct because it is not correlated.

In your specific query, it should be written:

AND EXISTS 
    (SELECT *
    FROM FactPerson FPi
    WHERE FPi.PersonType = 'A'
    AND FPi.Age BETWEEN 18 AND 21
    AND FPi.Age = FP.Age AND FPi.PersonType = FP.PersonType)

But all the subquery is really unnecessary. The simple conditions would be equivalent:

AND FP.PersonType = 'A' 
AND FP.Age BETWEEN 18 AND 21
  • The correlated EXISTS worked perfect! Thank you SO much!!!! – DataLuvr May 3 '16 at 21:56
1

You need to break apart your AND (FP.Age, FP.PersonType) INpart. The following code parses correctly and should provide the data you want.

AND (
        FP.Age
    IN (
        SELECT FP.Age

        FROM FactPerson FP
        WHERE (FP.PersonType = 'A')
            AND (FP.Age BETWEEN 18 AND 21)
        )
        AND FP.person IN (
        SELECT FP.person

        FROM FactPerson FP
        WHERE (FP.PersonType = 'A')
            AND (FP.Age BETWEEN 18 AND 21)
            )
            )
  • I did that originally; but my professor does not want that. I tried using EXISTS but it is returning records that are outside the ages of 18 and 21. – DataLuvr May 2 '16 at 22:56
  • You provided a correct answer but your prof doesn't like it? What is he/she expecting to see? Did you receive guidance about a specific way to accomplish this? – dfundako May 3 '16 at 14:11
  • He liked it but said that there was a more efficient way I could be doing it. No, just to use subquery. This is what I have for the EXISTS but as I said, it's returning records that are outside the Age criteria. AND EXISTS (SELECT Age FROM FactPerson WHERE Age BETWEEN 18 AND 21 AND PersonType = 'A') – DataLuvr May 3 '16 at 16:35
  • 1
    Please include your attempt with EXISTS in your original post – dfundako May 3 '16 at 16:37
  • I just put the EXISTS in the original post. Thanks. – DataLuvr May 3 '16 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.