About CASE and WHERE

*I have a Query like this:
SELECT CARTERA_PROV.cCAJA,
CAJA.sCAJA,
CARTERA_PROV.fEMISION,
CARTERA_PROV.fVENCIMIENTO
FROM
CARTERA_PROV,
ANEXO,
TIPO_DOCUMENTO,
CAJA,
CONCEPTO_GASTO,
MOVIMIENTO_CAJA
WHERE
case CARTERA_PROV.cCONCEPTO_CARTERA
when '38' then CARTERA_PROV.gTIPO_PRESTAMO
else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
end = #ANY VALUE#
AND
(...)
*Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
TRUE all this sentence:
case CARTERA_PROV.cCONCEPTO_CARTERA
when '38' then CARTERA_PROV.gTIPO_PRESTAMO
else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
end = #ANY VALUE#
*Can someone explain me that please? I don't think is a bug.
Regards,
Ral La TorreHmm. It seems that what you have should work fine.
Here is an example that shows the results. The query correctly returns the
1st and 4th rows but does not return the 2nd and 3rd rows.
CREATE TABLE TestTable(column1 int, column2 int, column3 int)
GO
INSERT INTO TestTable VALUES(1,2,5)
INSERT INTO TestTable VALUES(2,2,5)
INSERT INTO TestTable VALUES(3,5,5)
INSERT INTO TestTable VALUES(4,2,2)
GO
select *
from TestTable
WHERE CASE column1
WHEN 1 THEN column2
ELSE column3
END = 2
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Raul La Torre" wrote:

> *I have a Query like this:
> SELECT CARTERA_PROV.cCAJA,
> CAJA.sCAJA,
> CARTERA_PROV.fEMISION,
> CARTERA_PROV.fVENCIMIENTO
> FROM
> CARTERA_PROV,
> ANEXO,
> TIPO_DOCUMENTO,
> CAJA,
> CONCEPTO_GASTO,
> MOVIMIENTO_CAJA
> WHERE
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
> AND
> (...)
>
> *Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
> TRUE all this sentence:
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
>
> *Can someone explain me that please? I don't think is a bug.
> Regards,
> Raúl La Torre
>
>|||Just guessing, but try putting your case statement inside parenthesis.
Maybe the entire statement is not parsing as a whole and this will force it
to be handled as you desire. Adding in parentheses and some indentation can
make the code more readable anyway.
However,
I don't see why case is needed, or even useful here. I think an OR will be
easier to follow, more standard (i.e. not proprietary), portable, and more
efficient (I may be wrong here, still learning my way around the SQL tuning
process).
(
(CARTERA_PROV.gTIPO_PRESTAMO = #ANY VALUE# and
CARTERA_PROV.cCONCEPTO_CARTERA = 38)
or
(MOVIMIENTO_CAJA.gTIPO_PRESTAMO = #ANY VALUE# and
CARTERA_PROV.cCONCEPTO_CARTERA <> 38)
)
"Raul La Torre" <raul_la_torre@.hotmail.com> wrote in message
news:usHr%23l8GGHA.3896@.TK2MSFTNGP15.phx.gbl...
> *I have a Query like this:
> SELECT CARTERA_PROV.cCAJA,
> CAJA.sCAJA,
> CARTERA_PROV.fEMISION,
> CARTERA_PROV.fVENCIMIENTO
> FROM
> CARTERA_PROV,
> ANEXO,
> TIPO_DOCUMENTO,
> CAJA,
> CONCEPTO_GASTO,
> MOVIMIENTO_CAJA
> WHERE
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
> AND
> (...)
>
> *Whatever the value I put instead of #ANY VALUE#, the query ALWAYS analyze
> TRUE all this sentence:
> case CARTERA_PROV.cCONCEPTO_CARTERA
> when '38' then CARTERA_PROV.gTIPO_PRESTAMO
> else MOVIMIENTO_CAJA.gTIPO_PRESTAMO
> end = #ANY VALUE#
>
> *Can someone explain me that please? I don't think is a bug.
> Regards,
> Ral La Torre
>