Re: CONCAT returns null - Mailing list 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 '::VARCHAR and ' 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;

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.