TIMESTAMPDIFF (4, CHAR ( TIMESTAMP ('2001-09-29-11.25.42.483219') - TIMESTAMP ('2001-09-26-12.07.58.065497')))
다음 예에서는 두 시간소인 사이의 시간(분)인 4277을 리턴합니다.
날짜 형식 내가 원한는 형식의 타임으로 변환
SELECT
VARCHAR_FORMAT(FST_REG_DT, 'YYYY-MM-DD HH24:MI:SS')
FROM DMSEIP.TGP_HL_BOARD
1. 가공전 데이타
id rowfiled
aaa 1
aaa 2
aaa 3
aaa 4
aaa 5
2. 가공후 데이타.
id filed1 filed2 filed3 filed4 filed5
aaa 1 2 3 4 5
WITH Types AS (
SELECT id , rowfiled ,VALUE
, ROWNUMBER() OVER(ORDER BY rowfiled ) AS rn
FROM (
SELECT id , rowfiled ,VALUE
FROM table_a
where idin ( '45636' , '26354' )
)
AS R
)
SELECT a.PROF_KEY
, MIN(CASE WHEN rn = 1 THEN t.value) as birthday
, MIN(CASE WHEN rn = 2 THEN t.value) as cmpname
, MIN(CASE WHEN rn = 3 THEN t.value) as cname
, MIN(CASE WHEN rn = 4 THEN t.value) as ename
, MIN(CASE WHEN rn = 5 THEN t.value) as natal
, MIN(CASE WHEN rn = 6 THEN t.value) as photourl
, MIN(CASE WHEN rn = 7 THEN t.value) as rnkcode
FROM table_a A
, Types T
WHERE A.id = t.id
and A.id in ( '11111' , '2222' )
GROUP BY a.id ;
DB2
rowfiled 필드의 세로 데이타를 가로로 만드는 방법임.
error code SQLSTATE=22001
error SQLSTATE=42826
WITH RPL (LEVEL, ORG_ID, COMPANYCODE, DEPTCODE, DEPTNAME, PARENTDEPTCODE , allcode ,allcodeNm) AS
(
SELECT 1 ,parent.ORG_ID, parent.COMPANYCODE, parent.DEPTCODE, parent.DEPTNAME, parent.PARENTDEPTCODE ,
cast(parent.DEPTCODE as varchar(1500)) as allcode , -- 에러나는 필드를 임의로 늘려 준다
cast(parent.DEPTNAME as varchar(1500))as allcodeNm
FROM ORGANIZATION parent
where parent.PARENTDEPTCODE is null
and use_yn = 'Y'
UNION ALL
SELECT PARENT.LEVEL + 1,child.ORG_ID, child.COMPANYCODE, child.DEPTCODE, child.DEPTNAME, child.PARENTDEPTCODE ,
cast(parent.allcode||'>'||child.DEPTCODE as varchar(1500)) as allcode ,
cast(parent.allcodeNm||'>'||child.DEPTNAME as varchar(1500)) as allcodeNm
FROM (
SELECT ORG_ID, COMPANYCODE, DEPTCODE, DEPTNAME, PARENTDEPTCODE
FROM ORGANIZATION
where PARENTDEPTCODE is not null
and use_yn = 'Y'
) AS child , RPL PARENT
WHERE PARENT.DEPTCODE = child.PARENTDEPTCODE
)
SELECT LEVEL, 'deptcode' STATETYPE, COMPANYCODE COMPANYID, COMPANY_NAME COMPANYNM, DEPTCODE AS CODE, DEPTNAME CODENM ,PARENTDEPTCODE ,allcode ,allcodeNm
FROM RPL A, COMPANY_INFO B
where A.COMPANYCODE =B.COMPNAYID
ORDER BY LEVEL
WITH UR