Inserting data into table with select

i`m trying to do something…. I have to insert some data in to a table but….. So here is where I end up…

INSERT into HR.my_employees
(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
  SELECT 
    3 AS ID, 
    'Biri' AS LAST_NAME, 
    'Ben' AS FIRST_NAME,
    substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid, 
    1100 AS salary
FROM dual
UNION
  SELECT 
    4 AS ID, 
    'Newman' AS LAST_NAME, 
    'Chad' AS FIRST_NAME,
    substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid, 
    750 AS salary
FROM dual;

any suggestion…

Answer

You cannot refer to an alias in the SELECT or WHERE clauses of a sub-query where it is defined. Generate the data in a sub-query (or a sub-query factoring clause) and then refer to it in an outer query:

INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
WITH data (id, last_name, first_name, salary) AS (
  SELECT 3, 'Biri', 'Ben', 1100 FROM DUAL UNION ALL
  SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
)
SELECT id,
       last_name,
       first_name,
       SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
       salary
FROM   data;

or:

INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
SELECT id,
       last_name,
       first_name,
       SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
       salary
FROM   (
  SELECT 3 AS id, 'Biri' AS last_name, 'Ben' AS first_name, 1100 AS salary FROM DUAL
UNION ALL
  SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
);

fiddle