Left Join with Distinct Clause

Question

Below is my insert query.

INSERT INTO /*+ APPEND*/ TEMP_CUSTPARAM(CUSTNO, RATING)
SELECT DISTINCT Q.CUSTNO, NVL(((NVL(P.RATING,0) * '10.0')/100),0) AS RATING
FROM TB_ACCOUNTS Q LEFT JOIN TB_CUSTPARAM P
ON P.TEXT_PARAM IN (SELECT DISTINCT PRDCD FROM TB_ACCOUNTS)
AND P.TABLENAME='TB_ACCOUNTS' AND P.COLUMNNAME='PRDCD';

In the previous version of the query, P.TEXT_PARAM=Q.PRDCD but during insert to TEMP_CUSTPARAM due to violation of unique constraint on CUSTNO.

The insert query is taking ages to complete. Would like to know how to use distinct with LEFT JOIN statement.

Thanks.

Practice with the SELECT part. (And when finished do the INSERT with SELECT.) Can you add some sample table data and the SELECT's expected result? (As well formatted text.) — jarlh, Feb 21 '17 at 07:07
Change that inner Select into Inner join and you should be good to go — Veljko89, Feb 21 '17 at 07:12
Any chance that you missing a predicate on Q.CUSTNO in the ON clause? There is no column from the table Q referenced there.... — Marmite Bomber, Feb 21 '17 at 07:24
1. You shouldn't use `DISTINCT` inside `IN`. 2. You shouldn't use string literals for numbers (`'10.0'`). 3. `DISTINCT` is something rarely needed; are you sure there can be duplicate entries for a customer rating? — Thorsten Kettner, Feb 21 '17 at 07:44
Yes there are duplicate entries, because TB_ACCOUNTS contain duplicate customer numbers due to one customer having multiple accounts. — DeE DEe, Feb 21 '17 at 07:52

score 7 · Answer 1 · edited Feb 21 '17 at 07:14

7

 SELECT T1.Col1, T2.Col2 FROM  Table1 T1
  Left JOIN 
(SELECT Distinct Col1, Col2 FROM Table2
) T2 ON T2.Id = T1.Id

edited Feb 21 '17 at 07:14

answered Feb 21 '17 at 07:07

Singh Kailash

621
5
16

score 0 · Answer 2 · answered Feb 21 '17 at 07:29

You are missing criteria to join TB_ACCOUNTS records with their related TB_ACCOUNTS/PRDCD TB_CUSTPARAM records and thus cross join them instead. I guess you want:

INSERT INTO /*+ APPEND*/ TEMP_CUSTPARAM(CUSTNO, RATING)
SELECT DISTINCT 
  Q.CUSTNO, 
  NVL(P.RATING, 0) * 0.1 AS RATING
FROM TB_ACCOUNTS Q 
LEFT JOIN TB_CUSTPARAM P ON  P.TEXT_PARAM = Q.PRDCD
                         AND P.TABLENAME = 'TB_ACCOUNTS' 
                         AND P.COLUMNNAME = 'PRDCD';

score 0 · Answer 3 · edited May 23 '17 at 10:29

If the query is taking ages to complete, check first the execution plan. You may find some hints here - If you see a cartesian join on two non-trivial tables, probably the query should be revisited.

Than ask yourself what is the expectation of the query.

Do you expect one record per CUSTNO? Or can a customer have more than one rating?

One reting per customer could have sense from the point of business. To get unique customer list with rating

1) first get a UNIQUE CUSTNO - note that this is in generel not done with a DISTINCT clause, but if tehre are more rows per customer with a filter predicate, e.g. selecting the most recent row.

2) than join to the rating table

Left Join with Distinct Clause

3 Answers3