Oracle query to avoid duplicates

Question

0.00/5 (No votes)

See more:

Hi Team,

Table1

AccNo	TRNO	AMOUNT
123	123	100
1234	1234	50
12345	12345	50

Table2
AccNO	CODE
123	A
123	I
1234	A
12345	I

Need the O/P like below

AccNo	TRNO	AMOUNT
123	123	100
1234	1234	50
12345	12345	50

Need to join the 2 tables and fetch output as mentioned.

What I have tried:

SELECT DISTINCT(AccNo),TRNO,AMOUNT 
FROM Table1 T1 JOIN
Table2 T2 ON T1.AccNo=T2.AccNo

Posted 31-Jul-18 1:43am

Member 11658469

Updated 31-Jul-18 3:31am

Add a Solution

Comments

CHill60 31-Jul-18 9:26am

And what is wrong with the code you have just posted?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-07-31T03:32:00

Solution 1

Ok, the only problem I can find with your code is that the column AccNo in DISTINCT(AccNo) is ambiguous.

In other words, Oracle cannot determine whether you meant the AccNo in Table1 or the one in Table2.

So just let Oracle know which one to use. E.g.

SQL

SELECT DISTINCT(T1.AccNo),TRNO,AMOUNT

Posted 31-Jul-18 3:32am

CHill60

Comments

Member 11658469 1-Aug-18 4:21am

Hi
My Actual Code is copied below.
SELECT DISTINCT UPPER(PAYMENTLOCATION) AS PAYMENTLOCATION,UPPER(CREDITCARDTYPE) AS CREDITCARDTYPE,
CASE WHEN UPPER(PM.PAYMENTLOCATION) IN ('SHAMELSIMSIM','AUB','AUBSIMSIM','BSB','BBKSIMSIM','HSB','KHB','KHBSIMSIM','NBB','NBBSIMSIM','SBB','SCBSIMSIM','BBK') THEN
CASE WHEN COUNT(T4.SERVICENO) OVER (partition by '973' || PM.ACCOUNT_NUMBER) > 0
THEN COUNT(PM.TRANSACTION_NUMBER) OVER (PARTITION BY UPPER(PM.PAYMENTLOCATION) ,UPPER(PM.CREDITCARDTYPE))
ELSE 0 END ELSE 0 END AS DB_SIMSIM_COUNT,
CASE WHEN UPPER(PM.PAYMENTLOCATION) IN ('SHAMELSIMSIM','AUB','AUBSIMSIM','BSB','BBKSIMSIM','HSB','KHB','KHBSIMSIM','NBB','NBBSIMSIM','SBB','SCBSIMSIM','BBK') THEN
CASE WHEN COUNT(T4.SERVICENO) OVER (partition by '973' || PM.ACCOUNT_NUMBER) > 0
THEN SUM(PM.AMOUNT) OVER (PARTITION BY PM.PAYMENTLOCATION,UPPER(PM.CREDITCARDTYPE))
ELSE 0 END ELSE 0 END AS DB_SIMSIM_SUM
FROM PAYMENTS_LOG_ALL_V PM
INNER JOIN BSS_DATA.BSS_ASSET_INFO T4 ON '973' || PM.ACCOUNT_NUMBER = T4.SERVICENO AND T4.Assettype = 'Mobile_Prepaid' AND T4.ASSET_STATUS = 'Active' AND PM.BILL_PROFILE_ID IS NULL
WHERE UPPER(PM.PAYMENTLOCATION) IN ('SHAMELSIMSIM','AUB','AUBSIMSIM','BSB','BBKSIMSIM','HSB','KHB','KHBSIMSIM','NBB','NBBSIMSIM','SBB','SCBSIMSIM','BBK')
AND T4.Assettype = 'Mobile_Prepaid'

Here in the Table T4 one column CODE which will come 1 or 2 for the same SERVICENO in same table.Due to this code appear twicw we cant fetch the correct datas(SUM) using the query.Means for some SERVICENO if 2 codes with are there twice the transaction count & sum is taking.I need to avoid the second entry.If the SERVICENO is same for the CODES then once the sum will taken based on same condition in the query.
Please help

CHill60 1-Aug-18 5:03am

Why not just include in the WHERE clause

AND T4.SERVICENO = 1

or am I missing something?