I am trying to create a table that queries individual database records, but splits out values from an associated array into individual rows for the individual database record. So far I have created the numbers table that has numerical rows up to 255, but when I try to join that table to a table where I am splitting the array by string in the number table order, I receive an error function split_part(character varying, "unknown", double precision) does not exist
. Any idea what is off with my setup?
Note: I am using Amazon Redshift
Numbers Table:
Sample query results
number
1
2
3
4
...
255
Query
SELECT
p0.n
+ p1.n*2
+ p2.n * POWER(2,2)
+ p3.n * POWER(2,3)
+ p4.n * POWER(2,4)
+ p5.n * POWER(2,5)
+ p6.n * POWER(2,6)
+ p7.n * POWER(2,7)
as number
FROM
(SELECT 0 as n UNION SELECT 1) p0,
(SELECT 0 as n UNION SELECT 1) p1,
(SELECT 0 as n UNION SELECT 1) p2,
(SELECT 0 as n UNION SELECT 1) p3,
(SELECT 0 as n UNION SELECT 1) p4,
(SELECT 0 as n UNION SELECT 1) p5,
(SELECT 0 as n UNION SELECT 1) p6,
(SELECT 0 as n UNION SELECT 1) p7
Parser table query:
Ideal query results
row_id, record_id, medium_name
1, 24, work
2, 24, test
3, 24, success
Query
SELECT row_number() over (order by 1) as row_id
, test_lead.id as lead_id
, split_part(test_lead.test_mediums, ', ', numbers.number) as medium_name
FROM ${lead.SQL_TABLE_NAME} test_lead
JOIN ${marketing_model_numbers.SQL_TABLE_NAME} numbers
ON numbers.number <= regexp_count(test_lead.test_mediums, ',\\s') + 1
Array value structure:
["work", "test", "success"]
Best Answer
Your
numbers.number
is adouble precision
. Butsplit_part()
expects the third parameter to be aninteger
and the value isn't implicitly cast. Try to explicitly castnumbers.number
.