case where no value exists

[straud13]

I am querying an index table and I would like the case to accomodate where no record exists in the table. Here is my query so far.

select isBP = case
when uf.code_id = 19 then
(Select 'BP')
when uf.code_id is Null then
(select 'Null')
when uf.code_id <> 19 then
(select 'Not BP')
end

from amgr_CSCases CS
left outer join amgr_user_fields uf on
cs.client_id = uf.client_id and
cs.client_number = uf.contact_number
join amgr_user_field_defs ufd on
uf.type_id = ufd.type_id and
uf.code_id = ufd.code_id and
ufd.type_id = 306
where cs.client_id = '711-D1210808' and cs.client_number = 0 and cs.record_id = 27491

the second when I want to run when no row exists in the table, i've tried is null and not exists in the where but they didn't work. when complete the values in the where are going to be captured by a trigger, these are hard coded for test only.

 

[ArtieChoke]

Try setting the isBP to a value that you know won't occur first, like:

select isBP = 'bogus'
select isBP = case
when uf.code_id = 19 then
(Select 'BP')
...

then do the query and test for it = 'bogus' - this will tell you nothing came back from the query:

if isBP = 'bogus' then
print 'no data found'


"I think we're all Bozos on this bus!" - Firesign Theatre