<?xml version="1.0" encoding="UTF-8" ?>
<jsp:root
	xmlns:jsp="http://java.sun.com/JSP/Page"
	xmlns:og="hybs-taglib.tld"
	version="2.0">
<jsp:directive.page language="java" contentType="text/html; charset=UTF-8" />

<og:head headType="result" />
<jsp:directive.include file="/jsp/custom/result_info.jsp" />
<og:comment
	system	= "GF:エンジンサンプル"
	pgid	= "GF2010"
	title	= "テーブル"
	date	= "2008/10/27"
	author	= "Hiroki.Nakamura"
	text	= ""
	version	= "001"
/>

<og:columnCheck
	command   = "NEW"
	nullCheck = "SYSTEM_ID,PGID"
/>

<og:query command="NEW" debug="{@debug}" maxRowCount="{@maxRowCount}">
	<!-- NMSYORI="QUERY"  は、PGIDに関連するGF07のインデックスカラムの全カラム -->
		SELECT	A.SYSTEM_ID , B.PGID , 'QUERY' AS NMSYORI
			  , A.SEQNO as SEQ, A.CLM , A.TABLE_NAME, '' as ZOKUSEI , C.AS_TABLE , '' as FGJ , '' as UNIQ
		FROM GF07 A, GF90 B, GF91 C
		WHERE B.PGID		= C.PGID
		AND   A.TABLE_NAME	= C.TABLE_NAME
		AND   A.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   C.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.PGID		= '{@PGID}'
		AND   A.TBLSYU		= 'D'
		AND   A.FGJ			in ('0','1')
		AND   B.FGJ			in ('0','1')
		AND   C.FGJ			in ('0','1')
	<og:where startKey="and">
		<og:and value = "'QUERY' = '{@NMSYORI}'" />
	</og:where>
		union all
	<!-- NMSYORI="RESULT" は、PGIDに関連するGF05のテーブルの全カラム -->
		SELECT	A.SYSTEM_ID , B.PGID , 'RESULT' AS NMSYORI
			  , A.SEQNO as SEQ, A.CLM , A.TABLE_NAME, '' as ZOKUSEI , C.AS_TABLE , '' as FGJ , '' as UNIQ
		FROM GF05 A, GF90 B, GF91 C
		WHERE B.PGID		= C.PGID
		AND   A.TABLE_NAME	= C.TABLE_NAME
		AND   A.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   C.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.PGID		= '{@PGID}'
		AND   A.TBLSYU		= 'D'
		AND   A.FGJ			in ('0','1')
		AND   B.FGJ			in ('0','1')
		AND   C.FGJ			in ('0','1')
	<og:where startKey="and">
		<og:and value = "'RESULT' = '{@NMSYORI}'" />
	</og:where>
		union all
	<!-- NMSYORI="ORDER"  は、PGIDに関連するGF06のインデックスの備考 -->
		SELECT	A.SYSTEM_ID , B.PGID , 'ORDER' AS NMSYORI
				, 0 as SEQ, A.INDEX_NAME as CLM , A.TABLE_NAME
				, C.AS_TABLE || '.' || replace( A.BIKO,',', ',' || C.AS_TABLE || '.' ) as ZOKUSEI
				, C.AS_TABLE , '' as FGJ , '' as UNIQ
		FROM GF06 A, GF90 B, GF91 C
		WHERE B.PGID		= C.PGID
		AND   A.TABLE_NAME	= C.TABLE_NAME
		AND   A.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   C.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.PGID		= '{@PGID}'
		AND   A.TBLSYU		= 'D'
		AND   A.FGJ			in ('0','1')
		AND   B.FGJ			in ('0','1')
		AND   C.FGJ			in ('0','1')
	<og:where startKey="and">
		<og:and value = "'ORDER' = '{@NMSYORI}'" />
	</og:where>
		union all
	<!-- NMSYORI="JOIN"   は、PGIDに関連するGF07のテーブル違いの同一カラム -->
		SELECT	A.SYSTEM_ID , B.PGID , 'JOIN' AS NMSYORI
			  , A.SEQNO as SEQ, A.CLM , A.TABLE_NAME, D.TABLE_NAME as ZOKUSEI , E.AS_TABLE , '' as FGJ , '' as UNIQ
		FROM GF07 A, GF90 B, GF91 C , GF07 D, GF91 E
		WHERE B.PGID		= C.PGID
		AND   B.PGID		= E.PGID
		AND   A.TABLE_NAME	= C.TABLE_NAME
		AND   D.TABLE_NAME	= E.TABLE_NAME
		AND   D.TABLE_NAME	> A.TABLE_NAME
		AND   A.CLM			= D.CLM
		AND   A.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   C.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   D.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   E.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.PGID		= '{@PGID}'
		AND   A.TBLSYU		= 'D'
		AND   D.TBLSYU		= 'D'
		AND   A.FGJ			in ('0','1')
		AND   B.FGJ			in ('0','1')
		AND   C.FGJ			in ('0','1')
		AND   D.FGJ			in ('0','1')
		AND   E.FGJ			in ('0','1')
	<og:where startKey="and">
		<og:and value = "'JOIN' = '{@NMSYORI}'" />
	</og:where>
		order by SYSTEM_ID,PGID,NMSYORI,SEQ
</og:query>

<!-- SYSTEM_ID,PGID,NMSYORI,CLM,TABLE_NAME,ZOKUSEI でユニークになるように、データをマージします。 -->
<og:tableMerge action="GROUP" masterKeys="SYSTEM_ID,PGID,NMSYORI,CLM,TABLE_NAME,ZOKUSEI" />

<!-- CLM のレベルでユニークになるように、データをユニーク化します。 -->
<og:query command="NEW" debug="{@debug}" maxRowCount="{@maxRowCount}" scope="request" >
		SELECT	A.SYSTEM_ID , B.PGID , 'RESULT' AS NMSYORI
			  , '' as SEQ, A.CLM , MIN(A.TABLE_NAME) as TABLE_NAME, MIN(A.TABLE_NAME) || '.' || A.CLM as ZOKUSEI , MIN(C.AS_TABLE) AS AS_TABLE , '' as FGJ , '' as UNIQ
		FROM GF05 A, GF90 B, GF91 C
		WHERE B.PGID		= C.PGID
		AND   A.TABLE_NAME	= C.TABLE_NAME
		AND   A.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   C.SYSTEM_ID	= '{@SYSTEM_ID}'
		AND   B.PGID		= '{@PGID}'
		AND   A.TBLSYU		= 'D'
		AND   A.FGJ			in ('0','1')
		AND   B.FGJ			in ('0','1')
		AND   C.FGJ			in ('0','1')
		group by A.SYSTEM_ID , B.PGID , A.CLM
</og:query>

<!-- CLM のレベルでユニーク化したデータを、になるように、データをユニーク化します。 -->
<og:tableMerge action="UNION_CLM" masterKeys="SYSTEM_ID,PGID,NMSYORI,CLM,TABLE_NAME"  unionClms="ZOKUSEI" />

<!-- シーケンスデータを振りなおします。 -->
<og:tableFilter classId="SEQRESET" keys="SEQ_CLM" vals="SEQ" selectedAll="true" />

<form onSubmit="return oneClick();" method="POST" action="forward.jsp" target="RESULT" >
<og:writeCheck>
	<og:submit  value="entry"   lbl="ENTRY" accesskey="E" />
	<og:submit  value="reset"   lbl="RESET" accesskey="R" />
	<og:input type="reset" name="command" lbl="MSG0011" td="false" />
<br />
</og:writeCheck>

<og:view
	viewFormType = "HTMLTable"
	command    = "INSERT"
	noWritable = "UNIQ,FGJ"
	noDisplay  = "UNIQ,FGJ"
	checked    = "true"
	startNo    = "{@startNo}"
	pageSize   = "10000" />

<og:hidden name="sqlType" value="INSERT" />

</form>

<jsp:directive.include file="/jsp/common/htmlend.jsp" />
</jsp:root>
