Search and sorting of lookup values with aliases

This topic is locked

Search and sorting of lookup values with aliases	10/4/2006 4:34:14 AM
ASPRunnerPro General questions
M maurello author I am trying to find a way to search and sort records retrieved with a query with several joins and aliases (for tables and field names). My query looks like this (part of it): SELECT ,GroupShipBuilder.GroupShipBuilder ,GroupShipBuilderCountry.Country AS GroupShipBuilderCountry ,GroupShipBuilderCountry.Flag AS GroupShipBuilderFlag FROM Vessels LEFT JOIN ShipBuilder ON ShipBuilder.ShipBuilderID = Vessels.ShipBuilderID LEFT JOIN GroupShipBuilder ON GroupShipBuilder.GroupShipBuilderID = ShipBuilder.GroupShipBuilderID LEFT JOIN Country GroupShipBuilderCountry ON GroupShipBuilderCountry.CountryID = GroupShipBuilder.CountryID I modified the commonfunctions.asp file in order to be able to search in this case the ShipBuilder by its name. However, when it comes to the country I do not know which table I have to use, the original Country or the alias GroupShipBuilderCountry. As a matter of fact I tried both and I keep getting an error. When if "dbo.Vessels"=strTable and "GroupShipBuilderCountry"=field then GetFullFieldName="[Country].[GroupShipBuilderCountry]" exit function I get the following error Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid column name 'GroupShipOwnerCountry'. /WorldFleet/Vessels_list.asp, line 750 or When if "dbo.Vessels"=strTable and "GroupShipBuilderCountry"=field then GetFullFieldName="[GroupShipBuilderCountry].[GroupShipBuilderCountry]" exit function I get the following error Microsoft OLE DB Provider for SQL Server error '80040e14' The multi-part identifier "Country.GroupShipBuilderCountry" could not be bound. /WorldFleet/Vessels_list.asp, line 750

Mark as Solved Mark as not solved

5 replies


	Sergey Kornilov admin 10/4/2006
	Mauro, try to print the whole SQL query on the web page and post it here.


M	maurello author 10/5/2006
	I solved the problem creating several views of the same country table in SQL Server. However I still get the following error when I try to View one record: Microsoft OLE DB Provider for SQL Server error '80040e14' Ambiguous column name 'VesselID'. /WorldFleet/Vessels_view.asp, line 72 The full query I use in ASP Runner to retrieve my main table is this one: SELECT Vessels.[VesselID] ,Vessels.[FunctLoc] ,Vessels.[LRF_No] ,Vessels.[BRL_No] ,Vessels.[YardNo] ,Vessels.[LRF_YardNo] ,Vessels.[BRL_YardNo] ,Vessels.[VesselName] ,Vessels.[LRF_VesselName] ,Vessels.[BRL_VesselName] ,Vessels.[LRF_CurrNameEffDate] ,Vessels.[LRF_VesselPrevName] ,Vessels.[LRF_PrevNameEffDate] ,Vessels.[LRF_VesselOriginalName] ,Vessels.[LRF_ClassSocietyID] ,Vessels.[ContractDate] ,Vessels.[LRF_ContractDate] ,Vessels.[BRL_ContractDate] ,Vessels.[ContractYear] ,Vessels.[LRF_ContractYear] ,Vessels.[BRL_ContractYear] ,Vessels.[DeliveryDate] ,Vessels.[LRF_DeliveryDate] ,Vessels.[BRL_DeliveryDate] ,Vessels.[DeliveryYear] ,Vessels.[LRF_DeliveryYear] ,Vessels.[BRL_DeliveryYear] ,Vessels.[ShipBuilderID] ,Vessels.[LRF_ShipBuilderID] ,Vessels.[BRL_ShipBuilderID] ,Vessels.[WRL_ShipBuilderID] ,GroupShipBuilder.GroupShipBuilder ,ASP_GroupShipBuilderCountry.Country AS GroupShipBuilderCountry ,ASP_GroupShipBuilderCountry.Flag AS GroupShipBuilderFlag ,GroupShipOwner.GroupShipOwner ,ASP_GroupShipOwnerCountry.Country AS GroupShipOwnerCountry ,ASP_GroupShipOwnerCountry.Flag AS GroupShipOwnerFlag ,Vessels.[LRF_Lenght] ,Vessels.[LRF_Depth] ,Vessels.[LRF_Draught] ,Vessels.[LRF_Breadth] ,Vessels.[LRF_Displacement] ,Vessels.[LRF_Speed] ,Vessels.[LRF_SpeedIndicatorID] ,Vessels.[BRL_MCRSpeed] ,Vessels.[LRF_CurrFlagID] ,ASP_CountryCurrFlag.Flag AS CurrentFlag ,Vessels.[LRF_CurrFlagEffDate] ,Vessels.[LRF_PrevFlagID] ,ASP_CountryPrevFlag.Flag AS PreviousFlag ,Vessels.[LRF_PrevFlagEffDate] ,Vessels.[LRF_IceClassID] ,Vessels.[LRF_PropulsionTypeID] ,Vessels.[LRF_VesselStatusID] ,Vessels.[GRT] ,Vessels.[LRF_GRT] ,Vessels.[BRL_GRT] ,Vessels.[NET] ,Vessels.[LRF_NET] ,Vessels.[DWT] ,Vessels.[LRF_DWT] ,Vessels.[BRL_DWT] ,Vessels.[TEU] ,Vessels.[LRF_TEU] ,Vessels.[BRL_TEU] ,Vessels.[LGC] ,Vessels.[LRF_LGC] ,Vessels.[BRL_LGC] ,Vessels.[VesselTypeID] ,VesselCluster.VesselClusterPic ,Vessels.[LRF_VesselTypeID] ,Vessels.[BRL_VesselTypeID] ,Vessels.[WRL_VesselTypeID] ,Vessels.[FuelTypeID] ,Vessels.[VesselSizeID] ,Vessels.[VesselInsertDate] FROM Vessels LEFT JOIN VesselType ON VesselType.VesselTypeID = Vessels.VesselTypeID LEFT JOIN VesselGroup ON VesselGroup.VesselGroupID = VesselType.VesselGroupID LEFT JOIN VesselCluster ON VesselCluster.VesselClusterID = VesselGroup.VesselClusterID LEFT JOIN ShipBuilder ON ShipBuilder.ShipBuilderID = Vessels.ShipBuilderID LEFT JOIN GroupShipBuilder ON GroupShipBuilder.GroupShipBuilderID = ShipBuilder.GroupShipBuilderID LEFT JOIN ASP_GroupShipBuilderCountry ON ASP_GroupShipBuilderCountry.CountryID = GroupShipBuilder.CountryID LEFT JOIN Ownership ON Ownership.VesselID = Vessels.VesselID AND Ownership.OwnerTypeID = 11 LEFT JOIN ShipOwner ON ShipOwner.ShipOwnerID = Ownership.ShipOwnerID LEFT JOIN GroupShipOwner ON GroupShipOwner.GroupShipOwnerID = ShipOwner.GroupShipOwnerID LEFT JOIN ASP_GroupShipOwnerCountry ON ASP_GroupShipOwnerCountry.CountryID = GroupShipOwner.CountryID LEFT JOIN ASP_CountryCurrFlag ON ASP_CountryCurrFlag.CountryID = Vessels.LRF_CurrFlagID LEFT JOIN ASP_CountryPrevFlag ON ASP_CountryPrevFlag.CountryID = Vessels.LRF_PrevFlagID


	Sergey Kornilov admin 10/5/2006
	Mauro, not really sure what is wrong without having access to code and database. You can create a View in SQL Server on the top of this query and use this View as a datasource in ASPRunnerPro.


C	clig 10/5/2006
	I solved the problem creating several views of the same country table in SQL Server. However I still get the following error when I try to View one record: Microsoft OLE DB Provider for SQL Server error '80040e14' Ambiguous column name 'VesselID'. /WorldFleet/Vessels_view.asp, line 72 The full query I use in ASP Runner to retrieve my main table is this one: SELECT Vessels.[VesselID] ,Vessels.[FunctLoc] ,Vessels.[LRF_No] ,Vessels.[BRL_No] ,Vessels.[YardNo] ,Vessels.[LRF_YardNo] ,Vessels.[BRL_YardNo] ,Vessels.[VesselName] ,Vessels.[LRF_VesselName] ,Vessels.[BRL_VesselName] ,Vessels.[LRF_CurrNameEffDate] ,Vessels.[LRF_VesselPrevName] ,Vessels.[LRF_PrevNameEffDate] ,Vessels.[LRF_VesselOriginalName] ,Vessels.[LRF_ClassSocietyID] ,Vessels.[ContractDate] ,Vessels.[LRF_ContractDate] ,Vessels.[BRL_ContractDate] ,Vessels.[ContractYear] ,Vessels.[LRF_ContractYear] ,Vessels.[BRL_ContractYear] ,Vessels.[DeliveryDate] ,Vessels.[LRF_DeliveryDate] ,Vessels.[BRL_DeliveryDate] ,Vessels.[DeliveryYear] ,Vessels.[LRF_DeliveryYear] ,Vessels.[BRL_DeliveryYear] ,Vessels.[ShipBuilderID] ,Vessels.[LRF_ShipBuilderID] ,Vessels.[BRL_ShipBuilderID] ,Vessels.[WRL_ShipBuilderID] ,GroupShipBuilder.GroupShipBuilder ,ASP_GroupShipBuilderCountry.Country AS GroupShipBuilderCountry ,ASP_GroupShipBuilderCountry.Flag AS GroupShipBuilderFlag ,GroupShipOwner.GroupShipOwner ,ASP_GroupShipOwnerCountry.Country AS GroupShipOwnerCountry ,ASP_GroupShipOwnerCountry.Flag AS GroupShipOwnerFlag ,Vessels.[LRF_Lenght] ,Vessels.[LRF_Depth] ,Vessels.[LRF_Draught] ,Vessels.[LRF_Breadth] ,Vessels.[LRF_Displacement] ,Vessels.[LRF_Speed] ,Vessels.[LRF_SpeedIndicatorID] ,Vessels.[BRL_MCRSpeed] ,Vessels.[LRF_CurrFlagID] ,ASP_CountryCurrFlag.Flag AS CurrentFlag ,Vessels.[LRF_CurrFlagEffDate] ,Vessels.[LRF_PrevFlagID] ,ASP_CountryPrevFlag.Flag AS PreviousFlag ,Vessels.[LRF_PrevFlagEffDate] ,Vessels.[LRF_IceClassID] ,Vessels.[LRF_PropulsionTypeID] ,Vessels.[LRF_VesselStatusID] ,Vessels.[GRT] ,Vessels.[LRF_GRT] ,Vessels.[BRL_GRT] ,Vessels.[NET] ,Vessels.[LRF_NET] ,Vessels.[DWT] ,Vessels.[LRF_DWT] ,Vessels.[BRL_DWT] ,Vessels.[TEU] ,Vessels.[LRF_TEU] ,Vessels.[BRL_TEU] ,Vessels.[LGC] ,Vessels.[LRF_LGC] ,Vessels.[BRL_LGC] ,Vessels.[VesselTypeID] ,VesselCluster.VesselClusterPic ,Vessels.[LRF_VesselTypeID] ,Vessels.[BRL_VesselTypeID] ,Vessels.[WRL_VesselTypeID] ,Vessels.[FuelTypeID] ,Vessels.[VesselSizeID] ,Vessels.[VesselInsertDate] FROM Vessels LEFT JOIN VesselType ON VesselType.VesselTypeID = Vessels.VesselTypeID LEFT JOIN VesselGroup ON VesselGroup.VesselGroupID = VesselType.VesselGroupID LEFT JOIN VesselCluster ON VesselCluster.VesselClusterID = VesselGroup.VesselClusterID LEFT JOIN ShipBuilder ON ShipBuilder.ShipBuilderID = Vessels.ShipBuilderID LEFT JOIN GroupShipBuilder ON GroupShipBuilder.GroupShipBuilderID = ShipBuilder.GroupShipBuilderID LEFT JOIN ASP_GroupShipBuilderCountry ON ASP_GroupShipBuilderCountry.CountryID = GroupShipBuilder.CountryID LEFT JOIN Ownership ON Ownership.VesselID = Vessels.VesselID AND Ownership.OwnerTypeID = 11 LEFT JOIN ShipOwner ON ShipOwner.ShipOwnerID = Ownership.ShipOwnerID LEFT JOIN GroupShipOwner ON GroupShipOwner.GroupShipOwnerID = ShipOwner.GroupShipOwnerID LEFT JOIN ASP_GroupShipOwnerCountry ON ASP_GroupShipOwnerCountry.CountryID = GroupShipOwner.CountryID LEFT JOIN ASP_CountryCurrFlag ON ASP_CountryCurrFlag.CountryID = Vessels.LRF_CurrFlagID LEFT JOIN ASP_CountryPrevFlag ON ASP_CountryPrevFlag.CountryID = Vessels.LRF_PrevFlagID MSSQL? If so may require a more specific join as a "join" or an "inner join" may logically work in Access but may need to be a "LEFT OUTER JOIN" in MSSQL...


M	maurello author 10/6/2006
	Mauro, not really sure what is wrong without having access to code and database. You can create a View in SQL Server on the top of this query and use this View as a datasource in ASPRunnerPro. Yes, I can, but I would lose the "edit" functionality.

5 replies

Mark as Solved Reply