Orcel stored procedure, run once a day, the log shows the successful running every day, but don't know why the last two days of data not update each month, why? To know the great god help answer, thank you thank you!

A, the first variable is set as follows:
V_STARTPRO_TIME DATE;
V_START_DATE DATE;
V_SQL VARCHAR2 (1000);
V_ERROR_MSG VARCHAR2 (300); - the error message

The BEGIN
V_STARTPRO_TIME:=SYSDATE;
V_START_DATE:=TRUNC (SP_STARTDATE);
The SELECT MDATE1
INTO V_START_DATE
The FROM DW_BI. DIM_TIME
WHERE TDATE=V_START_DATE;

V_SQL:='TRUNCATE TABLE FACT_CRM_DEAL_RATE_TMP';
The EXECUTE IMMEDIATE V_SQL;
COMMIT;

Second, run the following data:
INSERT INTO FACT_CRM_DEAL_RATE_TMP
WITH RIQI AS
(SELECT MIN (D.M DATE1_STAT) STA_DATE, MAX (D.M DATE1_END) END_DATE
The FROM DIM_TIME D
WHERE D.M DATE1=V_START_DATE)

The SELECT e.c. with our fabrication: USTOMER_NO, D.M DATE1
The FROM ODS_CRM. TEMP_JC_ENQUIRY E
LEFT the JOIN DIM_TIME D
ON TRUNC (e.c. with our fabrication: REATE_TIME)=which DATE
WHERE e.c. with our fabrication: REATE_TIME & gt;=(SELECT STA_DATE FROM RIQI)
AND e.c. with our fabrication: REATE_TIME & lt;=(+ 1 FROM the SELECT END_DATE RIQI)
AND E.E NQUIRY_STATE='5');
Commit;


Three, the parameter at the end of the following:

- delete a formal table data during the month
The DELETE FROM FACT_CRM_DEAL_RATE WHERE A arjun DATE1=V_START_DATE;

COMMIT;

- insert temporary table data to formal table
INSERT INTO FACT_CRM_DEAL_RATE
SELECT * FROM FACT_CRM_DEAL_RATE_TMP;
COMMIT;

CodePudding user response:

Estimation is the data that has a problem, you didn't post all,

You can simulate was the last 2 days of the month, to try to get a stored procedure except finally remove data and insert the data try other code to run again, and see why there is no data in the temporary table, with the function assignment system date place, write the date value directly to death,

CodePudding user response:

Thank you for your answer, but running part of the code of text data here, when will the date die, single query is no problem, for a whole month in normal data and two days after a month of data can be the result of the query to the right, but the query table generated the storage process, because it is according to the summary of the month, query out always lack of two days after a month in the monthly summary data;
So I doubt it's time to run a stored procedure, a month after the two days have not run the stored procedure, but the log table also shows the successful running inside, is this why?

CodePudding user response:

Haven't used ORACLE, don't know if you run the stored procedure is invoked by homework, or other methods, is called trigger has a problem,