Combined Customized SQL Scripts

Customized SQL scripts, written to meet different requirements, can be combined into a single script at the project database. For example:

-- Set instrument typical process owner to instrument parent symbol tag name

UPDATE INSTLIST

SET [Process Owner] = OtherTable.[Process Owner] ,

[Inst Process Owner] = OtherTable.[Inst Process Owner]

FROM (

SELECT Handle1, [DRG+SHT], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS OtherTable

WHERE

OtherTable.Handle1 = substring(INSTLIST.Handle1, 1, CHARINDEX('#',INSTLIST.Handle1)-1)

and INSTLIST.Handle1 like ('%#%')

and OtherTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Process Owner] = ''

UPDATE INSTLIST

SET [Process Owner] = OtherTable.[Instrument Label] ,

[Inst Process Owner] = OtherTable.[Instrument Label]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner] , [Instrument Label]

FROM INSTLIST) AS OtherTable

WHERE

OtherTable.[Handle1] like '%#%'

and INSTLIST.Handle1 like ('%#%')

and OtherTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Inst Owner Handle] = substring(OtherTable.Handle1, 1, CHARINDEX('#',OtherTable.Handle1)-1)

and substring(INSTLIST.[Handle1], CHARINDEX('#',INSTLIST.[Handle1])+1, 3) = substring(OtherTable.[Handle1], CHARINDEX('#',OtherTable.Handle1)+1, 3)

-- Update instrument process owner where process owner is another instrument

UPDATE INSTLIST

SET [Inst Owner Handle] = SecTable.[Inst Owner Handle]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS SecTable

WHERE

INSTLIST.Handle1 like ('%#%')

and SecTable.[Handle1] = substring(INSTLIST.Handle1, 1, CHARINDEX('#',INSTLIST.Handle)-1)

and SecTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Update instrument process owner for equipment process owner getting specific typical nozzle tag.

UPDATE INSTLIST

SET [Process Owner] = NozTable.[FULL TAG] , [Inst Process Owner] = NozTable.[Equipment Label]

FROM (

SELECT Handle, [DRG+SHT], [FULL TAG] , [Equipment Label]

FROM NOZLIST) As NozTable

WHERE

INSTLIST.Handle1 like '%#%'

AND NozTable.Handle LIKE '%#%'

and INSTLIST.[Inst Owner Handle] = substring(NozTable.[Handle], 1, CHARINDEX('#',NozTable.Handle)-1)

and substring(INSTLIST.[Handle1], CHARINDEX('#',INSTLIST.Handle1)+1, 3) = substring(NozTable.[Handle], CHARINDEX('#',NozTable.Handle) + 1, 3)

and NozTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Reset Owner Handle to match it to parent symbol owner handle

UPDATE INSTLIST

SET [Inst Owner Handle] = ''

where

[Handle] like '%*%'

UPDATE INSTLIST

SET [Inst Owner Handle] = SecTable.[Inst Owner Handle]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS SecTable

WHERE

INSTLIST.Handle1 like ('%*%')

and SecTable.[Handle1] = substring(INSTLIST.Handle1, 1, CHARINDEX('*',INSTLIST.Handle)-1)

and SecTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Update instrument process owner where instrument is generated by a pattern

UPDATE INSTLIST

set [Inst Owner Handle] = SecTable.[Inst Owner Handle]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS SecTable

WHERE

INSTLIST.Handle1 like ('%*%')

and SecTable.[Handle1] = substring(INSTLIST.Handle1, 1, CHARINDEX('*',INSTLIST.Handle)-1)

and SecTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Process Owner] = ''

UPDATE INSTLIST

SET [Process Owner] = OtherTable.[Instrument Label] ,

[Inst Process Owner] = OtherTable.[Instrument Label]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner] , [Instrument Label]

FROM INSTLIST) AS OtherTable

WHERE

INSTLIST.Handle1 like ('%*%')

and OtherTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Inst Owner Handle] = OtherTable.[Handle1]

-- Update instrument process owner generated by patterns for equipment process owner getting specific nozzle tag.

UPDATE INSTLIST

SET [Process Owner] = NozTable.[FULL TAG] , [Inst Process Owner] = NozTable.[Equipment Label]

FROM (

SELECT Handle, [DRG+SHT], [FULL TAG] ,[Equipment Label]

FROM NOZLIST) As NozTable

WHERE

INSTLIST.Handle1 like '%*%'

and INSTLIST.[Inst Owner Handle] = NozTable.[Handle]

and NozTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Find instrument process owner connected to valve table

UPDATE INSTLIST

SET [Process Owner] = VLVTable.[Process Owner] , [Inst Process Owner] = VLVTable.[Process Owner]

FROM (

SELECT Handle, [DRG+SHT], [Process Owner] ,[Valve Tag]

FROM VLVLIST) As VLVTable

WHERE

INSTLIST.[Process Owner] = ''

and INSTLIST.[Inst Owner Handle] = VLVTable.[Handle]

and VLVTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Find instrument process owner connected to valve record generated with typicals

UPDATE INSTLIST

SET [Process Owner] =VLVTable.[Process Owner] , [Inst Process Owner] = VLVTable.[Process Owner]

FROM (

SELECT Handle, [DRG+SHT], [Process Owner] ,[Valve Tag]

FROM VLVLIST) As VLVTable

WHERE

INSTLIST.Handle1 like '%#%'

AND VLVTable.Handle LIKE '%#%'

and INSTLIST.[Inst Owner Handle] = substring(VLVTABLE.[Handle], 1, CHARINDEX('#',VLVTable.Handle)-1)

and substring(INSTLIST.[Handle1], CHARINDEX('#',INSTLIST.Handle1)+1, 3) = substring(VLVTable.[Handle], CHARINDEX('#',VLVTable.Handle) + 1, 3)

and VLVTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

UPDATE INSTLIST

SET [Process Owner] =VLVTable.[Valve Tag] , [Inst Process Owner] = VLVTable.[Valve Tag]

FROM (

SELECT Handle, [DRG+SHT], [Process Owner] ,[Valve Tag]

FROM VLVLIST) As VLVTable

WHERE

VLVTable.[Valve Tag] <> ''

and INSTLIST.Handle1 like '%#%'

AND VLVTable.Handle LIKE '%#%'

and INSTLIST.[Inst Owner Handle] = substring(VLVTABLE.[Handle], 1, CHARINDEX('#',VLVTable.Handle)-1)

and substring(INSTLIST.[Handle1], CHARINDEX('#',INSTLIST.Handle1)+1, 3) = substring(VLVTable.[Handle], CHARINDEX('#',VLVTable.Handle) + 1, 3)

and VLVTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Find instrument process owner connected to line fitting

UPDATE INSTLIST

SET [Process Owner] = LIFTABLE.[Process Owner] , [Inst Process Owner] = LIFTable.[Process Owner]

FROM (

SELECT Handle, [DRG+SHT], [Process Owner] ,[ValveTag] , [Special Item No]

FROM INLNLIST) As LIFTable

WHERE

INSTLIST.[Process Owner] = ''

and INSTLIST.[Inst Owner Handle] =LIFTable.[Handle]

and liftable.[DRG+SHT] = INSTLIST.[DRG+SHT]