ACCESS FEED: Re: [MS_AccessPros] Pass-Through query

Kamis, 15 Agustus 2013

Re: [MS_AccessPros] Pass-Through query

Duane

So I have been playing around and I tried this PT and I get an error with ODBC - call failed
sybase odbc driver adaptive servicer enterprise incorrect syntax newr '!', #102

Below is my sql statement

Jim Wagner

INSERT INTO ONE (
ACCOUNT
,ACCOUNT_CODE
,CURRENT_MONTH_ACTUAL
)
SELECT dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
,dbo_TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
,Sum(dbo_TRANSACTION_DETAIL_ALL.[AMOUNT_$]) AS [SumOfAMOUNT_$]
FROM ACCOUNTS
INNER JOIN dbo_TRANSACTION_DETAIL_ALL ON ACCOUNTS.ACCOUNT = dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
GROUP BY dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
,dbo_TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
,dbo_TRANSACTION_DETAIL_ALL.FISCAL_YEAR
,dbo_TRANSACTION_DETAIL_ALL.FISCAL_MONTH
,dbo_TRANSACTION_DETAIL_ALL.OBJECT_CODE
,dbo_TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE
HAVING (
((dbo_TRANSACTION_DETAIL_ALL.FISCAL_YEAR) = [Forms] ! [LOCAL_FORM] ! [CURRENT_FISCAL_YEAR])
AND ((dbo_TRANSACTION_DETAIL_ALL.FISCAL_MONTH) = [Forms] ! [LOCAL_FORM] ! [CURRENT_FISCAL_MONTH])
AND ((dbo_TRANSACTION_DETAIL_ALL.OBJECT_CODE) = "7010")
AND ((dbo_TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE) = "22")
);

Jim Wagner
________________________________

________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, August 14, 2013 4:41 PM
Subject: RE: [MS_AccessPros] Pass-Through query

BTW: this is a pass-through I wrote today to determine which factories were missing some required monthly data for reporting purposes. It would have taken hours to create and run this in Access. I get the results in a P-T in a second. The largest table (KPI_Data) has over 200,000 records. This query takes advantage of CTEs which aren't in Access/JET SQL. They are similar to subqueries but much easier to build.

WITH
cteLR as
(
-- these are the left KPI from your request
SELECT klrLIndCode as KPI
FROM KPI_LEFTRIGHT
WHERE klrKPI IN ('OSH','NCC','STC','SCF','MSF','CSF','ZPL','FVD')
UNION
-- these are the right KPI from your request
SELECT klrRIndCode
FROM KPI_LEFTRIGHT
WHERE klrKPI IN ('OSH','NCC','STC','SCF','MSF','CSF','ZPL','FVD')
),
cteFac as
(
-- these are all the factories in the reporting group
SELECT fagFacCode FacCode, facFactory
FROM KPI_FactoryGroups JOIN KPI_RptFactoryGroups on fagRFGID = rfgRFGID JOIN KPI_Factories on fagFacCode = facCode
WHERE fagRFGID = 85 and fagFacCode <>'G85'
),
cteMonths AS
(
-- these are the years and months
SELECT DISTINCT kdaYear yr, kdaMonth Mth
FROM KPI_Data
WHERE kdaYear = 2013 and kdaMonth BETWEEN 1 and 7
)
, cteCartesian As (
-- this is all possible combinations of KPI, Factories, and months
SELECT KPI, FacCode, facFactory, Yr, Mth
FROM cteLR, cteFac, cteMonths)
-- the selects the missing KPI values
SELECT FacCode, FacFactory,KPI, indTitle, Yr, Mth, kdaValue
FROM cteCartesian
JOIN kpi_AllIndicators on KPI = indCode
LEFT JOIN kpi_Data on kdaIndCode = KPI AND kdaFacCode = FacCode AND kdaYear = Yr and kdaMonth = Mth
WHERE kdaValue is null
ORDER BY facFactory, KPI, Yr, Mth

Duane

----------------------------------------
> To: ms_access_professionals@yahoogroups.com
> From: duanehookom@hotmail.com
> Date: Wed, 14 Aug 2013 17:03:17 -0500
> Subject: RE: [MS_AccessPros] Pass-Through query
>
> Jim,
>
> A pass-through is typically much much much more efficient than an Access query.
>
> P-T queries are read-only which might be a valuable feature.
>
> A P-T uses the SQL syntax of the database server so functionality like calling stored procedures, using CTEs, recursion, and other cool stuff that isn't available in Access queries.
>
> P-T queries aren't editable and can't take advantage of data not on your server (without a lot of work). There typically isn't a crosstab equivalent in most databases (again without a lot of work).
>
> Duane Hookom MVP
> MS Access
>
>
> ----------------------------------------
>> From: luvmymelody@yahoo.com
>>
>> Hello,
>>
>> So I have been researching pass-through queries and I cannot find anything that says why you should create a pass-through query. The only things I find is how to create the query. What is the difference between a pass-through query and a regular linked table?
>>
>> Thank You
>>
>> Jim Wagner
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]