오라클과 MS-SQL 함수, 쿼리 차이점

오라클과 MS-SQL 함수, 쿼리 차이점(마이그레이션....ㅎㅎ)
출처 : http://blog.daum.net/magixtech/1906527?srchid=BR1http%3A%2F%2Fblog.daum.net%2Fmagixtech%2F1906527

쿼리오라클9iMS-SQL2000
값 대체DECODE
ex) DECODE(컬럼명, 'A', 'OK', 'B', 'NO', 컬럼명)
CASE WHEN (오라클도 있음)
ex) CASE WHEN 컬럼명 = 'A' THEN 'OK' WHEN 컬럼명 = 'B' THEN 'NO' ELSE 컬럼명 END
문자열 자르기SUBSTR
ex) SUBSTR('ANSDDD', 1, 2) = 'AN'
SUBSTRING
ex) SUBSTRING('ANSDDD', 1,2) = 'AN'
시스템시간SYSDATEGETDATE()
널값제어NVL
ex) NVL(컬럼명,0) => 컬럼값이 널이면 0으로 대체
isNULL
ex) isNULL(컬럼명,0) => 컬럼값이 널이면 0으로 대체
형변환TO_CHAR,TO_NUMBER
ex) TO_CHAR(컬럼명) => 문자열 형태로 변환
CONVERT
ex) CONVERT(VARCHAR,컬럼명) => 문자열 형태로 변환

차이점(MS-SQL 사용시 유의점)

1. MS-SQL2000은 재귀순환쿼리 (ORACLE 의 CONNECT BY) 구문이 없습니다..
MS-SQL2005에서는 지원된다고 합니다.
따라서 재귀순환이 필요할 경우 함수를 만들어 사용해야 합니다.
ex)
-------------------------------------------------------------------
CREATE FUNCTION fn_recursivemenu(@current as varchar(10))
RETURNS @Hierarchy table (
 item_idx [int] IDENTITY (1, 1) NOT NULL , item varchar(20), up_item varchar(20), lvl int
)
-- Recusrsive메뉴 구현
as
begin
 DECLARE @lvl int
 DECLARE @stack table (item varchar(20), up_item varchar(20), lvl int)
 
    INSERT INTO @stack VALUES (@current, null, 1)
    SELECT @lvl = 1
    WHILE @lvl > 0
       BEGIN
          IF EXISTS (SELECT * FROM @stack WHERE lvl = @lvl)
             BEGIN

                SELECT @current = item
                FROM @stack
                WHERE lvl = @lvl

                INSERT INTO @Hierarchy
                SELECT menu_id, up_menu_id, menu_level FROM SYS_MENU
                WHERE menu_id = @current

                DELETE FROM @stack
                WHERE lvl = @lvl
                   AND item = @current

                INSERT @stack
                SELECT menu_id, up_menu_id, @lvl + 1
                FROM SYS_MENU
                WHERE up_menu_id = @current
   ORDER BY menu_id desc

                IF @@ROWCOUNT > 0
                   SELECT @lvl = @lvl + 1

             END

          ELSE

             SELECT @lvl = @lvl - 1
 
       END
 
 RETURN
END
------------------------------------------------------------

2. MS-SQL 은 하위쿼리 테이블 쿼리시 꼭 alias 명을 명시적으로
부여해야 됩니다.
 ex) select * from (select * from table) --- 에러
      select * from (select * from table) a --- OK

3. MS-SQL2000 Rownum 매기는법(편법)
  : MS-SQL에서 순차적인 번호를 매기기 위해서는 IDENTITY를 컬럼에 부여해주는

    방법밖에 없습니다.
    꼭 순차적인 번호를 부여해야 하는 경우라면 아래예문같은 방법을 사용해서 구현

    할 수는 있습니다만 그다지 추천할 방법은 아닙니다.

ex)
-------------------------
SELECT 
  ((SELECT count(*) FROM [테이블명] b where b.키컬럼 <= a.키컬럼)+200700000) AS ROWNUM
FROM                                                     
  [테이블명] a 
ORDER BY 키컬럼
-------------------------
[결과]
200700001
200700002

 

 

Math Functions
FunctionOracleSQL Server
Absolute valueABSABS
Arc cosineACOSACOS
Arc sineASINASIN
Arc tangent of nATANATAN
Arc tangent of n and mATAN2ATN2
Smallest integer >= valueCEILCEILING
CosineCOSCOS
Hyperbolic cosineCOSHCOT
Exponential valueEXPEXP
Round down to nearest integerFLOORFLOOR
Natural logarithmLNLOG
Logarithm, any baseLOG(N)N/A
Logarithm, base 10LOG(10)LOG10
Modulus (remainder)MODUSE MODULO (%) OPERATOR
PowerPOWERPOWER
Random numberN/ARAND
RoundROUNDROUND
Sign of numberSIGNSIGN
SineSINSIN
Hyperbolic sineSINHN/A
Square rootSQRTSQRT
TangentTANTAN
Hyperbolic tangentTANHN/A
TruncateTRUNCN/A
Highest number in listGREATESTN/A
Lowest number in listLEASTN/A
Convert number if NULLNVLISNULL

 

String Functions
FunctionOracleSQL Server
Convert character to ASCIIASCIIASCII
String concatenateCONCAT(expression! + expression!)
Convert ASCII to characterCHRCHAR
Return starting point of character in character string (from left)INSTRCHARINDEX
Convert characters to lowercaseLOWERLOWER
Convert characters to uppercaseUPPERUPPER
Pad left side of character stringLPADN/A
Remove leading blank spacesLTRIMLTRIM
Remove trailing blank spacesRTRIMRTRIM
Starting point of pattern in character stringINSTRPATINDEX
Repeat character string multiple timesRPADREPLICATE
Phonetic representation of character stringSOUNDEXSOUNDEX
String of repeated spacesRPADSPACE
Character data converted from numeric dataTO_CHARSTR
SubstringSUBSTRSUBSTRING
Replace charactersREPLACESTUFF
Capitalize first letter of each word in stringINITCAPN/A
Translate character stringTRANSLATEN/A
Length of character stringLENGTHDATALENGTH or LEN
Greatest character string in listGREATESTN/A
Least character string in listLEASTN/A
Convert string if NULLNVLISNULL

 

Date Functions
FunctionOracleSQL Server
Date addition(use +)DATEADD
Date subtraction(use -)DATEDIFF
Last day of monthLAST_DAYN/A
Time zone conversionNEW_TIMEN/A
First weekday after dateNEXT_DAYN/A
Convert date to stringTO_CHARDATENAME
Convert date to numberTO_NUMBER(TO_CHAR())DATEPART
Convert string to dateTO_DATECAST
Get current date and timeSYSDATEGETDATE()

 

 

 

substr == substring
to_char == convert

--to_char(to_date(a.accept_da,'YYYY-MM-DD'), 'yyyy.mm.dd'),
 convert(varchar(10),cast(a.accept_da as smalldatetime),102),
 
-- to_char(a.dr_amt, '999,999,999,999,999'),
subString(convert(varchar, cast(a.dr_amt as money ),1),1,len(convert(varchar, cast(a.dr_amt as money ),1))-3),
subString(convert(varchar, cast(a.cr_amt as money ),1),1,len(convert(varchar, cast(a.cr_amt as money ),1))-3),


--TO_CHAR(last_day(DATEADD(month,substring('20050127',1,6) +  '01',-1)), 'YYYYMMDD')
dateadd(dd, -datepart(dd, cast('20050127' as smalldatetime)), dateadd(mm,1,cast('20050127' as smalldatetime)))

 

 

LPAD == replace

NVL == COALESCE

-- AND ROWNUM = 1
TOP 1


--   DECODE(dr_cr, 'D', SUM(currency_amt), 0) debt_amount,
--   DECODE(dr_cr, 'C', SUM(currency_amt), 0) credit_amount,
(case dr_cr when 'D' then SUM(currency_amt) else 0 end) debt_amount,
(case dr_cr when 'C' then SUM(currency_amt) else 0 end) credit_amount,


--select LPAD(' ',TO_NUMBER(s.accnt_gubun) * 4,' ') ||  s.accnt_detal_name from tb_erp_faccnt_cd s where s.accnt_cd = a.accnt_cd) accnt_detal_name ,
select  accnt_cd, (select right(space(cast(s.accnt_gubun as int) * 4)+s.accnt_gubun,cast(s.accnt_gubun as int) * 4)+  s.accnt_detal_name from tb_erp_faccnt_cd s where s.accnt_cd = a.accnt_cd) accnt_detal_name ,

 

--to_char(add_months(to_date('@@accept_da_fr','yyyymmdd'),-12),'yyyymmdd')
convert(varchar(8),dateadd(month,-12,cast('@@accept_da_fr' as datetime)),112)

 

 

 

오라클의 trunc 함수를 MS-SQL에서 구현하기...

 

MSSQL

select

cast(round(A,0,1) as decimal(10))

from [TABLE]

 

 

# ORACLE

select trunc(A) from [TABLE]

 

  제목 : 오라클의 trunc와 같은 기능을 하는것??  
create table AAA
(
 A float
)

insert AAA values (10.12345)
insert AAA values (10.445)

select round(A,2,1), cast(round(A,2,1) as decimal(10,2)) from AAA
10.119999999999999 10.12
10.44 10.44
 

to_date --> cast

 

오라클에서 to_date는 형을 데이터 형식으로 바꾸는 함수 인데

to_date(컬럼명, 'YYYYMMDD') 이렇게 써주어서 변환했는데 MS-SQL에서는 CAST를 쓴다.

CAST(컬럼명 as smalldatetime) 이렇게 데이터 타입으로 바꾼다.

 

그럼 CONVERT는 안될까? ㅡㅡ;;

방금해봤는데..되긴된다.. 쿠허허허허

CONVERT(smalldatetime, 컬럼명)

by 狂虎 | 2010/10/08 16:35 | 수첩 | 트랙백 | 덧글(1)

트랙백 주소 : http://mt1716.egloos.com/tb/10590565
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]
Commented by 정지훈 at 2016/09/29 11:08
sqlserver2000 사용자인데 없는 기능이 많아서 애좀 먹었는데 덕분에 해결하였습니다.
감사합니다.

:         :

:

비공개 덧글

◀ 이전 페이지다음 페이지 ▶