[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL to remove NULL and return 1 record

Posted on 2014-08-25
1
Medium Priority
?
346 Views
Last Modified: 2014-08-25
Hi,
I’ve a data situation where there is a need to suppress 2 rows into 1. Duplicate rows are due to the way data is stored in 2 separate records for 1 PM code.  Here is an example:
SELECT /*+ FIRST_ROWS(50) */ pmcode
        ,Estimated_Time
       ,Date_reviewed
       ,ORGANIZATION
       ,ppm_class
FROM   cmbc_pmschd_view;

Returns:
PMCODE          ESTIMATED _TIME   DATE_REVIEWED      ORGANIZATION   PPM_CLASS
1234            NULL                  4/17/2014                           ABC                  F123
1234            A                            NULL                                   ABC                  F123

I would like to see is:

PMCODE          ESTIMATED _TIME   DATE_REVIEWED      ORGANIZATION   PPM_CLASS
1234            A                  4/17/2014              ABC                  F123
0
Comment
Question by:mohammadzahid
1 Comment
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40283735
MAX should do it.

select
PMCODE, max(ESTIMATED _TIME), max(DATE_REVIEWED), ORGANIZATION, PPM_CLASS
from cmbc_pmschd_view
group by PMCODE, ORGANIZATION, PPM_CLASS
/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

834 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