CODEWITHSUNDEEP
sqloracle-databasejoin
user1324945
user1324945

Reputation: 11

Oracle SQL: Returning a Record even when a specific value doesn't exist

I have a query where I'm trying to pull some values from a table where a specific ID is queried for. If that value doesn't exist, I would still like the query to return a record that only has that ID value I was looking for. Here's what I've tried so far.

Select attr.attrval, attr.uidservicepoint, sp.servicepointid 
From bilik.lssrvcptmarketattr attr 
Join bilik.lsmarketattrtype type on attr.uidmarketattrtype = type.uidmarketattrtype AND 
type.attrtype IN ('CAPACITY_REQUIREMENT_KW') and TO_CHAR( attr.starttime , 'mm/dd/yyyy')in ('05/01/2011') 
Right Outer Join bilik.lsservicepoint sp on attr.uidservicepoint = sp.uidservicepoint
Where sp.servicepointid in ('RGE_R01000051574382') Order By sp.servicepointid ASC

In this example, I'm trying to look for RGE_R01000051574382. If that doesn't exist in table SP.servicepointid, I want it to still return the 'RGE_R01000051574382' in a record with nulls for the other values I'm pulling. Normally, when I'm running this, I will be pulling about 1000 specific values at a time.

If anyone has any insight that they can give on this, it would be greatly appreciated. Thanks so much!

Upvotes: 1

Views: 1338

Answers (2)

krissco
krissco

Reputation: 21

I think you're saying you want to have a record returned, with the servicepointid column populated, but all others null?

In that case, use a union.

select   ...your query without order by...
and      sp.servicepointid = 'RGE_R010000515743282'
union
select   null, null, 'RGE_R010000515743282'
from     dual
where    not exists (select 'x' from (...your query without order by...))

Here's a complete example:

create table test (id number, val varchar2(10));
insert into test (id, val) values (1, 'hi');

select   id,
         val
from     test
where    id = 1
union
select   1,
         null
from     dual
where    not exists (select   'x'
                     from     test
                     where    id = 1)

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

If I understand correctly, you just need to move the WHERE clause into the JOIN clause.

select attr.attrval,
    attr.uidservicepoint,
    sp.servicepointid
from bilik.lssrvcptmarketattr attr
join bilik.lsmarketattrtype type on attr.uidmarketattrtype = type.uidmarketattrtype
    and type.attrtype in ('CAPACITY_REQUIREMENT_KW')
    and TO_CHAR(attr.starttime, 'mm/dd/yyyy') in ('05/01/2011')
right outer join bilik.lsservicepoint sp on attr.uidservicepoint = sp.uidservicepoint
    and sp.servicepointid in ('RGE_R01000051574382')
order by sp.servicepointid

Upvotes: 1

Related Questions