Convert Oracle SQL to work as Access Query

[sirhannick]

Hello. I am working with an Oracle database which I have linked to in access using the ODBC drivers. I have an excel sheet which is currently working to dump the data needed. Here is the critical part of the code in the excel sheet that I have:

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER={Oracle in instantclient_11_2};SERVER=server;UID=uid;PWD=pwd;DBQ=dbq;DBA=dba;" _
        ), Array( _
        "APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;" _
        ), Array("FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;")), Destination:=Range("$A$1")). _
        QueryTable
        .CommandText = Array( _
        "SELECT AL1.PRP_BEZ, AL1.PRP_VAR, AL1.PRP_VER, AL1.RUN_DATE, AL1.RUNID, AL3.MRK_NUM_EXT, AL3.MRK_BEZ, AL3.MRK_WERT, " _
        , _
        "AL3.MRK_EINHEIT, AL3.MRK_EIN_GUT, AL2.MRK_TXT, AL1.BMT_NAME,AL1.SFT_NAME, AL1.SFT_VERSION, AL1.SFT_TYP FROM EVAPROD.V_PD_LFD_RUN AL1, EVAPROD.V_PD_LFD_MXT2 AL" _
        , _
        "2, EVAPROD.V_PD_LFD_MED2 AL3 WHERE ( AL3.RUNID = AL2.RUNID (+) AND  AL3.RUNID_TYPE = AL2.RUNID_TYPE (+) AND  AL3.RU" _
        , _
        "N_DATE = AL2.RUN_DATE (+) AND  AL1.PRP_VAR = '" & Test & "' AND AL3.MRK_NUM = AL2.MRK_NUM (+) AND AL3.RUNID=AL1.RUNID AND AL3.RUNID_TYPE=AL1.RUNID_T" _
        , _
        "YPE AND    AL1.PRP_BEZ = '" & Product & "' AND  AL3.RUN_DATE=AL1.RUN_DATE)  AND (  AL1.RUN_DATE>={ts '" & Date1 & "'}  AN" _
        , "D ( AL1.RUN_DATE<={ts '" & Date2 & "'}) AND rownum < 500000)")

I would like to create a similar query in Access which would be easy to work with. It's a requirement to use Access and not Excel because I already have a rather complicated Access front end going. Any help would be greatly appreciated.

I currently have the 3 different tables imported as linked tables and can get a query to output some data, but it does not match what I am getting in excel in the least bit. I am not sure how critical the relationships of the tables in access is versus what the actual SQL in the query is. Thanks again.

 

[MSAccessRookie]

I am not positive, but I believe that the (+) notation indicates an optional Field in a comparison with the side that has the (+) being the optional one. The closest thing I can think of that might accomplish this would be an Left or Right Join. Try experimenting with Left and Right Joins and see if that helps you out.

 

[sirhannick]

So my first step was to get rid of the line continuations and break it into the SELECT, FROM, & WHERE statements.

[COLOR=black][FONT=&quot]SELECT AL1.PRP_BEZ, AL1.PRP_VAR, AL1.PRP_VER, AL1.RUN_DATE, AL1.RUNID, AL3.MRK_NUM_EXT, AL3.MRK_BEZ, AL3.MRK_WERT, "AL3.MRK_EINHEIT, AL3.MRK_EIN_GUT, AL2.MRK_TXT, AL1.BMT_NAME,AL1.SFT_NAME, AL1.SFT_VERSION, AL1.SFT_TYP[/FONT][/COLOR]  
  [COLOR=black][FONT=&quot]FROM EVAPROD.V_PD_LFD_RUN AL1, EVAPROD.V_PD_LFD_MXT2 AL2, EVAPROD.V_PD_LFD_MED2 AL3[/FONT][/COLOR]
  
  [COLOR=black][FONT=&quot]WHERE ( AL3.RUNID = AL2.RUNID (+) AND  AL3.RUNID_TYPE = AL2.RUNID_TYPE (+) AND  AL3.RUN_DATE = AL2.RUN_DATE (+) AND  AL1.PRP_VAR = '" & Test & "' AND AL3.MRK_NUM = AL2.MRK_NUM (+) AND AL3.RUNID=AL1.RUNID AND AL3.RUNID_TYPE=AL1.RUNID_TYPE AND    AL1.PRP_BEZ = '" & Product & "' AND  AL3.RUN_DATE=AL1.RUN_DATE)  AND (  AL1.RUN_DATE>={ts '" & Date1 & "'}  AND ( AL1.RUN_DATE<={ts '" & Date2 & "'}) AND rownum < 500000)[/FONT][/COLOR]

Then replace the AL1, AL2, & AL3 with the actual names of the tables since Access doesn't appear to use this type of referencing when it makes SQL code. Also, I replaced "EVAPROD." with "EVAPROD_".

  [COLOR=black][FONT=&quot]SELECT EVAPROD_V_PD_LFD_RUN .PRP_BEZ, EVAPROD_V_PD_LFD_RUN .PRP_VAR, EVAPROD_V_PD_LFD_RUN .PRP_VER, EVAPROD_V_PD_LFD_RUN .RUN_DATE, EVAPROD_V_PD_LFD_RUN .RUNID, EVAPROD_V_PD_LFD_MED2.MRK_NUM_EXT, EVAPROD_V_PD_LFD_MED2.MRK_BEZ, EVAPROD_V_PD_LFD_MED2.MRK_WERT, "EVAPROD_V_PD_LFD_MED2.MRK_EINHEIT, EVAPROD_V_PD_LFD_MED2.MRK_EIN_GUT, EVAPROD_V_PD_LFD_MXT2.MRK_TXT, EVAPROD_V_PD_LFD_RUN .BMT_NAME,EVAPROD_V_PD_LFD_RUN .SFT_NAME, EVAPROD_V_PD_LFD_RUN .SFT_VERSION, EVAPROD_V_PD_LFD_RUN .SFT_TYP[/FONT][/COLOR]
  
  [COLOR=black][FONT=&quot]FROM EVAPROD_V_PD_LFD_RUN, EVAPROD_V_PD_LFD_MXT2, EVAPROD_V_PD_LFD_MED2[/FONT][/COLOR]
  
  [COLOR=black][FONT=&quot]WHERE ( EVAPROD_V_PD_LFD_MED2.RUNID = EVAPROD_V_PD_LFD_MXT2.RUNID (+) AND  EVAPROD_V_PD_LFD_MED2.RUNID_TYPE = EVAPROD_V_PD_LFD_MXT2.RUNID_TYPE (+) AND  EVAPROD_V_PD_LFD_MED2.RUN_DATE = EVAPROD_V_PD_LFD_MXT2.RUN_DATE (+) AND  EVAPROD_V_PD_LFD_RUN .PRP_VAR = '" & Test & "' AND EVAPROD_V_PD_LFD_MED2.MRK_NUM = EVAPROD_V_PD_LFD_MXT2.MRK_NUM (+) AND EVAPROD_V_PD_LFD_MED2.RUNID=EVAPROD_V_PD_LFD_RUN .RUNID AND EVAPROD_V_PD_LFD_MED2.RUNID_TYPE=EVAPROD_V_PD_LFD_RUN .RUNID_TYPE AND    EVAPROD_V_PD_LFD_RUN .PRP_BEZ = '" & Product & "' AND  EVAPROD_V_PD_LFD_MED2.RUN_DATE=EVAPROD_V_PD_LFD_RUN .RUN_DATE)  AND (  EVAPROD_V_PD_LFD_RUN .RUN_DATE>={ts '" & Date1 & "'}  AND ( EVAPROD_V_PD_LFD_RUN .RUN_DATE<={ts '" & Date2 & "'}) AND rownum < 500000)[/FONT][/COLOR]

Then I attempt to view the query in design view and I get "Syntax error in string in query expression "


My problem is I just don't know what valid SQL code is supposed to look like! :banghead:



Here is a query I made via the Access "design view" that doesn't give me any code errors, but doesn't give me the desired output:

SELECT EVAPROD_V_PD_LFD_RUN.PRP_BEZ, EVAPROD_V_PD_LFD_RUN.PRP_VAR, EVAPROD_V_PD_LFD_RUN.PRP_VER, EVAPROD_V_PD_LFD_RUN.RUN_DATE, EVAPROD_V_PD_LFD_RUN.RUNID, EVAPROD_V_PD_LFD_MED2.MRK_NUM_EXT, EVAPROD_V_PD_LFD_MED2.MRK_BEZ, EVAPROD_V_PD_LFD_MED2.MRK_WERT, EVAPROD_V_PD_LFD_MED2.MRK_EINHEIT, EVAPROD_V_PD_LFD_MED2.MRK_EIN_GUT, EVAPROD_V_PD_LFD_MXT2.MRK_TXT, EVAPROD_V_PD_LFD_RUN.BMT_NAME, EVAPROD_V_PD_LFD_RUN.SFT_NAME, EVAPROD_V_PD_LFD_RUN.SFT_VERSION, EVAPROD_V_PD_LFD_RUN.SFT_TYP
FROM EVAPROD_V_PD_LFD_MXT2 INNER JOIN (EVAPROD_V_PD_LFD_RUN INNER JOIN EVAPROD_V_PD_LFD_MED2 ON EVAPROD_V_PD_LFD_RUN.RUNID = EVAPROD_V_PD_LFD_MED2.RUNID) ON EVAPROD_V_PD_LFD_MXT2.RUNID = EVAPROD_V_PD_LFD_RUN.RUNID
WHERE (((EVAPROD_V_PD_LFD_RUN.RUNID)="B6155844"));

 

[gblack]

Honestly I am not sure from your description what your trying to do with that code, specifically. It looks like you're trying to breakdown the code without considering any of the variables in the original VBA (e.g. Test, Product, Date1, Date2 etc...).

Consider using a passthrough query to access your oracle database. It will use the same ODBC connection, so you could probably Xfer that VBA code from excel over to MS Access with a little modification since a passthrough query must be in an Oracle format.

Because you are using variables like "Product" & "Test" etc... you will likely need to create a querydef (i.e. create the passthrough Query using VBA so that those variable values are written into the SQL of the Passthrough query), then push your Oracle SQL string to the Passthrough QueryDef.

The benefits of using a passthrough query is that all the work is done on the Oracle database (unlike ODBC table links) and not in MS Access. Unless your Oracle Database is notoriously slow for some reason, this will be a far faster way to access your data, than any ODBC table links you currently have.

After you set this up, I would take the passthrough query and use it as the basis for a "make-table query". That way you can pull in the exact data you need from Oracle and then push it into an MS Access table to be used and manipulated as you wish. This provides the fastest way to access any given set of data from another database, imo.

GL,
Gary

 

[sirhannick]

Thank you very much gblack! I just started with a PassThrough Query in Access (haven't done a VBA version yet). I was able to adopt the code slight to give me exactly what I needed! Passthrough was not a feature I was familiar with. Infact, it is faster than the excel code I posted earlier.
Here is the final code I used in the Pass-Through query:

SELECT AL1.PRP_BEZ, AL1.PRP_VAR, AL1.PRP_VER, AL1.RUN_DATE, AL1.RUNID, AL3.MRK_NUM_EXT, AL3.MRK_BEZ, AL3.MRK_WERT, AL3.MRK_EINHEIT, AL3.MRK_EIN_GUT, AL2.MRK_TXT, AL1.BMT_NAME,AL1.SFT_NAME, AL1.SFT_VERSION, AL1.SFT_TYP

FROM EVAPROD.V_PD_LFD_RUN AL1, EVAPROD.V_PD_LFD_MXT2 AL2, EVAPROD.V_PD_LFD_MED2 AL3

WHERE ( AL3.RUNID = AL2.RUNID (+) AND  AL3.RUNID_TYPE = AL2.RUNID_TYPE (+) AND  AL3.RUN_DATE = AL2.RUN_DATE (+) AND  AL1.PRP_VAR = 'proprietary' AND AL3.MRK_NUM = AL2.MRK_NUM (+) AND AL3.RUNID=AL1.RUNID AND AL3.RUNID_TYPE=AL1.RUNID_TYPE AND AL1.PRP_BEZ = 'proprietary' AND  AL3.RUN_DATE=AL1.RUN_DATE AND AL1.RUNID = 'proprietary');

I replaced the variables that the excel sheet used previously with constants (excel sheet was designed to be more flexible). Thanks again!