Function in a query does not return more than one row when reading from another record in a table

I have a basic function that when given one number as input can return two different values in the form of a table. I would like this function to read the values contained in a column of another table, but I am not succeeding at this.

If the function returns a string it works without problem, but I would like the function to be able to give me an output of one column and two rows based on one record value of a different table.

I provide some basic code to explain exactly what I am trying to achieve:

-- Creation mapping_table_test

create or replace TABLE mapping_table_test (
    status VARCHAR(100),
    operator VARCHAR(100),
    num1 NUMBER(38, 0),
    num2 NUMBER(38, 0)
);

-- Insert values

insert into mapping_table_test  (status, operator, num1) 
values ('Overdue', '=', 10);
insert into mapping_table_test  (status, operator, num1, num2) 
values ('On-hold', 'B', 9, 11);

-- Function creation
-- The function reads from the previous table

CREATE OR REPLACE FUNCTION map_value_basic (field_value STRING)
RETURNS TABLE(status STRING)
AS
$$
  SELECT 
    status
  FROM mapping_table_test 
  WHERE 
         (operator = '=' AND field_value = num1) OR
         (operator = 'B' AND field_value between num1 AND num2)
$$;

-- Testing the function

SELECT * FROM table(map_value_basic('10')); -- Returns: Overdue, on-hold

-- Creating another table the function reads from 
create or replace TABLE tbl_to_map_basic (
    value_num NUMBER(38, 0)
);

-- Inserting values

insert into tbl_to_map_basic (value_num) 
values (10);

SELECT * FROM tbl_to_map_basic;

-- Function bridging the two tables

SELECT * FROM table(map_value_basic('SELECT * FROM tbl_to_map_basic'));

This last SELECT is not working ('Numeric value 'SELECT * FROM tbl_to_map_basic' is not recognized', it is expecting a number).

I found a quite long solution based on the function returning a string (not a table) with comma-separated values that I can then separate in different columns and unpile again within the same column. However, I would need a more direct approach.

What I am expecting is a table such as SELECT * FROM table(map_value_basic('10'));, which returns two rows with values 'Overdue' and 'On-hold'. I only want to be able to provide the other table as input, also assuming that in a more complex scenario sometimes the number will only return one value, not two necessarily.

You're trying to use the output of one query SELECT * FROM tbl_to_map_basic as the input to a table function table(map_value_basic(...)). The way to do this in Snowflake is with a CROSS JOIN.

The simplest way to write a CROSS JOIN is with a comma-separated list of your data sources in the FROM statement, as so:

WITH my_values AS (
  SELECT value_num
  FROM tbl_to_map_basic
)

SELECT *
FROM my_values,
     table(map_value_basic(TO_VARCHAR(value_num)))
;

This returns:

| VALUE_NUM | STATUS   |
|-----------|----------|
| 10        | Overdue  |
| 10        | On-hold  |