How do I perform a function on an alias field?

I have a view in PostgreSQL where I am using the COALESCE function to pull the first NOT NULL field from a table. i.e.

SELECT 
SRC.SAP_SETNODE.SUBSET_CLS,

COALESCE(
    SETNODE_13.SUBSET_NAME,
    SETNODE_12.SUBSET_NAME,
    SETNODE_11.SUBSET_NAME,
    SETNODE_10.SUBSET_NAME,
    SETNODE_9.SUBSET_NAME,
    SETNODE_8.SUBSET_NAME,
    SETNODE_7.SUBSET_NAME,
    SETNODE_6.SUBSET_NAME,
    SETNODE_5.SUBSET_NAME,
    SETNODE_4.SUBSET_NAME,
    SETNODE_3.SUBSET_NAME,
    SETNODE_2.SUBSET_NAME,
    SETNODE_1.SUBSET_NAME,
    SRC.SAP_SETNODE.SUBSET_NAME,
    SRC.SAP_SETNODE.SET_NAME)
AS prctr1,

After I have this field I want to create a NEW field named prctr2 where I want to do a SPLIT PART & OVERLAY function on the prctr1 field if it is LIKE 'PC%'

I tried to do a CASE statement on this, along the lines of:

WHEN prctr1 LIKE 'PC%' THEN split_part( overlay(prctr1 placing '00000' from 1 for 2 ),'.',1)
ELSE prctr1

But I get a message saying that column prctr1 does not exist. Is there a way to make this happen?

Solution

Just encase your main query in an outer query where you will then perform the CASE statement like so:

   SELECT CASE WHEN prctr1 LIKE 'PC%' THEN split_part( overlay(prctr1 placing '00000' from 1 for 2 ),'.',1)
ELSE prctr1 end as pctrl1 
FROM (
SELECT 
SRC.SAP_SETNODE.SUBSET_CLS,

COALESCE(
    SETNODE_13.SUBSET_NAME,
    SETNODE_12.SUBSET_NAME,
    SETNODE_11.SUBSET_NAME,
    SETNODE_10.SUBSET_NAME,
    SETNODE_9.SUBSET_NAME,
    SETNODE_8.SUBSET_NAME,
    SETNODE_7.SUBSET_NAME,
    SETNODE_6.SUBSET_NAME,
    SETNODE_5.SUBSET_NAME,
    SETNODE_4.SUBSET_NAME,
    SETNODE_3.SUBSET_NAME,
    SETNODE_2.SUBSET_NAME,
    SETNODE_1.SUBSET_NAME,
    SRC.SAP_SETNODE.SUBSET_NAME,
    SRC.SAP_SETNODE.SET_NAME)
AS prctr1
) as foo