Solved

Translate Oracle SQL to Access SQL

Posted on 2010-09-16
3
269 Views
Last Modified: 2013-11-05
Can someone help me translate the following Oracle SQL statement into one that Access can understand. I am having trouble getting Access to run it. Thanks for any help you can give me.


SELECT LB.CUST_CODE, LB.CUST_DEST, LB.ASSY_UNT_ID, LB.SEQ_NUM LAST_BUILT_SEQ, LB.RACK_NBR LAST_BUILT_RACK, 
	   LS.SEQ_A LAST_BROADCAST_SEQ, LS.RACK_NBR LAST_BROADCAST_RACK, ((LS.SEQ_A - LB.SEQ_NUM) / 10) DIFF
FROM
(
SELECT SLT.CUST_CODE, SLT.CUST_DEST, SLT.ASSY_UNT_ID, GREATEST(SEQ_A,SEQ_B) SEQ_NUM, RELTSLT.RACK_NBR
FROM 	(
		   SELECT RELTSLT.CUST_CODE, RELTSLT.CUST_DEST, RELTSLT.ASSY_UNT_ID, 
		   MAX(RELTSLT.LIN_JOB_SEQ_A) SEQ_A, MAX(RELTSLT.LIN_JOB_SEQ_B) SEQ_B
		   FROM RELTSLT
		   WHERE (RELTSLT.SERL_NBR_TYPE='RCK') AND (RELTSLT.SERL_TS > (sysdate - 30)) 
		   GROUP BY RELTSLT.CUST_CODE, RELTSLT.CUST_DEST, RELTSLT.ASSY_UNT_ID, RELTSLT.SERL_NBR_TYPE
		) SLT, RELTSLT
WHERE (SLT.ASSY_UNT_ID = RELTSLT.ASSY_UNT_ID AND SLT.SEQ_A = RELTSLT.LIN_JOB_SEQ_A AND RELTSLT.SERL_NBR_TYPE = 'RCK')
ORDER BY SLT.CUST_CODE, SLT.CUST_DEST, SLT.ASSY_UNT_ID
) LB,
(
SELECT RST.CUST_CODE, RST.CUST_DEST, RST.ASSY_UNT_ID, SEQ_A, RELTRST.RACK_NBR
FROM 	(
		   SELECT RELTRST.CUST_CODE, RELTRST.CUST_DEST, RELTRST.ASSY_UNT_ID, 
		   MAX(RELTRST.LIN_JOB_SEQ) SEQ_A
		   FROM RELTRST
		   WHERE (RELTRST.PROD_LINE=0) AND (RELTRST.CHNG_TS > (sysdate - 10)) 
		   GROUP BY RELTRST.CUST_CODE, RELTRST.CUST_DEST, RELTRST.ASSY_UNT_ID
		) RST, RELTRST
WHERE (RST.ASSY_UNT_ID = RELTRST.ASSY_UNT_ID AND RST.SEQ_A = RELTRST.LIN_JOB_SEQ AND RELTRST.PROD_LINE=0)
ORDER BY RST.CUST_CODE, RST.CUST_DEST, RST.ASSY_UNT_ID
) LS
WHERE TRIM(LB.CUST_CODE) = TRIM(LS.CUST_CODE) AND TRIM(LB.CUST_DEST) = TRIM(LS.CUST_DEST) AND 
	  LB.ASSY_UNT_ID = LS.ASSY_UNT_ID AND LB.ASSY_UNT_ID LIKE 'KA_KK%'

Open in new window

0
Comment
Question by:G Scott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33695401
ExpertOnNothing,

Here are the things I spotted that would be different for Access:

+String literals use " instead of ' ; so just change your strings to "some text" instead of 'some text'.
+Use * as wildcard in LIKE statements instead of %
+GREATEST() function I don't believe exists, so you would use IIF(SEQ_A > SEQ_B, SEQ_A, SEQ_B)
+sysdate function would be DATE() or NOW() depending on if you want timestamps

Hope that helps!
0
 
LVL 1

Author Comment

by:G Scott
ID: 33695428
Sorry, I am not asking for a complete rewrite. I just need some guidance on what needs to be changed for Access to understand it. It is very confusing to start with.
0
 
LVL 1

Author Comment

by:G Scott
ID: 33695446
Oh...mwvisa1, I replied before I saw your reply. So my reply was not directed at you. I will give it a go with your recommendations and let you know how I make out. Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question