?
Solved

Returning values depending on data in column

Posted on 2011-02-11
6
Medium Priority
?
415 Views
Last Modified: 2012-05-11
I have a query in oracle..looking at returning a numeric value of 1 if a column is not NULL and 0.
some folks said that it would be similar to SQL but my query doesn't work.

So the value I want to change is Col2.  Set to 1 if the there is anything in Col2, and 0 if it is NULL.

Select
Col1,
If Col2 is NOT NULL THEN 1
Else 0 NewColName,
Col3,
From Database.
0
Comment
Question by:TechMonster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34875838
Select col1, case when col2 is not null then 1 else 0 end mycol from table;
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34875941
Select col1, decode(col2,null,0,1) mycol from table;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34875948
Decode is still valid but is being phased out.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Accepted Solution

by:
sdstuber earned 1500 total points
ID: 34876677
the NVL2 function does exactly the if null else check

Select col1, nvl2(col2,1,0) from table;
0
 

Author Closing Comment

by:TechMonster
ID: 34876851
This is the solution I used...thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34876931
why the B?  that's a penalty grade,  what more did you want for an A?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question