How can I use columns value from nested select in main select?

Question

I'm trying to execute this SQL in Oracle:

select z.*
  from (select (CASE
                 WHEN trunc(to_date('01.02.2015', 'DD.MM.YY'), 'MM') =
                      to_date('01.02.2015', 'DD.MM.YY') THEN
                  trunc(ADD_MONTHS(sysdate, -1), 'MM')
                 ELSE
                  trunc(sysdate - 1)
               END) as sd,
               trunc(sysdate) ed
          from dual) t,
       (SELECT *
          FROM table(SOMEOWNERUSER.SOMEPACKAGE.getPipelinedTable(to_char(t.sd,
                                                                       'dd.mm.yyyy'),
                                                               to_char(t.ed,
                                                                       'dd.mm.yyyy')))) z

But I get error ORA-00904: "T"."SD": invalid identifier. What am I doing wrong?

Try getting rid of the whole query from dual. Just use the case expression inside the getPipelinedTable call. You can't reference columns across the queries that way. SQL Server has cross apply for that but I don't know if Oracle has an equivalent. I don't think you need it though. If that doesn't work, you'll probably just need to initialize some variables with the arguments you want to pass. — shawnt00
– shawnt00, Commented Feb 9, 2015 at 16:02
Your reference to columns t.sd and t.ed in the second subquery is causing the error. You need to correlate your second sub-querry with the result of t. — SoulTrain
– SoulTrain, Commented Feb 9, 2015 at 16:07

Boneist · Accepted Answer · 2015-02-09 16:12:26Z

1

You just need to directly reference the table() in the same level join as the t subquery:

create type dt_type as object (dt date);
/

create type dt_tab as table of dt_type;
/

create or replace function getpipelinedtable (p_sd date, p_ed date)
return dt_tab
as
  l_tab dt_tab := dt_tab();
begin
  for i in 1 .. (p_ed - p_sd) + 1
  loop
    l_tab.extend;
    l_tab(l_tab.last) := dt_type(p_sd -1 + i);
  end loop;

  return l_tab;
end getpipelinedtable;
/

SELECT z.*
FROM   (SELECT (CASE WHEN TRUNC (TO_DATE ('01.02.2015', 'DD.MM.YY'), 'MM') = TO_DATE ('01.02.2015', 'DD.MM.YY') 
                          THEN TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') 
                     ELSE TRUNC (SYSDATE - 1) 
                END) AS sd,
                TRUNC (SYSDATE) ed
        FROM   DUAL) t,
        TABLE (  getpipelinedtable (t.sd, t.ed) ) z;

DT        
----------
01/01/2015
02/01/2015
03/01/2015
04/01/2015
05/01/2015
<snip>
06/02/2015
07/02/2015
08/02/2015
09/02/2015

drop function getpipelinedtable;
drop type dt_tab;
drop type dt_type;

answered Feb 9, 2015 at 16:12

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

X-factor Over a year ago

Boneist, thank you very mutch! This is it. Next my question was be "how do same thing if instead of TABLE(someFunc()) was select * from some_table?". But, I understand that need to use INNER JOIN: ... t, inner join some_join_table jt on jt.created >= t.sd and jt.created < t.ed

Boneist Over a year ago

Yes, you could do an inner join (although you'd remove the comma just after the t and before the inner join). It entirely depends on what you're trying to achieve.

X-factor Over a year ago

Yeap, I forgot to remove comma after t. Thanks.

Collectives™ on Stack Overflow

How can I use columns value from nested select in main select?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related