Home » SQL & PL/SQL » SQL & PL/SQL » Inline Views
Inline Views [message #192288] Mon, 11 September 2006 12:37 Go to next message
rajaguru_t
Messages: 7
Registered: September 2006
Location: India
Junior Member

Hi,
I'm jus using Inline views. How do I access the columns selected in outer query.

SELECT B.IDX, (SELECT M.ILL FROM ( SELECT ILL FROM sg_pf WHERE MX = SUBSTR(B.IDX,1,4))) FROM BAS B WHERE B.IDX IS NOT NULL;

ORA-00904: invalid column name

I'm not able to access B.IDX from inline view. Would some one please tell how to do that ?

SELECT B.IDX , (select ill from sg_fp where WHERE MX = SUBSTR(B.IDX,1,4) OR MX = (B.IDX,1,3) rownum =1 AND order by ill desc) from S_BASIC B where B.part is not null;

How to use Order by clause inside select in select.

My aim is to stop when MX = SUBSTR(B.IDX,1,4) is met.

Please advise ASAP.

Thanks,
Dun.


Re: Inline Views [message #192296 is a reply to message #192288] Mon, 11 September 2006 12:58 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Correlation values are only visible one level down. You will probably need a PL/SQL function here since you need the first ordered value.
Re: Inline Views [message #192297 is a reply to message #192288] Mon, 11 September 2006 13:04 Go to previous messageGo to next message
rajaguru_t
Messages: 7
Registered: September 2006
Location: India
Junior Member

Thanks Todd,
Can you please go on to explain little further. Im using Pro C. Can u please give me simplest solution please for solving two issues I posted.
Thanks.
Dun.
Re: Inline Views [message #192304 is a reply to message #192288] Mon, 11 September 2006 14:06 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
It doesn't look like M.ILL is defined. You pull the ILL column but the interior select statment isn't aliased with an M

SELECT 
  B.IDX, 
 (SELECT M.ILL 
  FROM ( SELECT ILL 
         FROM sg_pf 
         WHERE MX = SUBSTR(B.IDX,1,4)
       )
  ) 
FROM BAS B 
WHERE B.IDX IS NOT NULL;


You might try or simply removing the alias altogether:

SELECT 
  B.IDX, 
 (SELECT M.ILL 
  FROM ( SELECT ILL 
         FROM sg_pf 
         WHERE MX = SUBSTR(B.IDX,1,4)
       ) M
  ) 
FROM BAS B 
WHERE B.IDX IS NOT NULL;
Re: Inline Views [message #192306 is a reply to message #192304] Mon, 11 September 2006 14:15 Go to previous messageGo to next message
rajaguru_t
Messages: 7
Registered: September 2006
Location: India
Junior Member

Itz not working. Even If i alias or dont. it remains the same.
Its not able to access B.IDX
Re: Inline Views [message #192314 is a reply to message #192288] Mon, 11 September 2006 16:22 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Write it as

select b.idx,m.ILL
FROM BAS B, SG_PF M
WHERE M.MX = SUBSTR(B.IDX,1,4))
AND B.IDX IS NOT NULL;
Re: Inline Views [message #192325 is a reply to message #192314] Mon, 11 September 2006 19:08 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The issue here is caused by the need for a Top-N - it's more complicated than just a simple join.
Re: Inline Views [message #192326 is a reply to message #192325] Mon, 11 September 2006 19:14 Go to previous messageGo to next message
rajaguru_t
Messages: 7
Registered: September 2006
Location: India
Junior Member

I may not have tuples in SG_PF. So the join may fail. Because of that I went for seperate query.

Re: Inline Views [message #192449 is a reply to message #192288] Tue, 12 September 2006 07:47 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
For a nice discussion on top-n queries, please see the following link at asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:127412348064
Re: Inline Views [message #201534 is a reply to message #192288] Sun, 05 November 2006 13:06 Go to previous message
anonymous.nospam
Messages: 2
Registered: November 2006
Junior Member
In an inline view, you cannot access the columns of outer query. The database engine executes the inline view exactly the same way as it executes an ordinary view. See this thread for an example.
Previous Topic: function - two nested cursor for loop ???
Next Topic: how do i use the dictionary to know there is on delete cascade??
Goto Forum:
  


Current Time: Sat Dec 03 22:36:47 CST 2016

Total time taken to generate the page: 0.10984 seconds