How to use complex condition in insert into select query in oracle

Question

I want to insert some records with insert into select query in oracle. My condition should be when month of CREATE_DATE in SITA_HOSTS table is equal with month of sysdate - 1. Now my question is what should I write for the where statement? This is my code:

DECLARE
    p_year VARCHAR2(50);
    n_year NUMBER;
    n_month NUMBER;
    j_year VARCHAR2(4);
    j_month VARCHAR2(4);
    c_month NUMBER;
BEGIN
    SELECT TO_CHAR(sysdate, 'YYYY-MM-DD','nls_calendar=persian') INTO p_year FROM dual; --Change sysdate to jalali date
    
    SELECT regexp_substr(p_year,'[^-]+', 1, 1) INTO j_year
    FROM dual; -- Get year of jalalian sysdate
    
    SELECT regexp_substr(p_year,'[^-]+', 1, 2) INTO j_month
    FROM dual;--Get month of jalalian sysdate
    
    n_year := TO_NUMBER(j_year);
    n_month := TO_NUMBER(j_month);
    
    insert into sita_orders(REL_MODULE,REL_ID,CREATE_DATE,AMOUNT,CUSTOMER_ID,PROJECT_ID,ORDER_STATUS,TITLE,YEAR)
    SELECT 1,ID,sysdate,78787878,CUSTOMER_ID,PROJECT_ID,0,HOSTING_TITLE,j_year
    FROM SITA_HOSTS
    WHERE ????;
END;

At the end I should say that my date is Jalali date

Answer 1

Here is one way:

WHERE TRUNC(create_date,'MM') = ADD_MONTHS(TRUNC(SYSDATE,'MM'), -1)

TRUNC(date, 'MM') truncates to midnight on the first day of the month of the date.

Answer 2

It really depends on the content/meaning and data type of create_date within sita-hosts table. In addition to that is the requirement also unclear. Shall the insert also cover hosts that were created a couple of years ago or only the ones created last month.

solution for the hosts created during the last month. With trying to enforce the usage of indexes if there are some.

select <your select list>
 from sita_hosts
where create_date between add_months(trunc(sysdate,'mm')-1) and trunc(sysdate,'mm')
  and create_date < trunc(sysdate,'mm') 

the second where clause will exclude all times that are on the start of this month just at midnight.

Answer 3

Thanks a lot guys. I have written this code and it works fine:

insert into sita_orders(REL_MODULE,REL_ID,CREATE_DATE,AMOUNT,CUSTOMER_ID,PROJECT_ID,ORDER_STATUS,TITLE,YEAR)
  SELECT 1,ID,sysdate,100000,CUSTOMER_ID,PROJECT_ID,0,HOSTING_TITLE,TO_CHAR(sysdate, 'YYYY','nls_calendar=persian')
    FROM SITA_HOSTS
  WHERE
    to_number(TO_CHAR(CREATE_DATE, 'MM','nls_calendar=persian')) <= to_number(TO_CHAR(sysdate, 'MM','nls_calendar=persian') - 1)
    and ID not in (
        select REL_ID from sita_orders where REL_MODULE=1 and YEAR=TO_CHAR(sysdate, 'YYYY','nls_calendar=persian')
    );