Hi Folks,

I am working on a project whereby an external company has supplied some native SQL code, but unfortunately it doesn't work within access. I would be most grateful if someone could take a look at this code and tell/show me how to convert it for use within access.

==========
BEGIN CODE
==========

SELECT TOP (100) PERCENT dbo.Workflow.UserName, dbo.Applications.Caption AS Application, dbo.Flows.Caption AS Flow,
dbo.StationTypes.Caption AS StationType, dbo.Stations.Caption AS Station, dbo.Collections.Name AS Collection, dbo.Workflow.TimeIn,
dbo.Workflow.TimeOut, dbo.Workflow.FormCount, dbo.Workflow.PageCount, dbo.Workflow.FieldCount, dbo.Workflow.LineCount,
CASE WHEN TimeOut < '99991231' THEN datediff(second, TimeIn, TimeOut) ELSE 0 END AS ProcessTimeInSeconds,
CASE WHEN PageCount > 0 THEN CASE WHEN TimeOut < '99991231' THEN datediff(second, TimeIn, TimeOut)
ELSE 0 END / PageCount ELSE 0 END AS ProcessTimePerPage
FROM dbo.Applications INNER JOIN
dbo.Collections ON dbo.Applications.Id = dbo.Collections.ApplicationId INNER JOIN
dbo.Flows ON dbo.Applications.Id = dbo.Flows.ApplicationId INNER JOIN
dbo.Stations ON dbo.Applications.Id = dbo.Stations.ApplicationId INNER JOIN
dbo.StationTypes ON dbo.Stations.StationType = dbo.StationTypes.Id INNER JOIN
dbo.Workflow ON dbo.Collections.Id = dbo.Workflow.CollId AND dbo.Flows.Id = dbo.Workflow.FlowId AND
dbo.Stations.Id = dbo.Workflow.StationId
ORDER BY dbo.Workflow.UserName, Application, Flow, StationType, Station, Collection, dbo.Workflow.TimeIn

==========
END CODE
==========

Any assistance would be gratefully appreciated

John

SELECT TOP (100) PERCENT dbo.Workflow.UserName, dbo.Applications.Caption AS Application, dbo.Flows.Caption AS Flow,
dbo.StationTypes.Caption AS StationType, dbo.Stations.Caption AS Station, dbo.Collections.Name AS Collection, dbo.Workflow.TimeIn,
dbo.Workflow.TimeOut, dbo.Workflow.FormCount, dbo.Workflow.PageCount, dbo.Workflow.FieldCount, dbo.Workflow.LineCount,

IIF(TimeOut < '99991231' , DateDiff("s", TimeIn, TimeOut) , 0 ) AS ProcessTimeInSeconds,

IIF( PageCount > 0 ,
IIF( TimeOut < '99991231' , DateDiff("s", TimeIn, TimeOut) , 0 ) / PageCount , 0 )) AS ProcessTimePerPage

FROM dbo.Applications INNER JOIN
dbo.Collections ON dbo.Applications.Id = dbo.Collections.ApplicationId INNER JOIN
dbo.Flows ON dbo.Applications.Id = dbo.Flows.ApplicationId INNER JOIN
dbo.Stations ON dbo.Applications.Id = dbo.Stations.ApplicationId INNER JOIN
dbo.StationTypes ON dbo.Stations.StationType = dbo.StationTypes.Id INNER JOIN
dbo.Workflow ON dbo.Collections.Id = dbo.Workflow.CollId AND dbo.Flows.Id = dbo.Workflow.FlowId AND
dbo.Stations.Id = dbo.Workflow.StationId
ORDER BY dbo.Workflow.UserName, Application, Flow, StationType, Station, Collection, dbo.Workflow.TimeIn

You need to change the Case statements to nested IIF's and the DateDiff uses "s" apposed to "seconds". Not sure whether the nesting is correct without testing it but that is the essence of the task.

David

Hi DCrake,

Thanks for that, I will let you know how I get on with it.

John

Hi DCrake,

I plugged the SQL code into the SQL view of a query and access doesn't like the "PERCENT", It highlights this word and I get the following message:

"The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

Any suggestions would be appreciated.

John

Simply replace the first line with Select *

Since you're wanting a Jet SQL syntax then I assume this isn't an ADP - hence you'll need to drop all the dbo. prefixes on the object names (your linked tables will be named differently - perhaps instead of "dbo.Workflow" you'll have "dbo_Workflow" if you've linked using the wizard or just plain linked names such as "Workflow".

The syntax error you're getting is because the TOP value is wrapped in brackets - this is a T-SQL syntax (and quite an important difference - especially in SQL Server 2005 onwards). In Jet you'll need to remove it.
e.g.
SELECT TOP 100 PERCENT Workflow.UserName, Applications.Caption AS Application... etc...

Of course - it's important to point out (as I believe David just has) that your TOP predicate is redundant.
TOP 100 PERCENT is basically everything. Unless you plan to lower the value and request only a specific amount then it makes sense to remove it.
It will only have been included as this probably came from a View in a SQL 2000 box whereby the TOP predicate was a hack to allow Views to support sorting (which has now been dropped).

Cheers.