Solved

Translate Oracle SQL to Access SQL

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now