From: | "Rob - TEAM Systems Ltd" <rob(at)teamsystems(dot)co(dot)uk> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: How to return a default value if no result |
Date: | 2014-08-22 11:29:27 |
Message-ID: | 002901cfbdfc$555de9a0$0019bce0$@teamsystems.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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)
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Rob - TEAM Systems
Ltd
Sent: 22 August 2014 12:19
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] 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
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-08-22 14:07:25 | Re: How to return a default value if no result |
Previous Message | Rob - TEAM Systems Ltd | 2014-08-22 11:19:14 | How to return a default value if no result |