CONCAT returns null

From: "Sterpu Victor" <victor(at)caido(dot)ro>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: CONCAT returns null
Date: 2016-02-28 10:02:47
Message-ID: emf4faf124-bac4-4809-8371-ef9b41853ad0@victor-pc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

I have this concat:
CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), f2.nrfo,
TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))
that works fine but when I change to this(I added a ' with '):
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), '
with ', f2.nrfo, TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'),
DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
then concat returns NULL.
Why? I tried to add' with '::VARCHARand ' with '::TEXT but the result is
still NULL.

Thank you

This is part of a bigger query that I wrote down.
SELECT d1.id AS id, d1.name AS name, COUNT(DISTINCT(f2.id)) AS
nr_spitalizari,
ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, 'YYYY-MM-DD'), ' cu
', f2.nrfo, '/', TO_CHAR(fd7.validfrom, 'YYYY-MM-DD'), ' diferenta de ',
DATE(fd5.validto)-DATE(fd1.validfrom), ' zile ')) AS fise

FROM focg f
JOIN person p ON p.id = f.idpatient
JOIN focgdepartment fd1 ON fd1.idfocg = f.id
JOIN department d1 ON d1.id = fd1.iddepartment
LEFT JOIN focgdepartment fd2 ON fd2.idfocg = f.id AND fd2.validfrom <
fd1.validfrom
JOIN focgdepartment fd3 ON fd3.idfocg = f.id
JOIN department d2 ON d2.id = fd3.iddepartment
LEFT JOIN focgdepartment fd4 ON fd4.idfocg = f.id AND fd4.validfrom >
fd3.validfrom

JOIN focg f2 ON f2.idpatient = f.idpatient AND f2.id != f.id
JOIN focgdepartment fd5 ON fd5.idfocg = f2.id
LEFT JOIN focgdepartment fd6 ON fd6.idfocg = f2.id AND fd6.validfrom >
fd5.validfrom
JOIN focgdepartment fd7 ON fd7.idfocg = f2.id
LEFT JOIN focgdepartment fd8 ON fd8.idfocg = f2.id AND fd8.validfrom <
fd8.validfrom

WHERE fd2.id IS NULL AND fd4.id IS NULL AND fd6.id IS NULL AND fd8.id IS
NULL AND fd5.validto IS NOT NULL AND fd1.validfrom >= '2015-02-01' AND
fd1.validfrom <= '2016-02-29' AND
DATE(fd5.validto)-DATE(fd1.validfrom)<=30 AND
DATE(fd5.validto)-DATE(fd1.validfrom)>=0
GROUP BY d1.name, d1.id;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Charles Clavadetscher 2016-02-28 10:24:13 Re: CONCAT returns null
Previous Message David G. Johnston 2016-02-28 06:15:29 Re: Designing tables based on user input and defined values