How can i select from a static list of tuples in Oracle SQL?-CodePudding

I want to select from a static list of tuples in oracle sql but its not working, i have a syntax error.

I tried :

SELECT val1, val2 FROM (('test','B'),('test2','C'),('test3','D'));

CodePudding user response：

Use UNION ALL:

SELECT 'test' AS val1, 'B' AS val2 FROM DUAL UNION ALL
SELECT 'test2', 'C' FROM DUAL UNION ALL
SELECT 'test3', 'D' FROM DUAL;

Or pass the data as a JSON string and extract it using:

SELECT val1, val2
FROM   JSON_TABLE(
         '[["test","B"],["test2","C"],["test3","D"]]',
         '$[*]'
         COLUMNS (
           val1 VARCHAR2(10) PATH '$[0]',
           val2 VARCHAR2(10) PATH '$[1]'
         )
       );

Which both output:

VAL1	VAL2
test	B
test2	C
test3	D

fiddle

CodePudding user response：

It depends what do you have as 'list of tuples' and/or in what format you could pass the data to SQL (like JSON as mentioned by MTO or XML or ...). Let's say that you have just a string (or strings) and you want to extract those values...

Sample 1. - tuples in a column

WITH
    tbl AS
        (
            Select 'test1' "COL1", 'B' "COL2", '(''test1'', ''B'')' "TUPLES" From Dual Union All 
            Select 'test2','C', '(''test2'', ''C'')' From Dual Union All 
            Select 'test3','D', '(''test3'', ''D'')' From Dual
        )
Select * From tbl
Sample data - COL1 and COL2 are just for showing as separated columns
COL1  COL2 TUPLES       
----- ---- --------------
test1 B    ('test1', 'B') 
test2 C    ('test2', 'C') 
test3 D    ('test3', 'D')
__________________________________________________

Select
    TUPLES, SubStr(TUPLES, 2, InStr(TUPLES, ',') - 2) "VAL1",
    SubStr(TUPLES, InStr(TUPLES, ',')   2, Length(TUPLES) - InStr(TUPLES, ',') -2) "VAL2"
From tbl

TUPLES         VAL1          VAL2         
-------------- ------------- --------------
('test1', 'B') 'test1'       'B'            
('test2', 'C') 'test2'       'C'            
('test3', 'D') 'test3'       'D'

Sample 2. - list of tuples in one row/column

WITH
    tbl AS
        (Select '(''test1'',''B''),(''test2'',''C''),(''test3'',''D'')' "TUPLES_LIST" From Dual)
TUPLES_LIST                                 
-----------------------------------------
('test1','B'),('test2','C'),('test3','D')

___________________________________________________

Select
    TUPLES_LIST,
    SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)) "TUPLES",
    SubStr(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)), 2, InStr(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)), ',') - 2) "VAL1",
    SubStr(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)), InStr(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)), ',')   1, Length(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1))) - InStr(SUBSTR(TUPLES_LIST, InStr(TUPLES_LIST, '(', 1, LEVEL), InStr(TUPLES_LIST, ')', 1, 1)), ',') -1) "VAL2"
From tbl
WHERE ROWNUM = LEVEL
Connect By LEVEL <= 3

TUPLES_LIST                               TUPLES            VAL1            VAL2     
----------------------------------------- ----------------- --------------- ---------
('test1','B'),('test2','C'),('test3','D') ('test1','B')     'test1'         'B'      
('test1','B'),('test2','C'),('test3','D') ('test2','C')     'test2'         'C'      
('test1','B'),('test2','C'),('test3','D') ('test3','D')     'test3'         'D'