I want insert data from a table with random in two column I tried but failed

user_15377265 0
Viewed 33

I have two table from table pay_in_out I want to insert data in advance salary and selecting random from table but failed any one can tell me please

INSERT INTO  ADVANCE_SALARY (EMPLOYEECODE,ADV_DATE)
    VALUES (SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40
AND ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY');

I tried also like this but failed

INSERT INTO  ADVANCE_SALARY (EMPLOYEECODE, ADV_DATE)
    SELECT    
     EMPLOYEECODE,
     ADV_DATE
     FROM 
     (SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    WHERE ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY'
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40;

it says adb_date invalid identifier but select query works fine

SELECT EMP_CODE,ATT_DATE
FROM   (
    SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 40
AND ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY';
oracle sql
2 Answers

ok i got this its like this

INSERT INTO  ADVANCE_SALARY (EMPLOYEECODE, ADV_DATE)
    SELECT    
     EMP_CODE,
     ATT_DATE
     FROM 
     (SELECT EMP_CODE,ATT_DATE
    FROM   PAY_IN_OUT
    ORDER BY DBMS_RANDOM.RANDOM)
    WHERE ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
    AND  ((TO_CHAR(ATT_DATE, 'DAY'))) != 'SUNDAY'
    AND  rownum < 40;
user_153772650

If you are flexible about the 39 rows, you could think of using SAMPLE, which is somewhat cleaner and faster:

INSERT INTO ADVANCE_SALARY (EMPLOYEECODE, ADV_DATE)
SELECT EMP_CODE, ATT_DATE
  FROM pay_in_out SAMPLE (5)
 WHERE ATT_DATE BETWEEN DATE '2020-09-01' AND DATE '2021-03-31'
   AND ((TO_CHAR(ATT_DATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH'))) != 'SUNDAY';

You'll need to adjust the sample size (here 5%) to your data volume.

user_18640290
Related