In PostgreSQL, I have a column receipt_id which will have comma separated values or a single value .
Eg:
I need to use the values in the third column with another table called Voucher in where condition .
I have used split_part.
select ap.document_no AS invoice_number,
ap.curr_date AS invoice_date,ap.receipt_id,split_part(ap.receipt_id::text, ','::text, 1),
split_part(ap.receipt_id::text, ','::text, 2)
from ap_invoice_creation ap , voucher v
where (v.voucher_id::text IN
( SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 1) AS parts
FROM ap_invoice_creation
WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text
UNION
SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 2) AS parts
FROM ap_invoice_creation
WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text
UNION
SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 3) AS parts
FROM ap_invoice_creation
WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text)) AND ap.status::text = 'Posted'::text
But this is a part of query, it is taking more time.
Because of this entire query is taking more time.
Is there any other way to handle this?
>Solution :
Ideally, you should not even be storing CSV like this. That being said, there is no need for SPLIT_PART()
here and big ugly union. Consider this version:
SELECT
ap.document_no AS invoice_number,
ap.curr_date AS invoice_date,
ap.receipt_id,
SPLIT_PART(ap.receipt_id::text, ',', 1),
SPLIT_PART(ap.receipt_id::text, ',', 2)
FROM ap_invoice_creation ap
INNER JOIN voucher v
ON ',' || ap.receipt_id || ',' LIKE '%,' || v.voucher_id::text || ',%';