查询方案表——
Query_QueryCase
-
ID
-
CreatedBy 创建人
-
CreatedOn 创建时间
-
Modified By 修改 人
-
ModifiedOn 修改时间
-
SysVersion 版本
-
CaseGuid
-
CaseModelID
-
CaseType 方案类型
-
--Columns 栏目字段 Query_Columns表
-
CustomOQLFilter 自定义OQL条件
-
Description 描述信息
-
ExtendTag
-
FilterDefines
-
FilterValues
-
--Groups 分组字段 Query_Groups表
-
IsAllowShare 是否允许共享
-
IsMyDefault IsMyDefault
-
Organization 组织ID
-
--Sorts 排序字段 Query_Sorts表
-
Title 方案名称
-
UserID 用户ID
查询-栏目字段表
Query_Columns
-
ID
-
CreatedBy 创建人
-
CreatedOn 创建时间
-
Modified By 修改 人
-
ModifiedOn 修改时间
-
SysVersion 版本
-
Attribute 属性
-
AttributeType 属性类型
-
AttrID 属性ID
-
CaseGuid
-
CaseModelID 查询方案GUID
-
DataType 数据类型
-
DataTypeID 数据类型ID
-
DateDisplayFormat 日期显示格式
-
DisplayFormat 格式
-
EntityFullName 实体全名
-
ExtendXML 扩展XML
-
ExXML
-
FieldTitle 显示名称
-
IsFixed 是否固定列
-
IsFrozen 是否冻结
-
IsGlobalized 是否国际化字段
-
IsLink 是否链接
-
IsSum 是否合计
-
IsUserDefine 是否是用户自定义栏目
-
Item 栏目项
-
LineDisplayType 行显示方式
-
LinkMethodName 链接名称
-
NumberDisplayPoint 数字显示精度
-
QueryCase 查询方案 - Query_QueryCase表
-
Sequence 序号
-
Width 栏目显示长度
select
qc.Title as 方案名称,
col.Item as 栏目项,
--qc.UserID as 用户ID,
us2.DisplayName as 用户名,
qc.Organization as 组织ID,
qc.ID,
qc.CreatedBy as 创建人,
qc.CreatedOn as 创建时间,
qc.ModifiedBy as 修改人,
qc.ModifiedOn as 修改时间,
qc.SysVersion as 版本, org2.Name as 组织,
CASE qc.CaseType
WHEN 0 THEN '默认方案'
WHEN 1 THEN '共有方案'
WHEN 2 THEN '私有方案'
WHEN 3 THEN '描述性弹性域方案'
ELSE ''
END as 方案类型,
--qc.Columns as 栏目字段, --Query_Columns表
--col.ID as 栏目字段表ID,
qc.CustomOQLFilter as 自定义OQL条件 ,
qc.Description as 描述信息,
--qc.Groups as 分组字段, --Query_Groups表
--gro.ID as 分组字段ID,
CASE qc.IsAllowShare
WHEN 0 THEN '否'
WHEN 1 THEN '是'
ELSE ''
END as 是否允许共享,
qc.IsMyDefault as 我的默认,
--qc.Sorts as 排序字段, --Query_Sorts表
--sor.Item as 排序项,
--sor.Sequence as 排序,
-- qc.CaseGuid,
-- qc.CaseModelID,
-- qc.ExtendTag,
-- qc.FilterDefines,
-- qc.FilterValues,
col.Attribute as 属性,
CASE col.AttributeType
WHEN 0 THEN '实体类型'
WHEN 1 THEN '枚举类型'
WHEN 2 THEN '属性类型'
WHEN 3 THEN '集合类型'
WHEN 4 THEN '基本类型'
ELSE ''
END as 属性类型,
-- col.AttrID as 属性ID,
-- col.CaseGuid,
col.CaseModelID as 查询方案GUID,
col.DataType as 数据类型,
col.DataTypeID as 数据类型ID,
col.DateDisplayFormat as 日期显示格式,
col.DisplayFormat as 格式,
col.EntityFullName as 实体全名,
col.ExtendXML as 扩展XML,
col.ExXML,
--col.FieldTitle as 显示名称,
col.IsFixed as 是否固定列,
col.IsFrozen as 是否冻结,
col.IsGlobalized as 是否国际化字段,
col.IsLink as 是否链接,
col.IsSum as 是否合计,
col.IsUserDefine as 是否是用户自定义栏目,
col.LineDisplayType as 行显示方式,
col.LinkMethodName as 链接名称,
col.NumberDisplayPoint as 数字显示精度,
col.QueryCase as 查询方案,--Query_QueryCase表,
col.Sequence as 序号,
col.Width as 栏目显示长度
from Query_Columns as col
LEFT JOIN Query_Columns_Trl as col2 on col2.SysMLFlag = 'zh-CN' and col.ID = col2.ID
LEFT JOIN Query_QueryCase as qc on col.QueryCase = qc.ID
LEFT JOIN Base_User as us on qc.UserID = us.ID
LEFT JOIN Base_User_Trl as us2 on us2.SysMLFlag = 'zh-CN' and us.ID = us2.ID
LEFT JOIN Base_Organization as org on qc.Organization = org.ID
LEFT JOIN Base_Organization_Trl as org2 on org2.SysMLFlag = 'zh-CN' and org.ID = org2.ID
where
col.EntityFullName = 'UFIDA.U9.CBO.SCM.Item.ItemMaster'
and org2.Name = '青岛****有限公司'
and qc.CaseType !=-1
and qc.CreatedBy = '张**'
and qc.Title !='预置方案'
select
col.Item as 栏目项,
qc.ID,
qc.CreatedBy as 创建人,
qc.CreatedOn as 创建时间,
qc.ModifiedBy as 修改人,
qc.ModifiedOn as 修改时间,
qc.SysVersion as 版本,
qc.Title as 方案名称,
--qc.UserID as 用户ID,
us2.DisplayName as 用户名,
qc.Organization as 组织ID,
org2.Name as 组织,
CASE qc.CaseType
WHEN 0 THEN '默认方案'
WHEN 1 THEN '共有方案'
WHEN 2 THEN '私有方案'
WHEN 3 THEN '描述性弹性域方案'
ELSE ''
END as 方案类型,
--qc.Columns as 栏目字段, --Query_Columns表
--col.ID as 栏目字段表ID,
qc.CustomOQLFilter as 自定义OQL条件 ,
qc.Description as 描述信息,
--qc.Groups as 分组字段, --Query_Groups表
--gro.ID as 分组字段ID,
CASE qc.IsAllowShare
WHEN 0 THEN '否'
WHEN 1 THEN '是'
ELSE ''
END as 是否允许共享,
qc.IsMyDefault as 我的默认,
--qc.Sorts as 排序字段, --Query_Sorts表
--sor.Item as 排序项,
--sor.Sequence as 排序,
qc.CaseGuid,
qc.CaseModelID,
qc.ExtendTag,
qc.FilterDefines,
qc.FilterValues
from Query_QueryCase as qc
LEFT JOIN Base_User as us on qc.UserID = us.ID
LEFT JOIN Base_User_Trl as us2 on us2.SysMLFlag = 'zh-CN' and us.ID = us2.ID
LEFT JOIN Base_Organization as org on qc.Organization = org.ID
LEFT JOIN Base_Organization_Trl as org2 on org2.SysMLFlag = 'zh-CN' and org.ID = org2.ID
LEFT JOIN Query_Columns as col on qc.id = col.QueryCase
LEFT JOIN Query_Columns_Trl as col2 on col2.SysMLFlag = 'zh-CN' and col.ID = col2.ID
LEFT JOIN Query_Groups as gro on qc.id = gro.QueryCase
--LEFT JOIN Query_Sorts as sor on qc.id = sor.QueryCase
where
org2.Name = '青岛****有限公司'
and qc.CaseType !=-1
and qc.CreatedBy = '张**'
and qc.Title !='预置方案'
--and qc.Title like '%对%'
and qc.id =1002206090056346