Обсуждение: How to return a default value if no result

I want my query to return at least one row with a default result even if there are no matches in the database.

As a simple example, getting the valid VAT rate for a transaction date and VAT code:

Select vat_rate from vatrates where vat_code=’1’ and vat_date<=’2014-08-01’ order by vat_date desc limit 1

This is fine as long as there is a match.  If there are no matches, I’d like it to return a default (0) rather than no result.

I could make sure there is always a result by having a UNION with a hard-coded record, but this doesn’t let me order by a field not in the result.

In this example I could just make sure there is a dummy row in the table with a very old date and do something like this:

Select vat_rate from vatrates where (vat_code=’1’ or vat_code=’DUMMY’) and vat_date<=’2014-08-01’ order by vat_date desc limit 1

But it seems like a bodge to me.

Is there a nice way of forcing a default result somehow?

Thanks,

Rob

OK, we’ve thought of another possibility, still seems a bodge though:

Select case when exists(Select vat_rate from vatrates where vat_code=’1’ and vat_date<=’2014-08-01’)

Then (Select vat_rate from vatrates where vat_code=’1’ and vat_date<=’2014-08-01’ order by vat_date desc limit 1)

Else (select 0 as vat_rate from vatrates limit 1)

I want my query to return at least one row with a default result even if there are no matches in the database.

As a simple example, getting the valid VAT rate for a transaction date and VAT code:

Select vat_rate from vatrates where vat_code=’1’ and vat_date<=’2014-08-01’ order by vat_date desc limit 1

This is fine as long as there is a match.  If there are no matches, I’d like it to return a default (0) rather than no result.

I could make sure there is always a result by having a UNION with a hard-coded record, but this doesn’t let me order by a field not in the result.

In this example I could just make sure there is a dummy row in the table with a very old date and do something like this:

Select vat_rate from vatrates where (vat_code=’1’ or vat_code=’DUMMY’) and vat_date<=’2014-08-01’ order by vat_date desc limit 1

But it seems like a bodge to me.

Is there a nice way of forcing a default result somehow?

Thanks,

Rob