自动获得查询K3各种单据的SQL语句,自动得到相关字段,自动得到表关联关系!!!非常的方便,谁用谁知道.
-- 注意,
-- 1.将自动得到的SQL语句 Form 前面的逗号去掉
-- 2.可能有一条CASE语句,有点问题,将其屏蔽掉即可.(不知为何K3为何就那一条CASE语句有问题)
-- --调用实例
-- --exec CreateSql 61,'LAS-04002/07','销售订单'
--
CREATE PROCEDURE CreateSql
@FTemplateID int = 61,
@FBillNo varchar(20) = 'LAS-04002/07',
@OrderName varchar(20) = '销售订单'
AS
--ICChatBillTitle查询字段
select * from
(
select -100 AS HeadFInterID,'' AS BodyFInterID,'select ' as sql
union all
select TOP 1000 FInterID AS HeadFInterID,'' AS BodyFInterID,
case when Faction <> '' then Faction + ' AS [' + substring(FColCaption,1,len(FColCaption)-1) + '],'
else
case when FColCaption='$' and FColName='FInterID' then FTableAlias + '.' + FName + ' AS [单据内码],'
when FColCaption='$' and FColName='FEntryID' then FTableAlias + '.' + FName + ' AS [单据分录号],'
when FColCaption='$' and (FColName<>'FEntryID' and FColName<>'FInterID') then FTableAlias + '.' + FName + ' AS [' + FName + '],'
else FTableAlias + '.' + FName + ' AS [' + substring(FColCaption,1,len(FColCaption)-1) + '],'
end
end
as SQL
from icchatbilltitle
where FTypeID IN (@FTemplateID) and FName<>''
--order by FInterID
UNION ALL
select 9999 AS HeadFInterID,'' AS BodyFInterID,'from ' as sql
union all
select TOP 1000 10000 as HeadFInterID , FInterID AS BodyFInterID,
case when FInterID < 0 then FTableName + ' ' + FTableNameAlias + ' '
else ''
end +
case when FLogic='=' then 'Inner Join'
when FLogic='*=' then 'left outer Join'
when FLogic='=*' then 'right outer Join'
when FLogic='*=*' then 'full outer Join'
else ' '
end
+ ' ' +
FTableName11 + ' ' + FTableNameAlias11
+ ' on ' + FTableNameAlias + '.'+ FFieldName + '=' + FTableNameAlias11 + '.' + FFieldName11 as SQL
from ICTableRelation
where Ftypeid in (@FTemplateID) and FTableNameAlias11<>'##BASE##'
--order by Finterid
union
select 10001 as HeadFInterID , FInterID AS BodyFInterID,
'Where ' + FTableNameAlias11+'.FStandard=1' as sql from ICTableRelation where Ftypeid in (@FTemplateID) and FTableNameAlias11<>'##BASE##'
and FTableName11='t_MeasureUnit' and FFieldName11='FUnitGroupID'
union
select 10002 as HeadFInterID , 10002 AS BodyFInterID,
'and ' + 'v1.FTranType= ' + cast((select FID from ICTransActionType where FName like @OrderName) as char(4)) as sql
union
select 10003 as HeadFInterID , 10003 AS BodyFInterID,
'and ' + 'v1.FBillNo= ''' + @FBillNo + ''' ' as sql
) as TT order by HeadFInterID,BodyFInterID
|
