Re: Oracle SQL vs. Access SQL
- From: "Rick Brandt" <rickbrandt2@xxxxxxxxxxx>
- Date: Mon, 18 Apr 2005 16:21:38 GMT
Kirk P. wrote:
> 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?
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.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
.
- Follow-Ups:
- Re: Oracle SQL vs. Access SQL
- From: Kirk P.
- Re: Oracle SQL vs. Access SQL
- References:
- Oracle SQL vs. Access SQL
- From: Kirk P.
- Oracle SQL vs. Access SQL
- Prev by Date: Oracle SQL vs. Access SQL
- Next by Date: Re: Erroneous Errors!
- Previous by thread: Oracle SQL vs. Access SQL
- Next by thread: Re: Oracle SQL vs. Access SQL
- Index(es):
Relevant Pages
|
