Data extraction


I am extracting some data from the database.

Theses are four fields i.e. value field1,value field2,value field3,value field4 and the fifth field just carries the unit.

I want that if the value fields are blank it should not report anything. In this case it reports a unit.

And if the value field has a value it should report the value as well as the unit.

How to do it?

  value field1||'~'||unit field ||','||
  value field2  ||'~'||unit field ||','||
   value field3  ||'~'||unit field ||','||
    value field4 ||'~'||unit field ||','||
    unit field||','||
Who is Participating?
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.

RindbaekSenior ConsultantCommented:
if you want to exclude rows where values 1 to 4 all are empty (null) add a where clause

value field 1 is not null AND
value field 2 is not null AND
value field 3 is not null AND
value field 4 is not null
AmmarDJAuthor Commented:
Rindbaek can i use a where clause in a script like the one attached.
RindbaekSenior ConsultantCommented:
you already have one...

WHERE system = '77';

WHERE system = '77' AND
value field 1 is not null AND
value field 2 is not null AND
value field 3 is not null AND
value field 4 is not null ;
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.

johnsoneSenior Oracle DBACommented:
The where clause will exclude rows where all the values are null.  If there is a case where only some of them are null,  you can try something like this:

nvl2(value field1, value field1||'~'||unit field ||',', null) ||
nvl2(value field2, value field2||'~'||unit field ||',', null) ||
nvl2(value field3, value field3||'~'||unit field ||',', null) ||
nvl2(value field4, value field4||'~'||unit field ||',', null) ||

Open in new window

You could also rewrite using case statements like this:

case value field1 when not null then value field1||'~'||unit field end ||
case value field2 when not null then value field2||'~'||unit field end ||
case value field3 when not null then value field3||'~'||unit field end ||
case value field4 when not null then value field4||'~'||unit field end ||

Open in new window


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
AmmarDJAuthor Commented:

Thanks for the feedback.

regarding the nvl function, sounds a a good solution.

But i guess it will report a "null" if there is a null value. What if i just want to report a blank and not "null"

johnsoneSenior Oracle DBACommented:
Change the null at the end of the nvl2 statement to anything you want.  Since you are doing string concatenation, if it is null, there is nothing to concatenate.
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
Oracle Database

From novice to tech pro — start learning today.