|
|
Posted by Erland Sommarskog on 08/17/06 22:34
(s_wadhwa@berkeley.edu) writes:
> I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
> application with SQL Server as Backend.
>
> I'm having trouble converting Access Query into SQL Query. The Query is
> given below:
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SELECT DISTINCTROW Buildings.BuildingNumber,
> First(Buildings.BuildingName) AS FirstOfBuildingName,
> First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
> First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
> First(CityCodes.CityName) AS FirstOfCityName,
> First(CountyCodes.CountyName) AS FirstOfCountyName,
> First(Buildings.Address) AS FirstOfAddress,
> First(Buildings.YearConstructed) AS FirstOfYearConstructed,
> First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
> First(Buildings.NumberLevels) AS FirstOfNumberLevels,
> First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
> Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
> First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
> First(Buildings.SpecialArea) AS FirstOfSpecialArea,
> First(Buildings.CoveredUnenclosedGrossArea) AS
> FirstOfCoveredUnenclosedGrossArea
> FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
> INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
> Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
> Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
> Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
> Buildings.OwnershipCode) ON CountyCodes.CountyCode =
> CityCodes.CountyCode
> GROUP BY Buildings.BuildingNumber;
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Please can any one tell me substitue for First Function in Acess to SQL
> Function.
I don't know Access, but if I have understood it correctcly, First
returns the value for the "first" row in the group. What I don't know
if you are guaranteed that all these "first" will return data from the
same row from Buildings, or if they could be from different rows.
You see, in a relational database "first" is a not meaningful operation.
A table is a set of unordered tuples, and there is no first or last.
It could help if you posted the CREATE TABLE statements for the table,
including definitions of primary keys and foreign keys. It's also a good
idea to add a short description of what the query is supposed to achieve.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|