金蝶云

金蝶云 弹性域-仓位 动态关联查询

declare @Columns table(FNO INT,NAME VARCHAR(200)) insert into @Columns Select ROW_NUMBER()over(order by Name),Name FROM SysColumns Where id=Object_Id('T_BAS_FlexValuesDetail') AND Name LIKE 'FF%' declare @NAME VARCHAR(max),@NAME2 VARCHAR(max),@NAME3 VARCHAR(max),@NAME4 VARCHAR(max),@NAME5 VARCHAR(max),@i int,@NAME6 VARCHAR(max) set @NAME='' set @NAME2='' set @NAME3='' set @NAME4='' set @NAME5='' set @NAME6='' set @i=1 while @i<=(select count(*) from @Columns) begin set @NAME2=@NAME2+' when isnull(locCol'+cast(@i as varchar(10))+'.FENTRYID,0)<>0 then isnull(locCol'+cast(@i as varchar(10))+'.FENTRYID,0) ' set @NAME3=@NAME3+' when isnull(locCol'+cast(@i as varchar(10))+'.FNUMBER,'''')<>'''' then isnull(locCol'+cast(@i as varchar(10))+'.FNUMBER,'''') ' set @NAME4=@NAME4+' when isnull(locCol'+cast(@i as varchar(10))+'.FNAME,'''')<>'''' then isnull(locCol'+cast(@i as varchar(10))+'.FNAME,'''') ' set @NAME5=@NAME5+' left join (SELECT I.*,J.FNAME FROM T_BAS_FLEXVALUESENTRY I INNER JOIN T_BAS_FLEXVALUESENTRY_L J ON I.FENTRYID=J.FENTRYID AND J.FLOCALEID=2052) locCol'+cast(@i as varchar(10))+' on loc.'+(select NAME from @Columns where FNO=@i)+'=locCol'+cast(@i as varchar(10))+'.FENTRYID ' set @NAME6=@NAME6+' when isnull(locCol'+cast(@i as varchar(10))+'.FENTRYID,0)<>'''' then '''+(select NAME from @Columns where FNO=@i)+''' ' set @i=@i+1 end if exists (select 1 from @Columns) set @NAME='select loc.FID,case '+@NAME2+'else 0 end FCWID,CASE '+@NAME3+'ELSE '''' END FCWNUMBER,CASE '+@NAME4+'ELSE '''' END FCWNAME,case '+@NAME6+'ELSE '''' END FColumn from T_BAS_FlexValuesDetail loc '+@NAME5 EXEC (@NAME)

查询结果： FID 维度关联字段ID FCWID 仓位基础资料ID FCWNUMBER 仓位编码 FCWNAME 仓位名称 FColumn 维度资料列名