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.
Vinoy K PAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
If i remove the CASE Statement from the select query i am getting only one row.
do you mean removed below as a whole:

(CASE WHEN (MA.FIELD_NAME = 'UTILITIES_INCLUDED' )  THEN MA.FIELD_VALUE END ) "UTILITIES_INCLUDED",

Open in new window


or make it like below?

MA.FIELD_VALUE "UTILITIES_INCLUDED",

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.
Vinoy K PAuthor Commented:
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 ? .
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vinoy K PAuthor Commented:
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_INFO EMP ,WCTS.BLT_GFORMS_LOAN_DATA LOD,
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this and see what you getting?

SELECT  DISTINCT BO.BORROWER_AGE ,BO.BORROWER_FIRST_NAME, L_POS_TITLE_TYPE_OF_BUS ,
LOD.FIELD_NAME, LOD.FIELD_VALUE,
--(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_INFO EMP ,WCTS.BLT_GFORMS_LOAN_DATA LOD,
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;
PortletPaulEE Topic AdvisorCommented:
"select distinct" operates across the ENTIRE row, so as you increase the number of columns the probability that you get more rows increases because each column can help produce different, yet still unique, rows of data. The reason you get 2 rows is because in the column "UTILITIES_INCLUDED" 1 is distinct from NULL = one row for each.

e.g.  
this query probably produces only a few rows
SELECT  DISTINCT BO.BORROWER_AGE FROM WCTS.BORROWER BO

but this query produces more rows because many people of the same age will have different names
SELECT  DISTINCT BO.BORROWER_AGE ,BO.BORROWER_FIRST_NAME

I suspect that the table  WCTS.BLT_GFORMS_LOAN_DATA has more rows per loan than you need for this query, so you could try something like this:
SELECT DISTINCT
  BO.BORROWER_AGE,
  BO.BORROWER_FIRST_NAME,
  L_POS_TITLE_TYPE_OF_BUS,
  LOD.FIELD_NAME,
  --LOD.FIELD_VALUE,
  LOD.FIELD_VALUE  AS "UTILITIES_INCLUDED",
  L_MONTHLY_HOUSE_EXPENSE,
  L_POS_TITLE_TYPE_OF_BUS,
  L_YRS_ON_THIS_JOB

FROM WCTS.BORROWER BO
INNER JOIN WCTS.LOANS LO ON BO.LOAN_ID = LO.LOAN_ID
INNER JOIN BLTUW.BLT_1003_EMPLOYER_INFO EMP ON LO.LOAN_ID = EMP.LOAN_ID
LEFT JOIN WCTS.BLT_GFORMS_LOAN_DATA LOD ON LO.LOAN_ID = LOD.LOAN_ID
                                      AND LOD.FIELD_NAME = 'UTILITIES_INCLUDED'
WHERE 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;

Open in new window

Note, over 25 years ago it was decided that "best practice" in SQL is to use "explicit joins". Please take the time to adopt this practice. It helps if you just stop using commas between table names, that will help force you to consider how the tables should be joined.
PortletPaulEE Topic AdvisorCommented:
by the way...
There is a difference between "case statement" and "case expression" in Oracle. A select query allows use of "case expressions".

"Case expressions" are part of standard SQL
https://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions004.htm#SQLRF20037

"Case statements" allow for branching in PL/SQL
https://docs.oracle.com/cloud/latest/db112/LNPLS/case_statement.htm#LNPLS01304
Geert GOracle dbaCommented:
PortletPaul,
Note, over 25 years ago it was decided that "best practice" in SQL
that discussion hasn't been decided yet, at least not everywhere

and if it would have been decided, then oracle wouldn't support the commas anymore
not a lot people like the left and right join syntax, they prefer to stay in the "middle"

people should use what they are comfortable with

Vinoy,
nothing wrong with using comma's

but in your case, you don't have an "else part"
and you don't need to enclose a case in () brackets, neither the expressions
CASE WHEN MA.FIELD_NAME = 'UTILITIES_INCLUDED' THEN MA.FIELD_VALUE ELSE 0 END "UTILITIES_INCLUDED",

If you run it with the else 0 you'll see where ma.field_name is not UTILITIES_INCLUDED

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Starting an Oracle query like this: "SELECT  DISTINCT ..." often leads to problems.  Including "distinct" there is usually not the best way to get the job done in Oracle.  It is usually better to include additional "where" clause conditions and/or a "group by" clause instead of using "distinct".

And, if you have null values in the data, usually the simplest way to handle them is by using the "nvl" operator to substitute a valid, not null value instead of the nulls.
Vinoy K PAuthor Commented:
Thanks for every ones help .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.