T-SQL Conversion to PL/SQL

[oshlonger]

I'm trying to convert the following MS SQL to Oracle PS/SQL but not getting anywhere. I'm having problems converting the top N part of the query to use Oracle's ROWNUM function. That part of the query is already a sub-query and nesting 2 sub-queries is giving me a problem. Any suggestions?

Here are my tables:

TABLE & TABLE2 (the same table)

Id Record_Id Date
12 556 3/26/06
13 556 3/28/06
14 556 4/05/06

TABLE3
Id Record_Num
556 789878

Here are the results that I want:

NUM START COLUMN
556 3/26/06 3/28/06

select	NUM,
	START,
	COLUMN	
from (

	select 	B.RECORD_NUM NUM, 
		A.DATE START,

		(select TOP 1
			T.THE_VAL 
		from TABLE T
		where T.RECORD_ID = B.ID
		and T.DATE >= A.DATE
		order by T.DATE ) 'COLUMN',

	from  TABLE2 A
	join   TABLE3 B on A.RECORD_ID = B.ID 

) tb1

 

[hoinz]

It would be useful if you included the error message you get.
'... is giving me a problem ' is a bit poor.
Help us to help you!

But what I saw at first glance:
You are using 'COLUMN' as a column name.
But 'COLUMN' is an Oracle reserved word, and I think this won't work.
Try another name.

 

[oshlonger]

Sorry for the lack of information, here's a bit more...

I'm not really using 'COLUMN' as a column name. When I posted the query to the forum, I changed some of the names to make it a little easier.

I'm converting the Top N subquery to use the ROWNUM function in Oracle. This is what I have:

SELECT *
FROM  (SELECT T.THE_VAL
	   FROM TABLE T
	   WHERE T.RECORD_ID = B.ID
	   AND T.DATE >= A.DATE
	   ORDER BY T.DATE )
WHERE  ROWNUM < 2)  COLUMN

The problem is (I think) that since there is now another subquery nested in the subquery, I'm not able to join back to my TABLE3 (B) anymore.

The error returned is that B.ID is invalid.