Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Trying to get a CTE to work in PostgreSQL

I am new with SQL and I trying to make a recursive query to work on PostreSQL without any luck.

I have a table tb_invoice with these columns:

invoice_no cust_no payed tot_amount
"F0000C000400200" "C0004" "Y" 28786.7
"F0000C000400201" "C0004" "N" 5624.29
"F0000C000400202" "C0004" "Y" 25675.54
"F0000C000400203" "C0004" "Y" 35479.72
"F0000C000400207" "C0004" "Y" 23497.47
"F0000C000500212" "C0005" "N" 14754.03
"F0000C000500213" "C0005" "N" 3073.5

And I want to make a recursive CTE that gets the non-payed invoices bigger than 28.000eur on a single row for each cust_no. And order the result for number of invoices. It should look like something like this (in the column nombre_cliente should appear the cust_no).

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

enter image description here

But I cannot finish the code. I have tried multiple ways, but it always returns rows with repeated values or just one invoice per row.

This is some of the code I have tried. I am aware my mistake is with the conditions I give with the UNION clause, but I am unable to find a solution…

    WITH RECURSIVE lista_facturas AS (
    SELECT
        cust_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila,
        CAST (invoice_no AS TEXT) AS resultado
    FROM 
        factura_cliente
-- I have tried to add multiple WHERE clauses, but they all fail...
    UNION ALL
    SELECT
        f.cust_no,
        ROW_NUMBER() OVER (PARTITION BY f.cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY f.cust_no) AS max_numero_fila,
        CAST (l.resultado || ',' || f.invoice_no AS TEXT) AS resultado
    FROM
        factura_cliente f INNER JOIN lista_facturas l
            ON (l.cust_no = f.cust_no
               AND f.numero_fila = l.numero_fila + 1
               AND f.numero_fila <= l.max_numero_fila)

    -- Here I also tried 'l.invoice_no <> f.invoice_no' and other combinations
), factura_cliente AS(
    SELECT
        cust_no,
        invoice_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila
    FROM erp.tb_invoice i
    WHERE payed = 'N'
        AND tot_amount > 28000
)
SELECT 
    cust_no,
    resultado
FROM
    lista_facturas
;

>Solution :

Use STRING_AGG function to create coma separated invoice list.

SELECT 
  cust_no, 
  STRING_AGG(invoice_no,',' ORDER BY invoice_no) invoice_list  
FROM tb_invoice
WHERE payed = 'N'
      AND tot_amount > 28000
GROUP BY cust_no;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading