CODEWITHSUNDEEP
oracle-databaseplsql
Laoujin
Laoujin

Reputation: 10239

PL/SQL - How to concatenate values from different queries into one resultset?

Image I have the following queries in a package:

SELECT col1 FROM table WHERE id=5;
SELECT otherCol FROM otherTable WHERE id=78;

I want to return one record with two columns containing the values 'col1' and 'otherCol'.

In my case, I'd have lots of subqueries and DECODE statements so for readability I want to split it up into different queries something ala:

var result = {}; -- Unfortunately PL/SQL doesn't cope very well with JavaScript.
SELECT col1 INTO someVar FROM table WHERE id=5;
IF someVar = 1 THEN
  result.X = SomeQuery();
ELSE
  result.X = SomeEntirelyDifferentQuery();
END IF;

SELECT col INTO result.Z FROM tab1;
SELECT coz INTO result.Q FROM tab2;

IF result.Z IS NULL THEN
    result.B = Query1();
ELSE
    result.B = Query2();
END IF;

... and so on.

RETURN result;

So basically I want to create an "empty record" and then based on some conditions assign different values to columns in that record

This could be solved with DECODE but the resulting query is both not very readable nor very performant.

Upvotes: 0

Views: 807

Answers (2)

winkbrace
winkbrace

Reputation: 2711

You have to define an object type the function can return. 

CREATE OR REPLACE TYPE fancy_function_result_row as object
(
    X number,
    Y number,
    Q date,
    Z varchar2(30)
);

create or replace function fancy_function(...)
return fancy_function_result_row
as
  my_record fancy_function_result_row := fancy_function_result_row();
begin
  my_record.X := 1;
  etc...
end;

If you want to insert the record into a table, it might be a lot easier simply defining a rowtype of that table.

declare
  row_my_table my_table%rowtype;
begin
  row_my_table.X := etc..

  insert into my_table values row_my_table;
end;

Upvotes: 1

Pedro H.
Pedro H.

Reputation: 13

--To concat the values 'col1' and 'otherCol':

with r1 as (select col1 from table where id=5),
r2 as (select otherCol from otherTable WHERE id=78),
select r1.col1 || ' concat with ' || r2.othercol from r1, r2

--To do this condition using DECODE:

SELECT DECODE (col1,
                1, 
               (SELECT 1 FROM query1),
                (SELECT 1 FROM DifferentQuery)
               )
  FROM TABLE

Upvotes: 0

Related Questions