Native SQL 사용하기
이번 사이트는 Native SQL 을 사용하는 경우가 아주 많을 것 같다.
이에 관련된 자료를 찾다 ABAP TIP & TECH에서 발견해 퍼왔다.
나같은 Native SQL 초보자에게 아주 좋은 자료..
기본적인 오라클 함수까지 정리 되어 있다.
SAP R/3에는 Open SQL이라는 아주 편리한 SQL이 있습니다.
하지만, Open SQL로 해결이 되지않는 복잡한 SQL은 부득이 Native SQL을 사용해야
합니다.
기본문법 편에서는 Native SQL을 사용하기 위한 기본문법을 설명하도록 하겠습니다.
기본문법 1은 Open SQL의 SELECT SINGLE ~과 같은 결과를 가져오도록 하는 방법입
니다.
먼저 사용예를 보도록 하죠.
EXEC SQL.
SELECT A.MTART
INTO :G_MTART
FROM MARA A
WHERE A.MANDT = :SY-MANDT
AND A.MATNR = :G_MATNR
ENDEXEC.
위의 예에서 보면 Native SQL은 EXEC SQL.로 시작해서 ENDEXEC.로 끝나는 것을
알 수 있습니다.
SQL 문은 EXEC SQL과 ENDEXEC 사이에 들어가게 되는 거죠.
SELECT절에는 읽어올 Field명을 적어줍니다.
INTO절에는 읽어온 Field의 값을 저장할 변수를 적어줍니다.
FROM절에는 Table명을 적어줍니다.
MARA 다음의 A는 Table명이 아니라 MARA라는 Table을 현재 사용하고 있는 SQL에서
A라는 이름으로 사용하겠다는 표시입니다.
그래서, SELECT절이나 WHERE절에서 Field명 앞에 'A.'이라고 적어주는 것입니다.
WHERE절의 조건 중 MANDT는 모든 Table에 적용됩니다.
왜냐하면 MANDT는 Client 정보를 가지고 있는 Field인데 SAP의 모든(없는 Table이
있다면 Table을 새로 만들어야 합니다.) Table에 들어있습니다.
MANDT조건을 안 써주게되면 현재 Client가 아닌 다른 Client에서도 Data를 읽어와
버리기 때문에 꼭 써줘야 합니다.
Open SQL에서는 안써줘도 system이 알아서 현재 Client에 있는 data만 읽어옵니다.
그리고, Program 내에 사용된 변수(위에서는 G_MTART, G_MATNR, SY-MANDT)를 사용
할 때는 변수명 앞에 반드시 ':'를 적어줘야합니다.
마지막으로 SQL문의 끝에는 '.'을 찍지 않습니다.
Native SQL 사용하기 2 - Record Select
[소스코드예제]
DATA: BEGIN OF ITAB OCCURS 0.
MATNR LIKE MARA-MATNR,
MTART LIKE MARA-MTART,
END OF ITAB.
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.MATNR, A.MTART
INTO :ITAB-MATNR, :ITAB-MTART
FROM MARA A
WHERE A.MANDT = :SY-MANDT
AND A.MTART = 'FERT'
ENDEXEC.
FORM APPEND_ITAB.
APPEND ITAB.
CLEAR ITAB.
ENDFORM.
위의 source code는 Material Master에서 Material type이 'FERT'(완제품)인 것
의 Material code와 Material type을 모두 읽어오는 SQL문입니다.
1탄에 소개한 source와 눈에 띄게 달라진 부분이 PERFORMING APPEND_ITAB이라는
구문입니다.
위의 source code는 data를 하나씩(한 record씩) 읽어옵니다.
이때 한 record를 읽어올 때 마다 APPEND_ITAB이라는 subroutine을 실행하게 됩니다.
따라서, 위의 source code결과는 ITAB이라는 Internal table에 읽어온 data가
저장이 되게 됩니다.
Native SQL 사용하기 3 - Join
아래의 Source는 Material Type이 'FERT'인 Material의 code와 Decription을 읽어옵니다.
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.MATNR, B.MAKTX
INTO :ITAB-MATNR, :ITAB-MAKTX
FROM MAKT B, MARA A
WHERE A.MANDT = :SY-MANDT
AND A.MTART = 'FERT'
AND B.MANDT = A.MANDT
AND B.MATNR = A.MATNR
ENDEXEC.
Native SQL 사용하기 4 - Outer Join
Outer Join은 일반 C/S환경에서는 대부분의 SQL에서 사용해야만 한다고해도 과언이
아닙니다.
왜냐하면, Table과 Program을 일일이 다 만들어 주다보니 혹시 있을지 모르는 Bug
때문에 어떠한 경우라도 Data를 출력하기 위해서 입니다.
하지만, SAP R/3에서는 일단 Table Data의 Inconsistency 가능성이 거의 없기
때문에 특별한 경우에만 Outer Join을 사용합니다.
Outer Join은 다음의 경우에 사용합니다.
예)
A라는 CBO Table은 자재단가를 저장하는 Table이고, User는 자재단가와 자재의
Description을 출력하는 Report를 원한다고 가정합니다.
그리고, A Table과 자재의 Description이 들어있는 MAKT Table의 내용은 다음과 같습니다.
Table A Table MAKT(Descirption외의 field는 생략
======================= ======================================
MANDT MATNR NETPR MANDT MATNR MAKTX
======================= ======================================
100 M01 100 100 M01 Monitor
100 C01 10 100 C01 Case
100 C02 50 100 C02 CPU
100 V01 20
1) Outer Join을 사용하지 않은 경우
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.MATNR, B.MAKTX, A.NETPR
INTO :ITAB-MATNR, :ITAB-MAKTX, :ITAB-NETPR
FROM MAKT B, A A
WHERE A.MANDT = :SY-MANDT
AND B.MANDT = A.MANDT
AND B.MATNR = A.MATNR
ENDEXEC.
이경우 ITAB에는 다음과 같이 들어갑니다.
=================================
MATNR MAKTX NETPR
=================================
M01 Monitor 100
C01 Case 10
C02 CPU 50
2) Outer Join을 사용한 경우
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.MATNR, B.MAKTX, A.NETPR
INTO :ITAB-MATNR, :ITAB-MAKTX, :ITAB-NETPR
FROM MAKT B, A A
WHERE A.MANDT = :SY-MANDT
AND B.MANDT(+) = A.MANDT
AND B.MATNR(+) = A.MATNR
ENDEXEC.
이경우 ITAB에는 다음과 같이 들어갑니다.
=================================
MATNR MAKTX NETPR
=================================
M01 Monitor 100
C01 Case 10
C02 CPU 50
V01 20
위의 예에서 볼 수 있듯이 Outer Join을 사용하지 않으면 MAKT에 등록이 되어있는
자재단가만 Report에서 출력이 되지만, Outer Join을 사용하면 MAKT에 등록이 되어
있지 않는다 하더라도 출력이 되는 것을 볼 수 있습니다.
Description이 없다고 출력이 안되는 것보다는 없는 채로 출력이 되는 것이 당연히
더 좋겠죠.
그리고, 마지막으로 다음의 경우에는 Outer Join이 안먹습니다.
안먹는다는 얘기는 error라는 이야기가 아니고, Outer Join을 사용했지만 결과는
사용하지 않은 것과 같다는 말입니다.
1. FROM B, C, A
WHERE C.aaa(+) = A.aaa
AND C.bbb(+) = B.bbb
하나의 Table에 두 개의 Table에 걸쳐서 Outer Join을 걸 수 없습니다.
2. WHERE B, A
AND B.aaa(+) = A.aaa
AND B.bbb = 'ABC'
하나의 Table에 일부 field만 Outer Join을 사용하려고 할 때 Outer join은 작
동하지 않습니다.
Native SQL 사용하기 5 - Oracle Function
자주 사용하는 것 위주로 설명을 하니 양해를 바랍니다.
1. SUBSTR - String의 일부분을 추출할 때 사용합니다.
예) Material Type이 'FERT'인 Material Code의 11번째부터 4자리만 읽어오고
자 할 때
EXEC SQL PERFORMING APPEND_ITAB.
SELECT SUBSTR(A.MATNR,11,4)
INTO :ITAB-MATNR
FROM MARA A
WHERE A.MANDT = :SY-MANDT
AND A.MTART = 'FERT'
ENDEXEC.
2. CONCAT - String을 합칠 때 사용합니다.
예) Material Type이 'FERT'인 Material Code의 Material Code의 앞부분 4자리
와 Material Group을 합쳐서 읽어오고자 할 때
EXEC SQL PERFORMING APPEND_ITAB.
SELECT CONCAT(SUBSTR(A.MATNR,1,4), A.MAKTL)
INTO :ITAB-GROUP
FROM MARA A
WHERE MANDT = :SY-MANDT
AND MTART = 'FERT'
ENDEXEC.
3. DECODE - 읽어온 값을 판별하여 원하는 값으로 변환하여 저장하고자 할 때
예) MType이 '101'인 Material Document는 수량을 '+'로, MType이 '102'인
Material Document는 수량을 '-'로 변환하여 읽어오기
EXEC SQL PERFORMING APPEND_ITAB.
SELECT DECODE(A.BWART, '101', A.MENGE, '102', A.MENGE * -1)
INTO :ITAB-MENGE
FROM MSEG A
WHERE MANDT = :SY-MANDT
AND BWART IN ('101','102')
ENDEXEC.
4. LPAD - 전체 길이를 정해놓고 읽어온 값이 지정한 길이보다 짧으면 모자라는 만
큼 왼쪽에다가 지정한 문자를 넣어서 Return
예) Customer Code '123456'의 Description을 읽어올 때
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.NAME1
INTO :ITAB-NAME1
FROM KNA1 A
WHERE MANDT = :SY-MANDT
AND KUNNR = LPAD('123456',10,'0')
ENDEXEC.
위의 SQL문은 다음과 동일한 문장이다.
EXEC SQL PERFORMING APPEND_ITAB.
SELECT A.NAME1
INTO :ITAB-NAME1
FROM KNA1 A
WHERE MANDT = :SY-MANDT
AND KUNNR = '0000123456'
ENDEXEC.
* GROUP BY 예제.
EXEC SQL PERFORMING move_data.
SELECT TEXTGU, UPMUGU, JIJUM, COUNT(*)
INTO :it_smas-TEXTGU, :it_smas-UPMUGU,
:it_smas-JIJUM, :it_smas-QUANTITY
FROM sale1st.sap_tb_text@sales7
WHERE YYMM = :p_zyymm1
GROUP BY TEXTGU, UPMUGU, JIJUM
ENDEXEC.
EXEC SQL.
COMMIT
ENDEXEC.
끝.