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;
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 |