Why doesn't my SQL query run in PgAdmin 4 but runs in SQL editor online-CodePudding

Why doesn't these two codes run on PgAdmin 4? Where I already have the database for HR. When I add hr. on jobs and employees the next error is: ERROR: CASE types numeric and text cannot be matched

LINE 8: ' ' || (e.salary - ((j.min_salary j.max_salary)/2))

https://www.sqltutorial.org/sql-sample-database/

SELECT e.first_name,
         e.last_name,
         j.job_title,
         cast(e.salary AS varchar) salary,
         cast(((j.min_salary   j.max_salary)/2) AS varchar) average_salary,
         cast( (case
    WHEN e.salary > ((j.min_salary   j.max_salary)/2) THEN
    ' ' || (e.salary - ((j.min_salary   j.max_salary)/2))
    WHEN e.salary = ((j.min_salary   j.max_salary)/2) THEN
    '0'
    ELSE (e.salary-((j.min_salary   j.max_salary)/2)) end) AS varchar) class_difference
FROM employees e
LEFT JOIN jobs j
    ON e.job_id = j.job_id
ORDER BY  job_title, salary

===========================================================================================

with averages AS 
    (SELECT job_id,
         job_title,
         ((min_salary   max_salary)/2) average
    FROM jobs
    GROUP BY  job_id)
SELECT employee_id,
         first_name,
         last_name,
         a.job_title,
         cast(salary AS varchar) salary,
         cast(a.average AS varchar) avg_salary,
         cast((case
    WHEN (salary - a.average > 0) THEN
    ' ' || (salary - a.average)
    WHEN (salary - a.average = 0) THEN
    '0'
    ELSE (salary - a.average) end) AS varchar) salary_class_diff
FROM employees e
LEFT JOIN averages a
    ON e.job_id = a.job_id
ORDER BY  a.job_title, salary

CodePudding user response：

Could you please try this:

with averages AS 
        (SELECT job_id,
             job_title,
             ((min_salary   max_salary)/2) average
        FROM jobs
        GROUP BY  job_id)
    SELECT employee_id,
             first_name,
             last_name,
             a.job_title,
             cast(salary AS varchar) salary,
             cast(a.average AS varchar) avg_salary,
             cast((case
        WHEN (salary - a.average > 0) THEN
        ' '  || cast((salary - a.average) AS varchar)
        WHEN (salary - a.average = 0) THEN
        '0'
        ELSE cast((salary - a.average) as varchar) end) AS varchar) salary_class_diff
    FROM employees e
    LEFT JOIN averages a
        ON e.job_id = a.job_id
    ORDER BY  a.job_title, salary;

And this:

SELECT e.first_name,
         e.last_name,
         j.job_title,
         cast(e.salary AS varchar) salary,
         cast(((j.min_salary   j.max_salary)/2) AS varchar) average_salary,
         cast( (case
    WHEN e.salary > ((j.min_salary   j.max_salary)/2) THEN
    ' ' || cast((e.salary - ((j.min_salary   j.max_salary)/2)) AS varchar)
    WHEN e.salary = ((j.min_salary   j.max_salary)/2) THEN
    '0'
    ELSE cast((e.salary-((j.min_salary   j.max_salary)/2)) as varchar) end) AS varchar) class_difference
FROM employees e
LEFT JOIN jobs j
    ON e.job_id = j.job_id
ORDER BY  job_title, salary;

The problem is you are trying to concatane string value (' ') and numeric value(salary - a.average). I do not how sqltutorial handle this situtation, but appearently PgAdmin4 cannot handle.