SQLTeam.com | Weblogs | Forums

Issue with view

Hi There,
Can you help me please. When copying a view to a new database I am getting an error. But if I run on the new database the old sql it works. In the below code snippet you will see I am referencing a database called VanRental2013. when I run the code as a query this works. When I change the referenced database to VanRental-2019 I get an error of "Error in select clause: expression near '.'."
Is it because I have an hyphen in the database name VanRental-2019. This is my original code:

Blockquote
SELECT DISTINCT dbo.Rental.RentalId,
VanRental2013.dbo.Customer.FirstName,
VanRental2013.dbo.Customer.LastName,
VanRental2013.dbo.Customer.Add1,
VanRental2013.dbo.Customer.Add2,
VanRental2013.dbo.Customer.Add3,
VanRental2013.dbo.Customer.Town,
VanRental2013.dbo.Customer.County,
VanRental2013.dbo.Customer.Postcode,
VanRental2013.dbo.Customer.DrivingLicenseNumber,
VanRental2013.dbo.Customer.LicenseExpireyDate,
VanRental2013.dbo.Customer.Telephone,
VanRental2013.dbo.Customer.Telephone_2,
VanRental2013.dbo.Customer.Mobile,
VanRental2013.dbo.Customer.Email,
dbo.Rental.RentalStartDate,
dbo.Rental.RentalEndDate,
dbo.Rental.RentalStartTime,
dbo.Rental.RentalEndTime,
dbo.Rental.VehicleRegistrationNum,
dbo.Rental.InvoiceId,
dbo.Rental.VehicleId,
dbo.Rental.RentalTotalCost,
VanRental2013.dbo.Customer.CompanyName
FROM dbo.Rental INNER JOIN
VanRental2013.dbo.Customer ON dbo.Rental.CustomerID = VanRental2013.dbo.Customer.CustomerID

Thanks for the help.
Best Regards,
Steve.

Hello.

You cannot specify another select query inside the columns to be selected. Use "Join" instead of it.

This should work

SELECT DISTINCT R.RentalId,
    C.Customer.FirstName,
    C.Customer.LastName,
    C.Customer.Add1,
    C.Customer.Add2,
    C.Customer.Add3,
    C.Customer.Town,
    C.Customer.County,
    C.Customer.Postcode,
    C.Customer.DrivingLicenseNumber,
    C.Customer.LicenseExpireyDate,
    C.Customer.Telephone,
    C.Customer.Telephone_2,
    C.Customer.Mobile,
    C.Customer.Email,
    R.RentalStartDate,
    R.RentalEndDate,
    R.RentalStartTime,
    R.RentalEndTime,
    R.VehicleRegistrationNum,
    R.InvoiceId,
    R.VehicleId,
    R.RentalTotalCost,
    C.Customer.CompanyName
FROM dbo.Rental R INNER JOIN
[VanRental-2019].dbo.Customer C ON R.CustomerID = C.Customer.CustomerID