Abort Subsequent Union All Commands If Match Found [h2]

How do I adapt the query below so that it doesn't perform unnecessary UNION ALL unless the SELECT statement above it doesn't find a match? SELECT LATITUDE, LONGITUDE FROM coordinat

Solution 1:

You have to dummy the data. Make the first select some dummy record so that it never fails. or determine what will not fail before you start. You can then use a nested select to remove your dummy row. Basic syntax example.

select Latitude, longitude from
(
select'dummy data'as Lattitude, 'AltDummy'as Longitude
unionallSELECT LATITUDE, LONGITUDE FROM coordinates WHERE address = ? AND    community = ?
UNIONALLSELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text = ? AND community::text LIKE ? 
UNIONALLSELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text LIKE ? AND community
) x
where Lattitude <>'dummy data'and Longitude <>'AltDummy'

You will also need to cast your dummy data in the exact same data type as your actual data for this to work right 100% of the time. Submit it to the sql server all as one query and let the sql server do the work.

Solution 2:

Other approach:

SELECT LATITUDE, LONGITUDE FROM coordinates 
WHERE (address = ? AND community = ?)
or (text = ? AND community::textLIKE ? )
or (textLIKE ? AND community::textLIKE ?) 

Same net result. Speed differences vary depending on what platform you are working on. But this is likely faster than the previous.