Query to Merge subsequent rows in Oracle/Teradata

Question

I have a table with data as below

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t                      HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  GID     
IDS TD   SBD    IDS   Data_Val  cust_t  Phone       
IDS TD   SBD    IDS   Data_Val  cust_t  Account     
IDS TD   SBD    IDS   Data_Val  cust_t  Visa        
IDS TD   SBD    IDS   Data_Val  cust_t  Mail        
IDS TD   SBD    IDS   Data_Val  cust_t  Email       
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes 
TDS TD   FDT    TDS   Expense   Exp_t   Name                 LOW
TDS TD   FDT    TDS   Expense   Exp_t           Yes

I want the output as below:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t  GID     Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Phone   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Account Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Visa    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Mail    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Email   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes         HIGH
TDS TD   FDT    TDS   Expense   Exp_t   Name    Yes         LOW

N_identity will have either Yes or No value for a particular column.So far i have tried to use the below query but it's not giving me the desired result:

SELECT * FROM
(
   SELECT * FROM
   (
        SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(HDFT) as HDFT, MAX(N_Identity) as N_Identity, MAX(Class) as Class  
        FROM Table
        GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN
   )a 

  UNION

  SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, HDFT, N_Identity, Class FROM Table

)b

WHERE HDFT IS NOT NULL
AND N_Identity IS NOT NULL
AND Class IS NOT NULL

Updated requirement: The HDFT value can be null and below is one scenario:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT
IDS TD   SBD    IDS   Data_Val  cust_t                      INT
IDS TD   SBD    IDS   Data_Val  cust_t          No

Result Expected:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT

Can you describe in more detail the logic you want to use to determine the values of these derived columns -- `N_Identity` and `Class`? — ravioli, Aug 21 '19 at 07:58
I just want whatever value is populated in N_identity and Class field( which will be only one for one particular column). These values should be populated against the Non null HDFT column and the null HDFT row should be merged/removed. — user3901666, Aug 21 '19 at 08:03
Your actual expected output is not clear. The first output in your description is contradicting with the second output you have mentioned in the update section. Can you give one example with all scenarios? — Isuri Subasinghe, Aug 22 '19 at 02:06

Barbaros Özhan · Accepted Answer · 2019-08-25T17:52:48.233

2

I suppose you need nvl() and first_value() analytic functions only by considering to filter hdft is not null at the last step (after subquery operation is finished by those functions ) :

with tab2 as
(    
select db, dbms, inst, schema, "table", "column", hdft,
       first_value(class) over (partition by db) as class, 
       nvl(N_Identity,'Yes') as N_Identity 
  from tab                    
)
select * from tab2 where hdft is not null;

Demo

P.S. Avoid using preserved keywords for naming of table or columns such as table, column.

edited Aug 25 '19 at 17:52

answered Aug 21 '19 at 08:05

Barbaros Özhan

59,113
10
31
55

I ran it on SQL server by adding the below: over (partition by "database" order by "database" desc) as order by is required there, but the class value is coming as NULL for all rows. Also, I couldn't get why "Yes" is hardcoded in nvl as the value can also be NO – user3901666 Aug 21 '19 at 08:40
but you @user3901666 tagged the question by `oracle`. Secondly, I cannot know the logic behind,which you should elaborate, especially for `N_Identity` column. – Barbaros Özhan Aug 21 '19 at 08:44
Sorry for the confusion, but i can change that to sql server so its not an issue. Also, the HDFT field can be null as per my data. So current query will not pick that. – user3901666 Aug 21 '19 at 10:00
1

your solution worked perfect for me. Can you please upvote my question. I am serving a question ban now. – user3901666 Dec 20 '19 at 07:24

score 0 · Answer 2 · answered Aug 21 '19 at 07:51

0

select db,dbms,inst,schema,table,column,hdft,
case when N_identity>0 then N_Identity else 'Yes' end as N_identity
,case when class>0 then class 
when column='cust_t' then 'HIGH'
when column='Exp_t' then 'LOW' end as Class
from table
where hdft>0

answered Aug 21 '19 at 07:51

1

This is just a sample data and i need a generic query for it. Your query is taking hard coded values which i don't require – user3901666 Aug 21 '19 at 08:01

score 0 · Answer 3 · answered Aug 21 '19 at 08:53

0

You can use first_value function like this:

with selection as
(    
select db, dbms, inst, scheme, table1, column1, hdft,
       first_value(Class1) over (partition by db order by Class1) as "class1", 
       first_value(N_Identity) over (partition by db order by N_Identity)  as "N_Identity"
  from Y                 
)
select * from selection where hdft is not null;

answered Aug 21 '19 at 08:53

Isuri Subasinghe

117
9

The HDFT field can be null. Please see the updated description of the request. – user3901666 Aug 21 '19 at 10:01

Query to Merge subsequent rows in Oracle/Teradata

3 Answers3