v_project

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

sap

VARCHAR

4

√

null

siteid

INT

10

√

null

prjid

INT

10

√

0

sd_id

VARCHAR

16

√

null

sd_group

VARCHAR

14

Relationships

Close relationships within degrees of separation

One

View Definition

select `tb2`.`site`.`sap` AS `sap`,`tb2`.`project`.`site_id` AS `siteid`,`tb2`.`project`.`id` AS `prjid`,cast((case when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','CN') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation')) then concat(`tb2`.`site`.`sap`,'.','CR') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation') and (`tb2`.`project`.`relo_forced` = 1)) then concat(`tb2`.`site`.`sap`,'.','FCR') when (`tb2`.`site`.`flagship` = 1) then concat(`tb2`.`site`.`sap`,'.','FS') when ((`tb2`.`project_type`.`id` = 3) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat(`tb2`.`site`.`sap`,'.','BW') when ((`tb2`.`project_type`.`id` in (13,14)) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat(`tb2`.`site`.`sap`,'.','BWK') when ((`tb2`.`project_type`.`id` = 26) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat(`tb2`.`site`.`sap`,'.','BWDG') when ((`tb2`.`project_type`.`id` = 27) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat(`tb2`.`site`.`sap`,'.','BWDP') when ((`tb2`.`project_type`.`id` = 29) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat(`tb2`.`site`.`sap`,'.','KBW') when (`tb2`.`project_type`.`id` = 22) then concat(`tb2`.`site`.`sap`,'.','AS') when (`tb2`.`project_type`.`id` = 30) then concat(`tb2`.`site`.`sap`,'.','Pro.HVAC14') when (`tb2`.`project_type`.`id` = 31) then concat(`tb2`.`site`.`sap`,'.','SF14') when (`tb2`.`project_type`.`id` = 32) then concat(`tb2`.`site`.`sap`,'.','SG14') when (`tb2`.`project_type`.`id` = 33) then concat(`tb2`.`site`.`sap`,'.','PL14') when (`tb2`.`project_type`.`id` = 35) then concat(`tb2`.`site`.`sap`,'.','FM.FLOORING') when (`tb2`.`project_type`.`id` = 36) then concat(`tb2`.`site`.`sap`,'.','FM.Re.HVAC') when (`tb2`.`project_type`.`id` = 34) then concat(`tb2`.`site`.`sap`,'.','FM.GEN') when (`tb2`.`project_type`.`id` = 37) then concat(`tb2`.`site`.`sap`,'.','FM.POS') when (`tb2`.`project_type`.`id` = 38) then concat(`tb2`.`site`.`sap`,'.','FM.Signage') when (`tb2`.`project_type`.`id` = 39) then concat(`tb2`.`site`.`sap`,'.','WW') when (`tb2`.`project_type`.`id` in (18,19)) then concat(`tb2`.`site`.`sap`,'.','OEM') when (`tb2`.`project_type`.`id` = 25) then concat(`tb2`.`site`.`sap`,'.','LBTY') when (`tb2`.`project_type`.`id` = 17) then concat(`tb2`.`site`.`sap`,'.','SRP') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'Closure')) then concat(`tb2`.`site`.`sap`,'.','CC') when ((`tb2`.`project`.`channel_code` in ('ERP2','ERPL','TPR2','TPR3','TPRI','TPRT')) and (`tb2`.`project_type`.`name` = 'Closure')) then concat(`tb2`.`site`.`sap`,'.','TC') when ((`tb2`.`project`.`channel_code` = 'TPR3') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','30') when ((`tb2`.`project`.`channel_code` = 'TPR2') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','25') when ((`tb2`.`project`.`channel_code` = 'TPRI') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','I') when ((`tb2`.`project`.`channel_code` = 'TPRT') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','it14') when ((`tb2`.`project`.`channel_code` in ('ERP2','ERPL','TPR2','TPR3','TPRI','TPRT')) and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation')) then concat(`tb2`.`site`.`sap`,'.','TR') when ((`tb2`.`project_type`.`id` = 3) and (`tb2`.`project`.`channel_code` <> 'CORP')) then concat(`tb2`.`site`.`sap`,'.','TBW') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'Facilities Upgrade') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','FM.KR') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','EDP') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'Brand Wrap-GDC') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','EDP.BW') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'Signage') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','EDP.SRP') when ((`tb2`.`site`.`brand_id` = 2) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','GSMEDP') when ((`tb2`.`site`.`brand_id` = 2) and (`tb2`.`project`.`channel_code` = 'ESDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat(`tb2`.`site`.`sap`,'.','GSMESDP') else 'Other' end) as char charset latin1) AS `sd_id`,cast((case when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.CN') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation')) then concat('SD.CR') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation') and (`tb2`.`project`.`relo_forced` = 1)) then concat('SD.FCR') when (`tb2`.`site`.`flagship` = 1) then concat('SD.FS') when ((`tb2`.`project_type`.`id` = 3) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat('SD.BW') when ((`tb2`.`project_type`.`id` in (13,14)) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat('SD.BWK') when ((`tb2`.`project_type`.`id` = 26) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat('SD.BWDG') when ((`tb2`.`project_type`.`id` = 27) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat('SD.BWDP') when ((`tb2`.`project_type`.`id` = 29) and (`tb2`.`project`.`channel_code` = 'CORP')) then concat('SD.KBW') when (`tb2`.`project_type`.`id` = 22) then concat('SD.AS') when (`tb2`.`project_type`.`id` = 30) then concat('SD.Pro.HVAC14') when (`tb2`.`project_type`.`id` = 31) then concat('SD.SF14') when (`tb2`.`project_type`.`id` = 32) then concat('SD.SG14') when (`tb2`.`project_type`.`id` = 33) then concat('SD.PL14') when (`tb2`.`project_type`.`id` = 35) then concat('SD.FM.FLOORING') when (`tb2`.`project_type`.`id` = 36) then concat('SD.FM.Re.HVAC') when (`tb2`.`project_type`.`id` = 34) then concat('SD.FM.GEN') when (`tb2`.`project_type`.`id` = 37) then concat('SD.FM.POS') when (`tb2`.`project_type`.`id` = 38) then concat('SD.FM.Signage') when (`tb2`.`project_type`.`id` = 39) then concat('SD.WW') when (`tb2`.`project_type`.`id` in (18,19)) then concat('SD.OEM') when (`tb2`.`project_type`.`id` = 25) then concat('SD.LBTY') when (`tb2`.`project_type`.`id` = 17) then concat('SD.SRP') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'Closure')) then concat('SD.CC') when ((`tb2`.`project`.`channel_code` in ('ERP2','ERPL','TPR2','TPR3','TPRI','TPRT')) and (`tb2`.`project_type`.`name` = 'Closure')) then concat('SD.TC') when ((`tb2`.`project`.`channel_code` = 'TPR3') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.30') when ((`tb2`.`project`.`channel_code` = 'TPR2') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.25') when ((`tb2`.`project`.`channel_code` = 'TPRI') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.I') when ((`tb2`.`project`.`channel_code` = 'TPRT') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.it14') when ((`tb2`.`project`.`channel_code` in ('ERP2','ERPL','TPR2','TPR3','TPRI','TPRT')) and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'Relocation')) then concat('SD.TR') when ((`tb2`.`project_type`.`id` = 3) and (`tb2`.`project`.`channel_code` <> 'CORP')) then concat('SD.TBW') when ((`tb2`.`project`.`channel_code` = 'CORP') and (`tb2`.`project_type`.`name` = 'Facilities Upgrade') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.FM.KR') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.EDP') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'Brand Wrap-GDC') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.EDP.BW') when ((`tb2`.`site`.`brand_id` = 1) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'Signage') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.EDP.SRP') when ((`tb2`.`site`.`brand_id` = 2) and (`tb2`.`project`.`channel_code` = 'EDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.GSMEDP') when ((`tb2`.`site`.`brand_id` = 2) and (`tb2`.`project`.`channel_code` = 'ESDP') and (`tb2`.`project_type`.`name` = 'New Site') and (`tb2`.`site_transaction_type`.`transaction_type` = 'New')) then concat('SD.GSMESDP') else 'Other' end) as char charset latin1) AS `sd_group` from ((((`tb2`.`site` left join `tb2`.`site_transaction_type` on((`tb2`.`site`.`transaction_type_id` = `tb2`.`site_transaction_type`.`id`))) left join `tb2`.`site_brand` on((`tb2`.`site`.`brand_id` = `tb2`.`site_brand`.`id`))) left join `tb2`.`project` on((`tb2`.`project`.`site_id` = `tb2`.`site`.`id`))) left join `tb2`.`project_type` on((`tb2`.`project`.`project_type_id` = `tb2`.`project_type`.`id`)))

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views