NEW OPEN SQL CONTENTS 1 NEW OPEN SQL

NEW OPEN SQL

CONTENTS 1. NEW OPEN SQL? 1 1 -1. OPEN SQL vs NEW OPEN SQL 1 -2. NATIVE SQL vs NEW OPEN SQL 2. SQL 연산자? 2 -1. DIV vs MOD 2 2 -2. CEIL vs FLOOR 2 -3. COALESCE, ABS 2 -4. CASE, CAST 2 -5. UNION ALL 2 -6. && 3. TEST 3 3 -1. LEVEL 1 3 -2. LEVEL 2

1. NEW OPEN SQL 1. SAP NETWAVER 7. 40 SP 5 부터 NEW OPEN SQL 을 사용할 수 있다. 2. CODE PUSHDOWN (Calculations into DB) 방식 3. 사칙연산, 조건식, 문자열연결, 형변환 도 SELECT문에서 처리가능 관련 4. RIGHT OUTER JOIN 지원, JOIN 테이블 및 SUBQUERY 최대 50개 5. JOIN 문 ON기능 확장 (BETWEEN, <, >) 기능추가

1. NEW OPEN SQL CODE PUSHDOWN (Calculations into DB) 1. OPEN SQL 관련 => AS ABAP=> DB => CALCULATION AS ABAP 2. NEW OPEN SQL => AS ABAP => CALCULATION DB => AS ABAP

1. NEW OPEN SQL 관련

1. NEW OPEN SQL CLASSICAL APPROACH 1 관련 2 FUTURE APPROACH

1. NEW OPEN SQL 관련 OPEN SQL답은+아니다! NATIVE SQL

1. -1 OPEN SQL VS NEW OPEN SQL 관련

1. -1 OPEN SQL VS NEW OPEN SQL 관련

1. -1 OPEN SQL VS NEW OPEN SQL @DATA(WA_SFLIGHT)? 관련 @DATA : SAP 7. 4버전에서 새롭게 지원되는 문 법. SQL 구문에서 DIRECT 변수선언 가능. EX) VALUE# = MOVE-CORRESPONDING

1. -1 OPEN SQL VS NEW OPEN SQL DATA : GV_VAR TYPE C VALUE ‘DE~~~’. 관련

1. -1 OPEN SQL VS NEW OPEN SQL 사용가능 INTO @DATA(WA) 관련 INTO TABLE @DATA(ITAB)

1. -1 OPEN SQL VS NEW OPEN SQL DATA 선언 불필요. 관련 SELECT * FROM SCARR INTO TABLE @DATA(GT_ITAB) LOOP AT GT_ITAB INTO DATA@(GS_WA). WRITE: / GS_WA-WARRID, GS_WA-CARNAME. ENDLOOP.

1. -1 OPEN SQL VS NEW OPEN SQL 관련 CORRESPONDING~ 불필요

1. -2 NATIVE SQL VS NEW OPEN SQL VS NATIVE SQL 관련 WHAT’S DIFFERENT?

1. -2 NATIVE SQL VS NEW OPEN SQL 1. 2. 3. 4. 5. EXEC ~ ENDEXEC SY-MANDT @변수, : , . JOIN문법 DB종속적 QUERY 관련

1. -2 NATIVE SQL VS NEW OPEN SQL DATA: BEGIN OF gt_itab OCCURS 0, carrid LIKE sflight-carrid, connid LIKE sflight-connid, END OF gt_itab. DATA : gv_carrid LIKE sflight-carrid value 'AA'. 1. NATIVE SQL 구문 DATA : gs_wa LIKE sflight. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. EXEC SQL. SELECT * INTO : GS_WA FROM SFLIGHT A WHERE A. MANDT = : SY-MANDT AND A. CARRID = : GV_CARRID ENDEXEC. WRITE : gs_wa-carrid, gs_wa-connid. 관련 EXEC SQL PERFORMING APPEND_ITAB. SELECT A. CARRID, A. CONNID INTO : GT_ITAB-CARRID, : GT_ITAB-CONNID FROM SFLIGHT A WHERE A. MANDT = : SY-MANDT AND A. CARRID = : GV_CARRID SFLIGHT 테이블 ENDEXEC. FORM append_itab. APPEND gt_itab. CLEAR gt_itab. ENDFORM. 의 CARRID ‘AA’ 값이 1만 건이면, DB를 1만 번 SELECT. "APPEND_ITAB DATA : GS_WA LIKE GT_ITAB. EXEC SQL PERFORMING APPEND_ITAB. SELECT A. CARRID, A. CONNID INTO : GS_WA FROM SFLIGHT A WHERE A. MANDT = : SY-MANDT AND A. CARRID = : GV_CARRID ENDEXEC. FORM append_itab. APPEND GS_WA TO gt_itab. CLEAR GS_WA. ENDFORM. "APPEND_ITAB

1. -2 NATIVE SQL VS NEW OPEN SQL 1. NEW OPEN SQL 구문 DATA: BEGIN OF gt_itab OCCURS 0, carrid LIKE sflight-carrid, connid LIKE sflight-connid, END OF gt_itab. DATA : gv_carrid LIKE sflight-carrid VALUE 'AA'. DATA : gv_carrid LIKE sflight-carrid value 'AA'. EXEC SQL PERFORMING APPEND_ITAB. SELECT A. CARRID, A. CONNID INTO : GT_ITAB-CARRID, : GT_ITAB-CONNID FROM SFLIGHT A WHERE A. MANDT = : SY-MANDT AND A. CARRID = : GV_CARRID ENDEXEC. FORM append_itab. APPEND gt_itab. CLEAR gt_itab. ENDFORM. 관련 SELECT CARRID, CONNID FROM SFLIGHT INTO TABLE @DATA(GT_ITAB) WHERE MANDT = SY-MANDT AND CARRID = @GV_CARRID. 1번 SELECT

2. SQL 연산자 NEW OPEN SQL DIV 나눗셈에서 몫 DIV NEW OPEN SQL NUM 1 = 11, NUM 2 = 5. 관련 SELECT NUM 1, NUM 2 DIV( NUM 1, NUM 2) AS DIV = 2.

2. SQL 연산자 NEW OPEN SQL MOD 나눗셈에서 나머지 값 MOD NEW OPEN SQL NUM 1 = 11, NUM 2 = 5. 관련 SELECT NUM 1, NUM 2 MOD( NUM 1, NUM 2) AS MOD = 1.

2. SQL 연산자 NEW OPEN SQL CEIL 평가식의 값보다 크거나 같은 정수의 최소값 (올림 함수) CEIL NEW OPEN SQL NUM 1 = 450, 45. 관련 SELECT NUM 1, CEIL( NUM 1 ) AS CEIL = 451.

2. SQL 연산자 NEW OPEN SQL FLOOR 평가식의 값보다 작거나 같은 정수의 최대값 (버림함수) FLOOR NEW OPEN SQL NUM 1 = 450, 45. 관련 SELECT NUM 1, FLOOR( NUM 1 ) AS FLOOR CEIL = 450.

2. SQL 연산자 COALESCE SQL ABS SQL COALESCE ABS NULL 대체 절대값 관련

2. SQL 연산자 NEW OPEN SQL COALESCE NULL 대체 COALESCENEWOPENSQL NUM 1 = 0. 관련 SELECT NUM 1, COALESCE ( NUM 1, 1 ) AS COALESCE = 1.

2. SQL 연산자 NEW OPEN SQL ABS 절대 값 ABS NEW OPEN SQL NUM 1 = -10. 관련 SELECT NUM 1, ABS( NUM 1 ) AS ABS = 10.

2. SQL 연산자 CASE SQL CAST SQL CASE CAST 관련 조건문 형변환=>FLTP만 지원 16진수

2. SQL 연산자 NEW OPEN SQL CASE 조건문 CASE NEW OPEN SQL TYPE = 1 SELECT TYPE, 관련 CASE TYPE WHEN ‘ 1’ THEN ‘KIM’ ELSE ‘LEE’ END AS CASE_DESC = KIM.

2. SQL 연산자 NEW OPEN SQL CAST 형변환 CAST NEW OPEN SQL SELECT CARRID, CONNID, CAST( SEATSOCC AS FLTP) / 관련 CAST( SEATSMAX AS FLTP) AS OCCUP INTO TABLE @DATA(GT_ITAB) FROM SFLIGHT WHERE SEATSOCC > 0. GT_ITAB-OCCUP = 1. 4~~E.

2. SQL 연산자 NEW OPEN SQL UNION 관련 합산 AND 중복제거 A TABLE ZIP = 1. B TABLE ZIP = 1, 2. SELECT ZIP, FROM A UNION SELECT ZIP, FROM B ZIP = 1, 2.

2. SQL 연산자 NEW OPEN SQL UNION ALL 합산 AND 중복포함 UNION ALL NEW OPEN SQL A TABLE ZIP = 1. B TABLE ZIP = 1, 2. 관련 SELECT ZIP, FROM A UNION ALL SELECT ZIP, FROM B ZIP = 1, 1, 2.

2. SQL 연산자 EN T A NEW OPEN SQL C N O C && 문자열 연결 E T A && NEW OPEN SQL TYPE = 1 SELECT TYPE, 관련 CASE TYPE WHEN ‘ 1’ THEN (‘KIM’ && TYPE) ELSE ‘LEE’ END AS TYPE_DESC = KIM 1 GV_STR = ‘HTTP: //’ && ’KIUKI’ && ‘. CO. KR’.

3. TEST 관련

3. TEST-기초문법 A B TYPE NAME 1 PARTNER ADDRNUMBER 1 KIM 001 1234 2 LEE 002 5678 3 PARK 003 관련 SELECT TYPE, CASE TYPE WHEN ‘ 1’ THEN ( ‘PERSON’ && ‘/’ && TYPE ) WHEN ‘ 2’ THEN ( ‘CUSTOMER’ && ‘/’ && TYPE ) ELSE ‘CLIENT’ AND AS TYPE_DESC NAME 1 COALESCE( B~ADDRNUMBER, ‘NO ADDRESS’ ) AS ADDRNR FROM A LEFT OUTER JOIN B ON A~PARTNER = B~PARTNER.

3. TEST-기초문법 TYPE_DESC 1 PERSON/1 NAME 1 ADDRNR 1234 2 KIM CUSTOMER/2 관련 LEE 3 CLIENT NO ADDRESS PARK 5678

3. TEST-심화문법 A TYPE NUM 1 NUM 2 0 63 59 1 57 13 2 22 64 3 12 99 4 42 98 5 72 98 6 58 67 7 48 45 8 71 48 9 6 27 DATA(OFFSET) = 10000. SELECT ID, NUM 1, NUM 2, DIV( NUM 1, NUM 2 ) AS DIV, 관련 MOD( NUM 1, NUM 2 ) AS MOD, @OFFSET + ABS( NUM 1 – NUM 2 ) AS SUM FROM A INTO TABLE @DATA(GT_ITAB) ORDER BY SUM DESENDING.

3. TEST-심화문법 TYPE NUM 1 NUM 2 DIV MOD SUM 1 57 13 4 5 10044 2 22 64 0 22 10042 3 12 99 0 12 10087 4 42 98 0 42 10056 5 72 98 0 72 10026 6 58 67 0 58 10009 7 48 45 1 3 10003 8 71 48 1 23 10023 9 6 27 0 6 10021 0 63 59 1 4 10004 관련