CODEWITHSUNDEEP
phpsql
MANGAHAS
MANGAHAS

Reputation: 7

How can I create view in SQL without duplicate entries?

First I have Table SUM_REPANDMAINT, Table`s Column name:

ACTUAL_COST, PROJECTED_COST, TOTAL_PREV, TOTAL_PRESENT, VARIANCE, VARIANCE_PER, LIMIT, COST_CENTER, ID, PLANT, BUDGET_YEAR, VERSION,

Second I have View V_SUM_REPANDMAINT, View`s Column name:

USERNAME, CHARGE_CC, BUDGET_YEAR, VERSION, PLANT, CHARGE_CC, PART_DESC, YEAR_DATE_USD,

This images may help understand the issue:

enter image description here

enter image description here

I need to get non repeating values.

Note: The sum of YEAR_DATE_USD is based on PLANT AND CHARGE_CC

My Query:

SELECT 
   A.ACTUAL_COST,
   A.PROJECTED_COST,
   A.TOTAL_PREV,
   A.TOTAL_PRESENT,
   A.VARIANCE,
   A.VARIANCE_PER,
   A."LIMIT",
   A.COST_CENTER,
   A.PLANT,
   A.BUDGET_YEAR,
   A."VERSION",
   B.BUDGET_YEAR,
   B."VERSION",
   B.PLANT,
   B.CHARGE_CC,
   B.PART_DESC,
   B.YEAR_DATE_USD
FROM
   CMS.SUM_REPANDMAINT A,
   CMS.V_SUM_REPANDMAINT B

WHERE 
   (A.BUDGET_YEAR = B.BUDGET_YEAR(+)) AND 
   (A."VERSION" = B."VERSION"(+)) AND 
   (A.PLANT = B.PLANT(+)) AND 
   (A.COST_CENTER = B.CHARGE_CC(+)) AND
   (B.USERNAME = '[usr_name]')

Upvotes: 1

Views: 2159

Answers (2)

chorn
chorn

Reputation: 150

Try using GROUP BY or DISTINCT on your unique columns. This will group some results.

Documentation here

Upvotes: 3

kgautron
kgautron

Reputation: 8293

If you have duplicates it means that :

  • either you are not joining on all the required columns (need to add a statement to the WHERE clause for these columns)
  • or it is normal for you to have multiple rows, in that case you need to aggregate them using a GROUP BY clause

Upvotes: 1

Related Questions