본문 바로가기

Prog.Lang. or Query/SQL

[작업 TIP] 쿼리 생성 스크립트 만들기 /* SQL 생성 스크립트 실행 파일 (execute_test) */ SET ECHO OFFSET NEWPAGE 0SET SPACE 0SET PAGESIZE 0SET FEEDBACK OFFSET HEADING OFFSET TRIMSPOOL ONSET FLUSH OFFSET VERIFY OFFSET TERM OFFSET HEAD OFF SPOOL 파일명.sql;@SQL생성스크립트 파라미터1 ...SPOOL OFF; ... /* SQL 생성 스크립트 */WITH T1 AS ( SELECT '&1' OW, '&2' TN FROM DUAL)SELECT NVL2(LAG(OWNER) OVER (ORDER BY COLUMN_ID),' , ','INSERT INTO '||TABLE_NAME||' ( '||CHR(10).. 더보기
[SQL QUERY] SESSION MONITORING SQL SCRIPT SELECT /*+ PARALLEL(S,8) PARALLEL(Q,8) */ DISTINCT T.LOG_IO, T.PHY_IO ,T.CR_GET ,S.* FROM GV$SQL Q ,GV$SESSION S ,GV$TRANSACTION T WHERE S.SQL_ID = Q.SQL_ID AND S.INST_ID = Q.INST_ID AND SQL_TEXT LIKE '%INTO IDBADM.ODS_RT_TC_LCDR_DTL X%' AND SQL_TEXT NOT LIKE '%GV$%' AND S.SADDR = T.SES_ADDR(+) AND S.INST_ID = T.INST_ID(+) ORDER BY S.OWNERID DESC ,S.SID ; 더보기
[SQL QUERY] FIND ROWS THAT ARE NOT MAPPED IN OUTER JOIN TABLES WITH A AS ( SELECT 1 A, '매핑' MAPPED FROM DUAL UNION ALL SELECT 2 A, '매핑' MAPPED FROM DUAL UNION ALL SELECT 3 A, '미매핑' MAPPED FROM DUAL UNION ALL SELECT 4 A, '매핑' MAPPED FROM DUAL),B AS ( SELECT 1 A, '매핑' MAPPED FROM DUAL UNION ALL SELECT 2 A, '매핑' MAPPED FROM DUAL UNION ALL SELECT 4 A, '매핑' MAPPED FROM DUAL union all SELECT 5 A, '미매핑' MAPPED FROM DUAL ) SELECT * FROM A,B WHERE 1=1 AND A.A(+) = B.. 더보기
[SQLServer] Convert rows to a column ref: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ sqlserver rows to column SELECT p1.CategoryId, ( SELECT ProductName + ',' FROM Northwind.dbo.Products p2 WHERE p2.CategoryId = p1.CategoryId ORDER BY ProductName FOR XML PATH('') ) AS Products FROM Northwind.dbo.Products p1 GROUP BY CategoryId ; 대충 보면 이해가지? 안가면 테이블 변수 바꿔서 돌려보길 바랍니다. 뭐 대충 안에있는거 xml 화 해.. 더보기
UTLSAMPL.SQL ( scott table sample sql query) Rem Copyright (c) 1990 by Oracle Corporation Rem NAME REM UTLSAMPL.SQL Rem FUNCTION Rem NOTES Rem MODIFIED Rem gdudey 06/28/95 - Modified for desktop seed database Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT Rem rlim 04/29/91 - change char to varchar2 Rem mmoore 04/08/91 - use unlimited tablespace priv Rem pritto 04/04/91 .. 더보기

티스토리툴바