W.Jack W.Jack - 1 month ago 10
MySQL Question

An extra empty row occured when I am using CASE WHEN expression in Oracle.

I am trying to output some necessary rows for my report using a simple query to fetch. That's the query:-

SELECT DISTINCT Z.ITEMDESC, X.NMCODDES AS CODE

FROM NMCODMAS X, NMITEMAS Y, NMSALREC Z

WHERE
X.NMHRDCDE='PKZ'
AND Y.FINISHGD='Y'
AND X.COMPCODE=Z.COMPCODE
AND Y.COMPCODE=Z.COMPCODE
AND X.COMPCODE=Y.COMPCODE
AND Y.USERDEF1=X.NMSOFCDE
AND Y.ITEMCODE=Z.ITEMCODE
AND Z.DOCTDATE BETWEEN NVL(:P11,Z.DOCTDATE) AND NVL(:P12,Z.DOCTDATE)


And Here's the ouput

But for some reasons I can't use
X.NMHRDCDE='PKZ'
IN WHERE. So, I just use
X.NMHRDCDE='PKZ'
in a CASE WHEN Expression. Just Like that:-

SELECT DISTINCT Z.ITEMDESC,

CASE
WHEN X.NMHRDCDE='PKZ'
THEN X.NMCODDES
END AS CODE

FROM NMCODMAS X, NMITEMAS Y, NMSALREC Z

WHERE
-- X.NMHRDCDE='PKZ'
Y.FINISHGD='Y'
AND X.COMPCODE=Z.COMPCODE
AND Y.COMPCODE=Z.COMPCODE
AND X.COMPCODE=Y.COMPCODE
AND Y.USERDEF1=X.NMSOFCDE
AND Y.ITEMCODE=Z.ITEMCODE
AND Z.DOCTDATE BETWEEN NVL(:P11,Z.DOCTDATE) AND NVL(:P12,Z.DOCTDATE)


In this case every row has been duplicated with an NULL NMCODDES field. Check the output-output

I can't understand why there has been an extra empty row for every item. And using an
ELSE
in the above code doesn't make any difference. Because I can assure that in my DATABASE every item has an unique
NMHRDCDE
. SO, even using
ELSE
will not solve my problem.

Could someone help me pinpointing the problem??

Answer

The only way to ignore that rows is:

select * from (/*YOUR QUERY*/) where code is not null;

As WJack wrote your nulls are ruturned when case not matches and it is correct behaviour. Where clause defines which rows will be returned, case defines what will be returned for rows. So your second query returns rows for any X.NMHRDCDE but do not have value for X.NMHRDCDE != 'PKZ'