Access SQL to SQL

Question

Access SQL to SQL

wrightyrx7

Old Hand

Points: 312
More actions
January 14, 2016 at 3:56 am

#74527

Hi all,
I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database.
Could anyone please help me convert it so it is correct for Oracle SQL and not Access SQL if that makes sense?
Thanks in advance
Chris
SELECT T1.*, (SELECT TOP 1 T3.[SMN_DATEC]-1 FROM
(SELECT T2.[DET_NUMBERA], T2.[SMN_DATEC] FROM [CHRISCS_EMSAL] AS T2 ORDER BY [DET_NUMBERA],[SMN_DATEC]) AS T3 WHERE T3.[DET_NUMBERA] = T1.[DET_NUMBERA] AND T3.[SMN_DATEC] > T1.[SMN_DATEC] ) AS EndDate, T1.[DET_NUMBERA], T1.[SMN_DATEC]
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.[DET_NUMBERA], T1.[SMN_DATEC];
I have tried to test it in ORACLE SQL Developer but am getting the error:
Error at line 1, column 25:
ORA-00923: FROM keyword not found where expected
June 10, 2016 at 7:09 am

This was removed by the editor as SPAM
March 11, 2017 at 11:54 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

It appears you are using two subqueries in the expression, and Access does subqueries somewhat differently than ANSI SQL, which is closer to Oracle. Some versions of Access let you use ANSI SQL, or something close to it, so you might try changing that option in Access and see what it does. Unfortunately I am not conversant with Oracle, so hopefully someone else with that expertise can make further suggestions.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Oracle does not use TOP(n). One alternative is to filter by ROW_NUMBER().

SELECT T1.*,

(SELECT END_DATE

FROM (SELECT T3.SMN_DATEC-1 AS END_DATE,

ROW_NUMBER() OVER(ORDER BY DET_NUMBERA,SMN_DATEC) AS ROWNO

FROM (SELECT T2.DET_NUMBERA,

T2.SMN_DATEC

FROM CHRISCS_EMSAL AS T2

ORDER BY DET_NUMBERA,SMN_DATEC) AS T3

WHERE T3.DET_NUMBERA = T1.DET_NUMBERA

AND T3.SMN_DATEC > T1.SMN_DATEC ) T4

WHERE ROWNO = 1) AS EndDate,

T1.DET_NUMBERA, T1.SMN_DATEC

FROM CHRISCS_EMSAL AS T1

ORDER BY T1.DET_NUMBERA, T1.SMN_DATEC;

This code is untested because I don't have an Oracle server nor DDL and sample data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wrightyrx7 Old Hand Points: 312 More actions · Answer 3

Luis Cazares (1/14/2016)
Oracle does not use TOP(n). One alternative is to filter by ROW_NUMBER().
SELECT T1.*,
(SELECT END_DATE
FROM (SELECT T3.SMN_DATEC-1 AS END_DATE,
ROW_NUMBER() OVER(ORDER BY DET_NUMBERA,SMN_DATEC) AS ROWNO
FROM (SELECT T2.DET_NUMBERA,
T2.SMN_DATEC
FROM CHRISCS_EMSAL AS T2
ORDER BY DET_NUMBERA,SMN_DATEC) AS T3
WHERE T3.DET_NUMBERA = T1.DET_NUMBERA
AND T3.SMN_DATEC > T1.SMN_DATEC ) T4
WHERE ROWNO = 1) AS EndDate,
T1.DET_NUMBERA, T1.SMN_DATEC
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.DET_NUMBERA, T1.SMN_DATEC;
This code is untested because I don't have an Oracle server nor DDL and sample data.

Hi Luis,

Many thanks for your reply, I am getting the following error:

Error at line 7, column 38:

ORA-00907: missing right parenthesis

Thanks again.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

wrightyrx7 (1/14/2016)
Luis Cazares (1/14/2016)
Oracle does not use TOP(n). One alternative is to filter by ROW_NUMBER().
SELECT T1.*,
(SELECT END_DATE
FROM (SELECT T3.SMN_DATEC-1 AS END_DATE,
ROW_NUMBER() OVER(ORDER BY DET_NUMBERA,SMN_DATEC) AS ROWNO
FROM (SELECT T2.DET_NUMBERA,
T2.SMN_DATEC
FROM CHRISCS_EMSAL AS T2
ORDER BY DET_NUMBERA,SMN_DATEC) AS T3
WHERE T3.DET_NUMBERA = T1.DET_NUMBERA
AND T3.SMN_DATEC > T1.SMN_DATEC ) T4
WHERE ROWNO = 1) AS EndDate,
T1.DET_NUMBERA, T1.SMN_DATEC
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.DET_NUMBERA, T1.SMN_DATEC;
This code is untested because I don't have an Oracle server nor DDL and sample data.
Hi Luis,
Many thanks for your reply, I am getting the following error:
Error at line 7, column 38:
ORA-00907: missing right parenthesis
Thanks again.

Are you using exactly that code by itself? I can't find any missing parenthesis.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wrightyrx7 Old Hand Points: 312 More actions · Answer 5

Luis Cazares (1/14/2016)
wrightyrx7 (1/14/2016)
Luis Cazares (1/14/2016)
Oracle does not use TOP(n). One alternative is to filter by ROW_NUMBER().
SELECT T1.*,
(SELECT END_DATE
FROM (SELECT T3.SMN_DATEC-1 AS END_DATE,
ROW_NUMBER() OVER(ORDER BY DET_NUMBERA,SMN_DATEC) AS ROWNO
FROM (SELECT T2.DET_NUMBERA,
T2.SMN_DATEC
FROM CHRISCS_EMSAL AS T2
ORDER BY DET_NUMBERA,SMN_DATEC) AS T3
WHERE T3.DET_NUMBERA = T1.DET_NUMBERA
AND T3.SMN_DATEC > T1.SMN_DATEC ) T4
WHERE ROWNO = 1) AS EndDate,
T1.DET_NUMBERA, T1.SMN_DATEC
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.DET_NUMBERA, T1.SMN_DATEC;
This code is untested because I don't have an Oracle server nor DDL and sample data.
Hi Luis,
Many thanks for your reply, I am getting the following error:
Error at line 7, column 38:
ORA-00907: missing right parenthesis
Thanks again.
Are you using exactly that code by itself? I can't find any missing parenthesis.

Yes your code only :/

when the error appears it points to the letter "A" in the word "AS" in the following line

FROM CHRISCS_EMSAL AS T2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

I'm not sure what's the problem. The code should be working and there's no missing parenthesis. That's just another reason why I hate Oracle.

Maybe someone else can help.

By the way, the code works in SQL Server, and should work with most RDBMS as everything is under the SQL standard.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wrightyrx7 Old Hand Points: 312 More actions · Answer 7

Luis Cazares (1/14/2016)
I'm not sure what's the problem. The code should be working and there's no missing parenthesis. That's just another reason why I hate Oracle.
Maybe someone else can help.
By the way, the code works in SQL Server, and should work with most RDBMS as everything is under the SQL standard.

It must be something on my side then, I really appreciate you spending the time writing the code for me.

Thanks again

Chris

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

wrightyrx7 (1/14/2016)
Luis Cazares (1/14/2016)
I'm not sure what's the problem. The code should be working and there's no missing parenthesis. That's just another reason why I hate Oracle.
Maybe someone else can help.
By the way, the code works in SQL Server, and should work with most RDBMS as everything is under the SQL standard.
It must be something on my side then, I really appreciate you spending the time writing the code for me.
Thanks again
Chris

It's basically on the Oracle side, but I haven't used Oracle recently to be sure what it is. Try an Oracle forum for more help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ken McKelvey SSCoach Points: 18765 More actions · Answer 9

I think LEAD has been in Oracle for sometime.

Something like:

SELECT *

,LEAD(SMN_DATEC, 1) OVER (PARTITION BY DET_NUMBERA ORDER BY SMN_DATEC) - 1 AS EndDate

FROM CHRISCS_EMSAL

ORDER BY DET_NUMBERA, SMN_DATEC;