Solved

Translate Oracle SQL to Access SQL

Posted on 2010-09-16
3
268 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 59

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 36
IIF in access query 19 42
Sql case statement to calculate totals 5 37
Trying to understand why my Index is so large 12 20
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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