CREATE PROCEDURE UNIFY_METRO_DATA (
UNIFY_DATE DATE DEFAULT '2016-03-03'
)

The BEGIN

VAR_METRO_RAW_TABLE=
SELECT
CASE the WHEN ALIASCARDNO LIKE '0000%' THEN the SUBSTRING (ALIASCARDNO, 5) ELSE ALIASCARDNO END AS CARD_NO,
CARDCLASS AS CARD_TYPE
The FROM
"CC_METRO_EXT
"WHERE
TO_DATE (GETOFFDATETIME)=: UNIFY_DATE
AND
ONSTATIONID!=STATIONID;

VAR_METRO_RESULT=
SELECT
CARD_NO,
COUNT (CARD_NO) AS METRO_COUNT,
MAX (CARD_TYPE) AS CARD_TYPE
FROM: VAR_METRO_RAW_TABLE
GROUP BY CARD_NO;

VAR_FINAL_RESULT=
SELECT
T1 CARD_NO,
T1 METRO_COUNT,
T1 CARD_TYPE,
T2. IDCARDNO
FROM: VAR_METRO_RESULT AS T1
LEFT the JOIN "CC_MAP_LITE_EXT AS T2
"ON
T1. CARD_NO=T2. ALIASCARDNO
AND
T2. CHOOSEFLAG=1;

The DELETE FROM "CC_EVERYDAY_METRO_RECORDS
"WHERE COLLECT_DATE=: UNIFY_DATE;

INSERT INTO "CC_EVERYDAY_METRO_RECORDS
"SELECT
CARD_NO AS ALIASCARDNO,
CARD_TYPE AS CARD_CLASS,
IDCARDNO,
METRO_COUNT AS SUBWAY_TIMES,
TO_DATE (: UNIFY_DATE) AS COLLECT_DATE
FROM: VAR_FINAL_RESULT;
END;

CodePudding user response:

You this is not a stored procedure, the stored procedure inside want to get married, assigned to a variable should be use the select col variables into the from! Remember a single variable, and found out wedding column should be a line

CodePudding user response:

reference 1st floor biubiu ash response:

you that this is not a stored procedure, the stored procedure inside want to get married, assigned to a variable should be used to select col variables into the from! Remember a single variable, and found out wedding column should be a line oh

The stored procedure is a I from hana database sprocket, and put into use in oracle database, then looked at it a few SQL but don't know how to change the

CodePudding user response:

refer to the second floor BABYVAF response:

Quote: refer to 1st floor biubiu ash response:

you that this is not a stored procedure, the stored procedure inside want to get married, assigned to a variable should be used to select col variables into the from! Remember a single variable, and found out wedding column should be a line oh

The stored procedure is a I from hana database sprocket, and put into use in oracle database, then looked at it a few SQL but don't know how to change the

SAP? You select into look at baidu