I am trying to get rid of duplicate job roles before the data is entered into my actual dimensions table, everytime I run my script I keep getting a missing expression error and im not sure why, any help would be mostly appreciated.
INSERT INTO job_role_dim (
SELECT job_role_id.nextval, DISTINCT job_role_desc
FROM temp_job_role_dim);
You should not have the parentheses around the query, and your distinct is in the wrong place. You're possibly trying to avoid doing this:
INSERT INTO job_role_dim
SELECT DISTINCT job_role_id.nextval,job_role_desc
FROM temp_job_role_dim;
... which doesn't remove the duplicates, but just gives them different sequence values. So you need a subquery:
INSERT INTO job_role_dim
SELECT job_role_id.nextval, job_role_desc
FROM (
SELECT DISTINCT job_role_desc
FROM temp_job_role_dim
);
The inline view finds the distinct/unique values; the outer query then gives a sequence value to each of those.
This is an elaboration on Alex's answer. The better way to write this query is using a list of columns for the select. This is generally a really good idea -- and for other reasons than just the parentheses around the query. So:
INSERT INTO job_role_dim(job_role_id, job_role_desc) -- making up these column names
SELECT job_role_id.nextval, job_role_desc
FROM (SELECT DISTINCT job_role_desc
FROM temp_job_role_dim
) t;
That is, SQL expects the parentheses after the table to contain a list of columns. And, that is how you should write your INSERT statements. The parser gets confused when you include a subquery, because it is expecting a list of columns.
DISTINCTkeyword suggests that you have a different expectation for its effect than the effect it actually has.DISTINCTdoes not qualify individual columns; rather it qualifies the overallSELECTclause, instructing that no duplicate rows be returned.