asked on

How to return at least one record in an Oracle query

Hello,

In an Oracle Database 11gR2, I have the following table:

BLOQUEIO_MATERIAL
( ID_UNNEGOCIO VARCHAR2 ( 4 BYTE),
ID_MATERIAL VARCHAR2 ( 20 BYTE),
ID_FAMILIA VARCHAR2 ( 2 BYTE),
DESCRIPTION VARCHAR2 (200 BYTE),
BLOQUEADO_BUSCA VARCHAR2 ( 1 BYTE),
BLOQUEADO_CALCULO_DEMANDA VARCHAR2 ( 1 BYTE) )

If there is NOT a record with values¿¿:

ID_UNNEGOCIO = '0067' AND ID_MATERIAL = '627275'

The query:

SELECT id_material,
id_unnegocio,
bloqueado_busca,
bloqueado_calculo_demanda
FROM bloqueio_material
WHERE id_unnegocio = '0067'
AND id_material = '627275';

Will return no records.

What is the correct way to write the query so that it will return a record as:

id_material id_unnegocio bloqueado_busca bloqueado_calculo_demanda
----------- ------------ --------------- -------------------------
627275 0067 N N

Grateful.

Elective Junior Zanotto.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Electivo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

note the union with subquery will require more io. Even cached on the second hit, it's still likely to be less efficient.

Do try both though. There are likely conditions where the cost of an outer join would be more expensive.

Electivo

ASKER

Found a very good idea.