Vinoy K P
asked on
Avoid the NULL in the result set while using the CASE Statement in ORACLE
hi How i can avoid the NULL in the result set while using the CASE Statement in ORACLE.
(CASE WHEN (MA.FIELD_NAME = 'UTILITIES_INCLUDED' ) THEN MA.FIELD_VALUE END ) "UTILITIES_INCLUDED",
I suppose to get only one row of data but its bringing two rows but one with NULL.
44 DAVID data Analyst III (null) 1000 7
44 DAVID data Analyst III 1 1000 7
If i remove the CASE Statement from the select query i am getting only one row.
I dont want to add condition in the WHERE Clause because there are other CASE Statements as well. Please help. Thanks.
(CASE WHEN (MA.FIELD_NAME = 'UTILITIES_INCLUDED' ) THEN MA.FIELD_VALUE END ) "UTILITIES_INCLUDED",
I suppose to get only one row of data but its bringing two rows but one with NULL.
44 DAVID data Analyst III (null) 1000 7
44 DAVID data Analyst III 1 1000 7
If i remove the CASE Statement from the select query i am getting only one row.
I dont want to add condition in the WHERE Clause because there are other CASE Statements as well. Please help. Thanks.
ASKER
when i am removing entire CASE Statement
(CASE WHEN (MA.FIELD_NAME = 'UTILITIES_INCLUDED' ) THEN MA.FIELD_VALUE END ) "UTILITIES_INCLUDED",
i am getting only one row .
If So i believe the WHERE clause is fine right ? .
(CASE WHEN (MA.FIELD_NAME = 'UTILITIES_INCLUDED' ) THEN MA.FIELD_VALUE END ) "UTILITIES_INCLUDED",
i am getting only one row .
If So i believe the WHERE clause is fine right ? .
If So i believe the WHERE clause is fine right ? .I don't know how you construct your whole SQL statement but in general, we use WHERE to filter records, in your case, I guess it should be used.
ASKER
SELECT DISTINCT BO.BORROWER_AGE ,BO.BORROWER_FIRST_NAME, L_POS_TITLE_TYPE_OF_BUS ,
--(CASE WHEN LOD.FIELD_NAME = 'UTILITIES_INCLUDED' THEN LOD.FIELD_VALUE END)"UTILITIES_INCLUDED",
L_MONTHLY_HOUSE_EXPENSE , L_POS_TITLE_TYPE_OF_BUS
,L_YRS_ON_THIS_JOB
FROM WCTS.BORROWER BO, BLTUW.BLT_1003_EMPLOYER_IN
WCTS.LOANS LO WHERE
LO.LOAN_ID = BO.LOAN_ID AND
EMP.LOAN_ID = LO.LOAN_ID AND
LOD.LOAN_ID = LO.LOAN_ID AND
L_POS_TITLE_TYPE_OF_BUS IS NOT NULL AND
LO.LOAN_ID = 401000791 AND BO.BORROWER_FIRST_NAME = 'DAVID' AND LOD.DOCUMENT_ID = 3;
Table Data
------------------
401000791 3 UTILITIES_INCLUDED 1 -- There is no null value in the table at all.
--(CASE WHEN LOD.FIELD_NAME = 'UTILITIES_INCLUDED' THEN LOD.FIELD_VALUE END)"UTILITIES_INCLUDED",
L_MONTHLY_HOUSE_EXPENSE , L_POS_TITLE_TYPE_OF_BUS
,L_YRS_ON_THIS_JOB
FROM WCTS.BORROWER BO, BLTUW.BLT_1003_EMPLOYER_IN
WCTS.LOANS LO WHERE
LO.LOAN_ID = BO.LOAN_ID AND
EMP.LOAN_ID = LO.LOAN_ID AND
LOD.LOAN_ID = LO.LOAN_ID AND
L_POS_TITLE_TYPE_OF_BUS IS NOT NULL AND
LO.LOAN_ID = 401000791 AND BO.BORROWER_FIRST_NAME = 'DAVID' AND LOD.DOCUMENT_ID = 3;
Table Data
------------------
401000791 3 UTILITIES_INCLUDED 1 -- There is no null value in the table at all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for every ones help .
Open in new window
or make it like below?
Open in new window
since your data returned different values for MA.FIELD_NAME and it depends on how your SQL statement is written, there are propensity it will return more than 1 row.
so you need to somehow filter those records that not necessary, hence a WHERE condition may need to be added somewhere in your SQL statement.