Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number, 
       oks_l.line_id,
       oks_l.tank_numbers, column_value AS TANK_NUMBER 
  FROM oks_contract_header oks_c
 INNER JOIN oks_contract_lines oks_l 
    ON oks_l.contract_number = oks_c.contract_number AND 
       oks_l.item_name LIKE '%.55201'
    , table(str2tbl(oks_l.tank_numbers,';')) acbb
ORDER BY oks_c.contract_number, 
         oks_l.line_id, 
         TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return 
str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
        pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
    return;
end;

But I'm trying to understand why it works. Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different - or is this really an OUTER APPLY?

share|improve this question
    
My guess would be that your tank_numbers only contains a single "element". But why do you mix implicit joins and explicit joins? That's very bad coding style. –  a_horse_with_no_name Aug 20 '13 at 19:13
    
@a_horse_with_no_name No, the TANK_NUMBERS column is delimited with ; and have multiple items in the string and the results are correct. Someone gave me this code because using a CONNECT BY with a regexp to do the pivoting was not working in Oracle 10g (query was not completing, and would eventually complete with some trivial filtering, but it was unworkable). I don't believe you can use a regular join to a table that is different for each row? Or can you? I have a huge blind spot in Oracle table-valued functions, and how does COLUMN_VALUE work here? –  Cade Roux Aug 20 '13 at 20:49
add comment

1 Answer

It's not cross-joined because the pipelined function is row-dependent; you can't cross-join row 1's values from the pipelined function to row 2's values because row 1's pipelined function values don't exist anymore.

If the pipelined function weren't row-dependent - if it went off of a static value - I suspect it would cross-join. (Try it and see!)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.