Re: Please, the condition don't filter, Why??????

On 11/19/2004 09:29:55 PM, Juan Carlos Reyes Pacheco wrote:
> Hi I can't filter a query, what I'm doing wrong, please

Ehm, this is an improvement over "DBA tips". Much better. So, let's be =20 nice about it ant try to help you out.

>=20
> This is the query
> SELECT TIPO, CTP_COD, CTP_NOMBRE
> FROM SOA.VEW_STR_CONTRAPARTE
> WHERE ((CTP_TIPO_CTPTE IS NULL OR CTP_TIPO_CTPTE=3D 'AGB' )) AND
> (INC_CODCLI_TIT =3D NVL(NULL,-1) OR INC_CODCLI_TIT IS NULL ) AND
NVL(NULL,-1) =3D -1. It is a constant expression. Your condition reads ((INC_CODCLI_TIT =3D -1) OR (INC_CODCLI_TIT IS NULL))
> INC_TIPO =3D 'P' AND CTP_COD=3D'CNC';
> and it returns all rows from the table ( in thery it only have to
> return the
> one that has CTP_COD=3D'CNC'

You can do this:

SELECT TIPO, CTP_COD, CTP_NOMBRE
from (
SELECT TIPO, CTP_COD, CTP_NOMBRE,CTP_TIPO_CTPTE,INC_CODCLI_TIT FROM SOA.VEW_STR_CONTRAPARTE
WHERE INC_TIPO =3D 'P' AND CTP_COD=3D'CNC') WERE ((CTP_TIPO_CTPTE IS NULL OR CTP_TIPO_CTPTE=3D 'AGB' )) AND       ((INC_CODCLI_TIT =3D -1) OR (INC_CODCLI_TIT IS NULL )) But it shouldn't make any difference. Subset operations intersection =20 and union are both commutative and associative, which means that your =20 query is likely wrong.

>and it returns all rows from the table ( in thery it only have to
> return the one that has CTP_COD=3D'CNC'

Do you have a primary key on that table? What do you mean "the one that =20 has CTP_COD=3D'CNC'"? Is CTP_COD some kind of a key? If so, why would you =20 want to qualify the query any further? Your filtering is based on =20 nullable columns, which means that your primary key is not involved in this query. I smells suspiciously like a bad design. Normally, if =20 it's not easy to get data from the table based on a set of columns, =20 that is because the table was not designed to make that type of queries =20 easy. It usually signifies bad understanding of the underlying =20 business model or a cataclysmic shift in the business model itself. --=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l