Re: Please, the condition don't filter, Why??????
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: jreyes@xxxxxxxxxxxxxxxx
- Date: Sat, 20 Nov 2004 03:10:20 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
