Problem with alias in join statiment

I have problem with following query:

SELECT 
    g_contac.contid, g_contac.name, g_contac.email, f_sync.foreign_key,
    (
        SELECT COUNT(g_cpers.cpersid) 
        FROM g_cpers 
        WHERE g_cpers.contid = g_contac.contid
    ) AS employee_count
FROM f_sync 
    FULL OUTER JOIN g_contac ON 
    (
       开发者_运维百科 g_contac.contid = f_sync.external_id AND 
        model = case when f_sync.employee_count = 0 then 'PRIVATE' else 'COMPANY' end
    )
WHERE model = 'COMPANY' or model = 'PRIVATE' OR model IS null

When I execute it, I get error:

Invalid column name 'employee_count'.

How to solve this problem?

---

That's because you mention f_sync.employee_count in your query, but f_sync doesn't have a column called employee_count: you just created a dynamic column in the query with the alias employee_count.

Simple fix is to repeat the calculation:

SELECT 
g_contac.contid, g_contac.name, g_contac.email, f_sync.foreign_key,
(
    SELECT COUNT(g_cpers.cpersid) 
    FROM g_cpers 
    WHERE g_cpers.contid = g_contac.contid
) AS employee_count
FROM f_sync 
FULL OUTER JOIN g_contac ON 
(
    g_contac.contid = f_sync.external_id AND 
    model = case when (SELECT COUNT(g_cpers.cpersid) 
    FROM g_cpers 
    WHERE g_cpers.contid = g_contac.contid) = 0 then 'PRIVATE' else 'COMPANY' end
)
WHERE model = 'COMPANY' or model = 'PRIVATE' OR model IS null;

Better fix is to create a view that includes this column, which would mean it would only be calculated once;

Edit: Improved query and incorporated comments

You could improve the clarity a bit by using the SQL not exists instead of count(*) = 0:

SELECT 
g_contac.contid, g_contac.name, g_contac.email, f_sync.foreign_key
FROM f_sync 
FULL OUTER JOIN g_contac ON 
(
    g_contac.contid = f_sync.external_id AND 
    model = case when not exists (SELECT * FROM g_cpers 
      WHERE g_cpers.contid = g_contac.contid) then 'PRIVATE' else 'COMPANY' end
)
WHERE model = 'COMPANY' or model = 'PRIVATE' OR model IS null;

---

Can you try this one:

SELECT 
    gt.contid, gt.name, gt.email, f_sync.foreign_key,
    gt.employee_count
FROM f_sync 
    FULL OUTER JOIN
    (   SELECT
            g_contac.contid, g_contac.name, g_contac.email,
            (
                SELECT COUNT(g_cpers.cpersid) 
                FROM g_cpers 
                WHERE g_cpers.contid = g_contac.contid
            ) AS employee_count
        FROM g_contac  
    )  AS gt ON 
    (
        gt.contid = f_sync.external_id AND 
        model = case when gt.employee_count = 0 then 'PRIVATE' else 'COMPANY' end
    )