Oracle SQL vs. Access SQL

G

Guest

I want to create a SQL pass through query in Access connecting to a back end
Oracle database. The back in database is extremely large, and I'm hoping
performance will improve using a pass through query as oppossed to a select
query over ODBC. Problem is, I can't properly translate complex Access SQL
into Oracle SQL.

Here's my Access SQL:

SELECT
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "" AS WC_NAME, GL_PROD_ID, "" AS
PROD_DESC, GL_PRODUCT_UOM, "" AS PROD_GAL_LBS_CONV, Sum(GL_TRANSACTION_QTY)
AS QTY, GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID, CUST_NAME, CUST_TYPE,
Sum(IIf([GL_DR_CR_IND]="C",-[GL_POSTING_AMOUNT],[GL_POSTING_AMOUNT])) AS
AMOUNT
FROM
AGRPRD.GL_POSTING_LOG LEFT JOIN SRPTPRD.A_CUSTS ON GL_SHIP_TO_CUST_ID =
CUST_ID
WHERE
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate]))
GROUP BY
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "", GL_PROD_ID, "",
GL_PRODUCT_UOM, "", GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID, CUST_NAME, CUST_TYPE
HAVING
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)<"100")) OR (((GL_PROCESS_TYPE)="I") AND
((GL_ACCOUNT_NBR)="480008") AND ((CUST_TYPE)="270")) OR
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)="280")) OR (((GL_ACCOUNT_NBR)="480024")) OR
(((GL_ACCOUNT_NBR)="580092"));

Can someone help in translating this into Oracle SQL?
 
R

Rick Brandt

Kirk said:
I want to create a SQL pass through query in Access connecting to a
back end Oracle database. The back in database is extremely large,
and I'm hoping performance will improve using a pass through query as
oppossed to a select query over ODBC. Problem is, I can't properly
translate complex Access SQL into Oracle SQL.

Here's my Access SQL:

SELECT
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "" AS WC_NAME,
GL_PROD_ID, "" AS PROD_DESC, GL_PRODUCT_UOM, "" AS PROD_GAL_LBS_CONV,
Sum(GL_TRANSACTION_QTY) AS QTY, GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID,
CUST_NAME, CUST_TYPE,
Sum(IIf([GL_DR_CR_IND]="C",-[GL_POSTING_AMOUNT],[GL_POSTING_AMOUNT]))
AS AMOUNT
FROM
AGRPRD.GL_POSTING_LOG LEFT JOIN SRPTPRD.A_CUSTS ON GL_SHIP_TO_CUST_ID
= CUST_ID
WHERE
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate]))
GROUP BY
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "", GL_PROD_ID, "",
GL_PRODUCT_UOM, "", GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID, CUST_NAME,
CUST_TYPE HAVING
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)<"100")) OR (((GL_PROCESS_TYPE)="I") AND
((GL_ACCOUNT_NBR)="480008") AND ((CUST_TYPE)="270")) OR
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)="280")) OR (((GL_ACCOUNT_NBR)="480024")) OR
(((GL_ACCOUNT_NBR)="580092"));

Can someone help in translating this into Oracle SQL?
Click to expand...

I am not familiar with Oracle SQL syntax but you have form references in
this query and NO passthrough query can use those. The SQL has to be a
statement that can run on the server completely separate from your Access
app (basic definition of a passthrough). The Oracle server has no knowledge
of the forms in your Access app.
 
G

Guest

OK, that makes sense, but even without the reference to the Access form, I
can't get a pass-through query to run with a left outer join, or any grouping
or specifying certain date ranges (do I enclose the dates in #, ", ') -
nothing seems to work.

About the only thing I can get to work is SELECT * FROM ORACLETABLE.
Anything over and above that I get syntax errors that I don't know how to
correct.

Rick Brandt said:
Kirk said:
I want to create a SQL pass through query in Access connecting to a
back end Oracle database. The back in database is extremely large,
and I'm hoping performance will improve using a pass through query as
oppossed to a select query over ODBC. Problem is, I can't properly
translate complex Access SQL into Oracle SQL.

Here's my Access SQL:

SELECT
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "" AS WC_NAME,
GL_PROD_ID, "" AS PROD_DESC, GL_PRODUCT_UOM, "" AS PROD_GAL_LBS_CONV,
Sum(GL_TRANSACTION_QTY) AS QTY, GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID,
CUST_NAME, CUST_TYPE,
Sum(IIf([GL_DR_CR_IND]="C",-[GL_POSTING_AMOUNT],[GL_POSTING_AMOUNT]))
AS AMOUNT
FROM
AGRPRD.GL_POSTING_LOG LEFT JOIN SRPTPRD.A_CUSTS ON GL_SHIP_TO_CUST_ID
= CUST_ID
WHERE
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate]))
GROUP BY
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, "", GL_PROD_ID, "",
GL_PRODUCT_UOM, "", GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID, CUST_NAME,
CUST_TYPE HAVING
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)<"100")) OR (((GL_PROCESS_TYPE)="I") AND
((GL_ACCOUNT_NBR)="480008") AND ((CUST_TYPE)="270")) OR
(((GL_PROCESS_TYPE)="I") AND ((GL_ACCOUNT_NBR)="480008") AND
((CUST_TYPE)="280")) OR (((GL_ACCOUNT_NBR)="480024")) OR
(((GL_ACCOUNT_NBR)="580092"));

Can someone help in translating this into Oracle SQL?
Click to expand...

I am not familiar with Oracle SQL syntax but you have form references in
this query and NO passthrough query can use those. The SQL has to be a
statement that can run on the server completely separate from your Access
app (basic definition of a passthrough). The Oracle server has no knowledge
of the forms in your Access app.
Click to expand...
 
R

Rick Brandt

Kirk said:
OK, that makes sense, but even without the reference to the Access
form, I can't get a pass-through query to run with a left outer join,
or any grouping or specifying certain date ranges (do I enclose the
dates in #, ", ') - nothing seems to work.

About the only thing I can get to work is SELECT * FROM ORACLETABLE.
Anything over and above that I get syntax errors that I don't know
how to correct.
Click to expand...

Yeah, for that you would need to consult a manual on how to write Oracle
SQL. Every database has differences in that regard and all you can do is
learn them.
 
S

Sylvain Lafontaine

For MS SQL-Server, strings and dates are delimited by single quotes ' and
not double quotes " or #. Maybe it's the same thing for Oracle.

But obviously, reading a good book on Oracle should be your first step.
 
J

John Vinson

Can someone help in translating this into Oracle SQL?
Click to expand...

Well... my Oracle SQL is pretty rusty, but here's a rough go:

SELECT
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, '' WC_NAME, GL_PROD_ID, ''
PROD_DESC, GL_PRODUCT_UOM, '' PROD_GAL_LBS_CONV,
Sum(GL_TRANSACTION_QTY) QTY, GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID,
CUST_NAME, CUST_TYPE,
Sum(DECODE([GL_DR_CR_IND],'C',-[GL_POSTING_AMOUNT],True,[GL_POSTING_AMOUNT]))
AMOUNT
FROM
AGRPRD.GL_POSTING_LOG, SRPTPRD.A_CUSTS
WHERE
GL_SHIP_TO_CUST_ID = CUST_ID(+)
AND

******************************
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)>=[Forms]![frmDateRange]![txtFromDate] And
(GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate])) OR
(((GL_EFFECTIVE_DATE)<=[Forms]![frmDateRange]![txtToDate]))
******************************
AND
(((GL_PROCESS_TYPE)='I') AND ((GL_ACCOUNT_NBR)='480008') AND
((CUST_TYPE)<'100' OR (CUST_TYPE)='270') OR ([CUST_TYPE]='280')))
OR (((GL_ACCOUNT_NBR)='480024'))
OR (((GL_ACCOUNT_NBR)='580092'))

GROUP BY
EQP_REG, EQP_DIS, GL_ORDER_NBR, GL_ORDER_DATE, GL_PROCESS_TYPE,
GL_GL_WORKCENTER_TYPE, GL_GL_WORKCENTER_ID, GL_PROD_ID,
GL_PRODUCT_UOM, GL_ACCOUNT_NBR, GL_SHIP_TO_CUST_ID, CUST_NAME,
CUST_TYPE;

Comments:
- The join is handled in SQL-88 form in the WHERE clause rather than
using a SQL-92 JOIN expression. The (+) indicates a Left Outer Join.
- Use ' rather than " to delimit strings and dates
- I moved your HAVING clause into the WHERE clause since none of the
referenced fields are totals fields
- I simplified the OR logic on CUST_TYPE, possibly correctly, perhaps
not
- You'll need to replace all the Forms references by literal dates in
dd-MMM-yy format, building up a SQL string in code for your
passthrough

As I say - this is RUSTY and may not be accurate in all respects!

John W. Vinson[MVP]
 
Top