Solved

Translate Oracle SQL to Access SQL

Posted on 2010-09-16
3
266 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

786 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