How to remove redundant data from the DML

Question

MDW_BILL1 has the following columns

BILL_ID NOT NULL VARCHAR2(10)
CUSTOMER_ID VARCHAR2(10)
MONTH VARCHAR2(10)
YEAR NUMBER
TOTAL NUMBER
ACTIVE_INDICATOR VARCHAR2(10)

MDW_BILL_TRANSACTION1 has the following columns:

BILL_TRANSACTION_ID NOT NULL VARCHAR2(10)
BILL_ID VARCHAR2(10)
ACCOUNT_ID VARCHAR2(10)
TRANSACTION_TYPE VARCHAR2(20)
COST NUMBER
ACTIVE_INDICATOR VARCHAR2(10)

This is the query that I am trying to execute:

select 
    b.ACCOUNT_ID, b.TRANSACTION_TYPE, b.cost 
from 
    mdw_bill1 a, mdw_bill_transaction1 b 
where 
    a.CUSTOMER_Id='CUS0033' 
    and a.month='JUN' and a.year=2013;

The o/p is:

ACCOUNT_ID TRANSACTION_TYPE           COST
---------- -------------------- ----------
AID0159    Transport Cost              182
AID0159    Meal Cost                  1300
AID0159    Subscription Cost           120
AID0161    Transport Cost              168
AID0161    Meal Cost                   840
AID0161    Subscription Cost           240
AID0160    Transport Cost           203.58
AID0160    Meal Cost                   650
AID0160    Subscription Cost           400
AID0164    Transport Cost           187.92
AID0164    Meal Cost                   720
AID0164    Subscription Cost           480

How can i remove the repeating ACCOUNT_ID and display one account_id for each set of billing details

I modified the code to

select b.ACCOUNT_ID, b.TRANSACTION_TYPE, b.cost from mdw_bill1 a
    inner join mdw_bill_transaction1 b
    on a.bill_id = b.bill_id
    where a.CUSTOMER_Id='CUS0033'
    and a.month='JUN' and a.year=2013;

but i still get the same result

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) — marc_s, Jun 08 '13 at 12:29

score 1 · Accepted Answer · answered Jun 08 '13 at 22:49

The solution to this is to enumerate the rows that are being output and to only include the account id on the first row in each group:

select (case when seqnum = 1 then Account_Id else '' end),
       Transaction_type, cost
from (select b.ACCOUNT_ID, b.TRANSACTION_TYPE, b.cost,
             row_number() over (partition by account_id
                                order by b.bill_transacction_id) as seqnum
      from mdw_bill1 a, mdw_bill_transaction1 b 
      where a.CUSTOMER_Id='CUS0033'and a.month='JUN' and a.year=2013
     ) t
order by Account_id, seqnum

Some comments. You should give your tables sensible aliases, like b for mdw_bill1 and bt for mdw_bill_transaction. Your original query did not have an order by clause; you need that to guarantee the ordering of the rows -- no guarantee that one account will always be on adjacent lines. Third, joining two tables this way looks . . . shall I say awkward. I think you intend for the conditions to be:

      from mdw_bill1 a join
           mdw_bill_transaction1 b 
           on a.bill_id = b.bill_id
      where a.CUSTOMER_Id='CUS0033'and a.month='JUN' and a.year=2013

How to remove redundant data from the DML

1 Answers1