How can I avoid using two iSNulls

Question

I am Trying to not use two isnulls since it is making my query extremely slow. what better ways are there to write the following?

[Example] =

CAST (isnull(ap.CustomDate2, (isnull(ap.CustomDate10,
                        (Select top 1 sts.times
                                From timetable sts
                                        where sts.ShipmentStatusID IN (8000, 8089)
                                        Order By sts.times DESC)))) AS date

You have a query fragment not a proper query; `top` and `isnull` is SQL Server syntax but you have tagged *mysql*, do you know what RDBMS you are using? Tagging incorrectly is almost certain to gain you down/close votes. — Stu, Jan 31 '23 at 19:10
You probably want a `case` expression if the intent is to avoid execution of that subquery as much as possible. — shawnt00, Jan 31 '23 at 19:39

score 0 · Answer 1 · answered Jan 31 '23 at 19:29

You would be better off using a COALESCE here. Assuming you are indeed working in Sql Server and not MySql

    CAST( 
        COALESCE(
            ap.CustomerDate2, 
            ap.CustomerDate10, 
            (SELECT TOP 1 sts.times
             FROM timetable sts
             WHERE sts.ShipmentStatusID IN (8000, 8089)
             ORDER BY sts.times DESC)
        )
     AS DATE) as Your_Column_Name

How can I avoid using two iSNulls

1 Answers1