select 
  CODE, 
  NAME, 
  STGRPCODE, 
  (
    select 
      TOP 1 S.DEFINITION_ 
    from 
      LG_301_SPECODES S 
    WHERE 
      (
        (
          (S.CODETYPE = 4) 
          AND (S.SPECODETYPE = 0)
        ) 
        OR (
          (S.CODETYPE = 4) 
          AND (S.SPECODETYPE > 0)
        ) 
        OR (
          (S.CODETYPE > 4)
        )
      ) 
      AND (
        (
          (S.CODETYPE = 4) 
          AND (S.SPECODETYPE <= 0)
        ) 
        OR (
          (S.CODETYPE < 4)
        )
      ) 
      AND S.SPECODE = I.STGRPCODE
  ) STGRPCODE_A, 
  SPECODE, 
  (
    select 
      TOP 1 S.DEFINITION_ 
    from 
      LG_301_SPECODES S 
    WHERE 
      S.SPECODE = I.SPECODE 
      AND S.SPETYP1 = 1 
      AND S.CODETYPE = 1 
      AND S.SPECODETYPE = 1
  ) SPECODE_A, 
  SPECODE2, 
  (
    select 
      TOP 1 S.DEFINITION_ 
    from 
      LG_301_SPECODES S 
    WHERE 
      S.SPECODE = I.SPECODE2 
      AND S.SPETYP2 = 1 
      AND S.CODETYPE = 1 
      AND S.SPECODETYPE = 1
  ) SPECODE2_A, 
  SPECODE3, 
  (
    select 
      TOP 1 S.DEFINITION_ 
    from 
      LG_301_SPECODES S 
    WHERE 
      S.SPECODE = I.SPECODE3 
      AND S.SPETYP3 = 1 
      AND S.CODETYPE = 1 
      AND S.SPECODETYPE = 1
  ) SPECODE3_A, 
  SPECODE4, 
  (
    select 
      TOP 1 S.DEFINITION_ 
    from 
      LG_301_SPECODES S 
    WHERE 
      S.SPECODE = I.SPECODE4 
      AND S.SPETYP4 = 1 
      AND S.CODETYPE = 1 
      AND S.SPECODETYPE = 1
  ) SPECODE4_A 
from 
  LG_301_ITEMS I