I have below query in Oracle:
SELECT to_number(a.v_VALUE), b.v_VALUE FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b ON a.v_idx = b.v_idx
which give me result like:
I want to convert the query to Postgres. I have tried a query like:
I have also tried:
SELECT a,b FROM (select UNNEST(String_To_Array('12;5;25;10;2',';'))) a LEFT JOIN (select UNNEST(String_To_Array('12;5;25;10',';'))) b ON a = b
But didn't get a correct result.
I don't know how to write query that's fully equivalent to the Oracle version. Anyone?
In the expression
select a the
a is not a column, but the name of the table alias. Consequently that expressions selects a complete row-tuple (albeit with just a single column), not a single column.
You need to define proper column aliases for the derived tables. It is also recommended to use set returning functions only in the from clause, not in the select list.
If you are not on 9.4 you need to generate the "index" using a window function. If you are on 9.4 then Erwin's answer is much better.
SELECT a.v_value, b.v_value FROM ( select row_number() over () as idx, -- generate an index for each element i as v_value from UNNEST(String_To_Array('12;5;25;10;2',';')) i ) as a JOIN ( select row_number() over() as idx, i as v_value from UNNEST(String_To_Array('10;20;;',';')) i ) as b ON a.idx = b.idx;
An alternative way in 9.4 would be to use the
with ordinality option to generate the row index in case you do need the index value:
select a.v_value, b.v_value from regexp_split_to_table('12;5;25;10;2',';') with ordinality as a(v_value, idx) left join regexp_split_to_table('10;20;;',';') with ordinality as b(v_value, idx) on a.idx = b.idx