GCT Utilities

  • Title test1
  • Writer Minwoo, Song Date 2023-11-14/18:53 Read 34
      SELECT [full_name], t.name AS [name]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCMEASURING_COMPONENT',                   0) AS [LGCMEASURING_COMPONENT]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCLOCATION_ON_INSTALLATION',              0) AS [LGCLOCATION_ON_INSTALLATION]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCRESPONSE_TIME',                               0) AS [LGCRESPONSE_TIME]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCSENSING_PRINCIPLE',                           0) AS [LGCSENSING_PRINCIPLE]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCALARM_SET_POINT_1ST',                   0) AS [LGCALARM_SET_POINT_1ST]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCALARM_SET_POINT_2ND',                   0) AS [LGCALARM_SET_POINT_2ND]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCMEASURING_RANGE',                             0) AS [LGCMEASURING_RANGE]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCLOCATION_ON_ALARM_INSTALLATION',  0) AS [LGCLOCATION_ON_ALARM_INSTALLATION]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCACCURACY',                                          0) AS [LGCACCURACY]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCCALIBRATION_PERIOD',                    0) AS [LGCCALIBRATION_PERIOD]
                  , dbo.getDatAttValueWithUoM(i.noderef, '@LGCOTHERS',                                      0) AS [LGCOTHERS]
      FROM lgc.dbo.vnet_tag_i t
                         INNER JOIN
                   lgc.dbo.vnet_instance_i i
                         ON t.parent = i.noderef
                         INNER JOIN
                   lgc.dbo.vnet_class_i c
                         ON i.class = c.noderef
             WHERE t.context in (select t_plant.noderef from lgc.dbo.vnet_instance_i i_plant inner join lgc.dbo.vnet_tag_i t_plant on t_plant.noderef = i_plant.tag
                              where class in (select noderef from lgc.dbo.vnet_class_i where name = 'PLANT') and t_plant.name != 'LGCHEM')
             AND c.name in (      'FTC_GAS_DETECTOR_050-030-020',
                                          'FTC_LIQUID_LEAKAGE_DETECTOR_050-030-030')
로그인 하시면 댓글을 남길 수 있습니다.
  • Minwoo, Song(2023.11.14/18:55:37) Reply
  • ALTER FUNCTION [dbo].[getDatAttValueWithUoM]
    (
          @noderef int,
          @attName nvarchar(255),
          @with bit
    )
    RETURNS nvarchar(max)
    AS
    BEGIN
          DECLARE @ResultVar nvarchar(max);

          select @ResultVar = case when @with = 1 then value + c.abbrev else value end
           from lgc.dbo.vnet_attribute_i a
                       left outer join
                 lgc.dbo.vnet_uom_i u
                       on a.uom = u.noderef
                       left outer join
                 lgc.dbo.vnet_class_i c
                       on u.uom = c.noderef
           where a.parent in (select target
                                        from lgc.dbo.vnet_association_i
                                   where source = @noderef
                                         and type = (select noderef from lgc.dbo.vnet_association_type_i where name = 'DAT'))
           and a.noderef = (select noderef from lgc.dbo.vnet_class_i where name = @attName)
          ;

          RETURN @ResultVar;
    END
  • Minwoo, Song(2023.11.14/19:08:15) Reply
  • ALTER FUNCTION [dbo].[GetTopDown](@DeptCd char(5))
    RETURNS @rTable TABLE (
      [DeptCd] nvarchar(50),
      [DeptName] nvarchar(50),
      [ParentDeptCd] nvarchar(50),
      [Depth] int
    )
    AS
    BEGIN
      WITH OrgTree(DeptCd, DeptNAME, ParentDeptCd, Depth) AS (
        SELECT DeptCd, DeptNAME, ParentDeptCd, 0 AS Depth
        FROM Company
        WHERE DeptCd = @DeptCd

        UNION ALL

        SELECT COM.DeptCd, COM.DeptNAME, COM.ParentDeptCd, ORG.Depth + 1
        FROM Company AS COM
        INNER JOIN OrgTree AS ORG
          ON COM.ParentDeptCd = ORG.DeptCd
      )
      INSERT @rTable
      SELECT DeptCd, DeptNAME, ParentDeptCd, Depth
      FROM OrgTree
      OPTION (MAXRECURSION 100);

    RETURN
    END
    출처: https://spaghetti-code.tistory.com/22 [어떻게 짤것인가:티스토리]
  • Minwoo, Song(2023.11.14/21:56:51) Reply
  • MSSQL - 재귀 쿼리. 제일 간단
    --------------------------------
    WITH TLB_CTE (id, parent_id) AS
    (
      SELECT id, parent_id
      FROM [db_edms].[dbo].[tbl_tree]
      WHERE parent_id = 2
      UNION ALL
      SELECT a.id, a.parent_id
      FROM [db_edms].[dbo].[tbl_tree] a INNER JOIN TLB_CTE b ON a.parent_id = b.id
    )
    SELECT id, parent_id
    FROM TLB_CTE
    where id in
    (select id from TLB_CTE)
    order by parent_id asc;
Privious ▲ :
iis cookie
Next ▼ :
SQL 모든 테이블 검색