Getting a unique constraint error on insert into new table

I have the following new table
ps_tr_ac_fmt_tbl
COUNTRY         KEY
COUNRY_CODE     KEY
AREA_CODE       KEY
DESC
PHONE_FORMAT

I receive a unique constraint error on the following insert

INSERT INTO ps_tr_ac_fmt_tbl
SELECT a.country,
      b.country_code,
      c.cg_area_code,
      c.descr,
      'Free Form'
FROM   ps_country_tbl@cehrdvl1 a,
      ps_cg_cntrydil_tbl@cehrdvl1 b,
      ps_state_tbl@cehrdvl1 c
WHERE  a.country = b.country
      AND a.country = c.country
      AND c.cg_area_code <> ' '
      AND effdt = (SELECT Max(effdt)
                   FROM   ps_cg_cntrydil_tbl@cehrdvl1
                   WHERE  b.country = country
                          AND b.country_code = country_code)
      AND b.eff_status = 'A');

The reason is that the  ps_state_tbl has the following keys
country
state

Within the country, state on this table there are multiple entries for a description field.
(I.E. USA NY test
        USA CT test )
Therefore I am bring back 2 rows for country, country_code and area_code into my new table
Is there anyway to combine the entries in the ps_state_tbl by country / description so I only
bring back 1 row into my insert ?

thanks in advance
Dennis
dmisselAsked:
Who is Participating?
 
ravindran_eeeConnect With a Mentor Commented:
There is no state column in your ps_tr_ac_fmt_tbl table. What are the columns that are part of the unique constraint in the ps_tr_ac_fmt_tbl and the ps_state_tbl tables?
0
 
htonkovConnect With a Mentor Commented:
Question is unclear, but instead of

ps_state_tbl@cehrdvl1 c

Open in new window


you might want to join with

(select distinct country, cg_area_code  ps_state_tbl@cehrdvl1 ) c

Open in new window


or

(select distinct country, max(cg_area_code) cg_area_code  ps_state_tbl@cehrdvl1 group by country) c

Open in new window


or something similar (or most probably - change the logic of query)

(Question is also is this thread still alive after 3 months on inactivity)
0
 
dmisselAuthor Commented:
thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.