|
|
Posted by Hugo Kornelis on 05/18/06 00:54
On 17 May 2006 10:02:37 -0700, s_wadhwa@berkeley.edu wrote:
>SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
>UCASE(Buildings.BuildingName) AS BuildingName,
>Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
>Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
>Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
>dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
>dCoveredUnenclosedGrossArea,
>IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
>Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
>Buildings.YearConstructed, Format$([NumberLevels],"00") AS
>dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
>dUnrelatedGrossArea, Buildings.YearLatestImprovement,
>UCASE(Buildings.Address) AS Address, Buildings.CityCode,
>CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
>Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
>Format$([Mechanical],"0000000") AS dMechanical,
>Format$([Custodial],"0000000") AS dCustodial
>FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode
>ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
>
>
>Please if anyone can help me in Converting the above given Access Query
>to Sql Server. I don't know which function to use for format$, IIF. I
>would really appreciate your suggestions.
>
>Thanks,
Hi s_wadhwa,
Change doouble quotes to single quotes.
Use + instead of & for string concatenation.
Replace UCASE() with UPPER()
Replace IIf with CASE (look it up in Books Online)
My knowledge of Format$() is very llimited, but if (for isntance) the
function Format$([Custodial],"0000000") is intended to format Custodial
as a 7-digit numeric string with leading zerooes (e.g. 0001234), then
you can replace it with:
RIGHT (REPLICATE('0', 7) + STR(Custodial), 7)
Finally, remove the constant from the ORDER BY list. It serves no
purpose.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|