Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Translate Oracle SQL to Access SQL

Posted on 2010-09-16
3
Medium Priority
?
271 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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