question

Alans avatar image
Alans asked

View not updating when base table values change

Hi, I have the following view syntax below. The problem I am having is when I add a new record to the client table (base table), this information is not pulling through the view. Can anyone see anything wrong with the syntax, did I miss something when creating it? This is on SQL server 2008R2 CREATE VIEW [dbo].[__DOCKIT] AS SELECT ROW_NUMBER() OVER (ORDER BY Client.Name) AS ID, Client.Name AS "Full Name of Company", Client.cAccDescription AS "Full Trading Name", Client.Physical1 as "Physical Address 1", Client.Physical2 as "Physical Address 2", Client.Physical3 as "Physical Address 3", Client.Physical4 as "Physical Address 4", Client.Physical5 as "Physical Address 5", Client.PhysicalPC as "Physical Postal Code", Client.cWebPage as "Website Address", Client.Registration as "Company/CC Reg No.", Client.ulARucARRegistrationType as "Company Registration Type", LEFT(Client.Registration,4) as "Company/CC Registration Date", Client.Tax_Number as "VAT Reg No.", Client.Contact_Person as "Applicant Full Name", Client.Fax1 as "Cell", Client.Telephone as "Tel (w)", Client.Fax2 as "Fax", Client.EMail as "eMail", Client.ulARMembershipLevels as "Membership Level", Client.ulARucChapterRegion as "SATSA Chapters", Client.ulARAddcatAccomodation as "Accomodation Category", Client.ucARTotalNumberofRooms as "Number of Rooms", Client.ucARTotalNumberofBedsSingle as "Number of Beds - Single", Client.ucARTotalNumberofBedsTwin as "Twin", Client.ucARTotalNumberofBedsDoubles as "Doubles", Client.ulARAddCatBroker as "Broker Category", Client.ucARTotalNumberOfVehicles as "T - Total Number of Vehicles", Client.ucARTotalNumberOfAircraft as "T - Aircraft", Client.ucARTotalNumberOfBoats as "T - Boats", Client.ucARTotalNumberOfRailCoaches as "T - Rail Coaches", Client.ucARTotalPassengerCar0to9Seats as "T - Passenger Car (0-9 Seats)", Client.ucARTotalMiniBus10to16Seats as "T - Mini Bus (10-16 Seats", Client.ucARTotalMidiBus17to34Seats as "T - Midi Bus (17-34 Seats)", Client.ucARTotalTBus35PlusSeats as "T - Bus (35+ Seats)", Client.ucARTOTotalNumberOfVehicles as "TO - Total Number of Vehicles", Client.ucARTOTotalNumberOfAircraft as "TO - Aircraft", Client.ucARTOTotalNumberOfBoats as "TO - Boats", Client.ucARTOTotalNumberOfRailCoaches as "TO - Rail Coaches", Client.ucARTOTotalPassengerCar0to9Seats as "TO - Passenger Car (0-9 Seats)", Client.ucARTOTotallMiniBus10to16Seats as "TO - Mini Bus (10-16 Seats)", Client.ucARTOTotalMidiBus17to34Seats as "TO - Midi Bus (17-34 Seats)", Client.ucARTotalTBus35PlusSeats as "TO - Bus (35+ Seats", Client.ucARDirectorsName1 as "Shareholder 1 Full Name", Client.ucARDirectorsName2 as "Shareholder 2 Full Name", Client.ucARucARDirectorsName3 as "Shareholder 3 Full Name", Client.ucARDirectorsID1 as "Shareholder 1 ID No.", Client.ucARDirectorsID2 as "Shareholder 2 ID No.", Client.ucARucARDirectorsID3 as "Shareholder 3 ID No.", Client.ucARPercenShareholder as "Shareholder 1 % Shareholding", Client.ucARPercentageofShareholding2 as "Shareholder 2 % Shareholding", Client.ucARPercentageofShareholding3 as "Shareholder 3 % Shareholding", Client.ucARNumberOfFullTimeEmployees as "Number of Full Time Employees", Client.ucARNumberofPartTimeEmployees as "Number of Part Time Employees", Client.ulARActualCompanyTurnover as "Annual Company Turnover", Client.ucARTargetMarket as "Target Market", Client.ulARNearestAirport as "Nearest Airport", Client.ulARDistanceFromOfficetoAirport as "Distance From Office to Airport", Client.ucARAnnualNumberofPAX as "Annual Number of PAX", Client.ubAROwnVehiclesAircraftBoatsRailCoaches as "Own Vehicles/Aircraft/Boats/Rail Coaches", Client.ubAROutsourcedTransportVehicleHireUtilised as "Outsourced Transport/Vehicle Hire Utilised", Client.ucAROtherAssociations as "Other Associations", Client.BankAccNum as "Current Account Number", Client.ucARDateBankAccOpened as "Date Opened", BankMain.BankName as "Bank", Client.BranchCode as "Branch Code", Client.ucARAccountant as "Accountant/Auditor Name", Client.ucARInsuredBy as "Insurer Name",Client.ucARSignatoryName as "Signatory Name", Client.ucARSignatoryIDNumber as "Signatory ID Number", Client.ubARAcceptedTsandCsSignature as "Accepted T's and C's (Signature", Client.udARDateSigned as "Date Signed", Client.ucARSkype as "Skype", Cliclass.Description as "Group", Client.udARucdateapplied as "Date Applied", Client.ucARVehiclesPerRegionGauteng as "Vehicles per Region (Gauteng)", Client.ucARVehiclesPerRegionWesternCape as "Vehicles per Region (Western Cape)", Client.ucARVehiclesPerRegionEasternCape as "Vehicles per Region (Eastern Cape)", Client.ucARVehiclesPerRegionFreeState as "Vehicles per Region (Free State)", Client.ucARVehiclesPerRegionKwaZuluNatal as "Vehicles per Region (Kwa-Zulu Natal)", Client.ucARVehiclesPerRegionLimpopo as "Vehicles per Region (Limpopo)", Client.ucARVehiclesPerRegionMpumalanga as "Vehicles per Region (Mpumalanga)", Client.ucARVehiclesPerRegionNorthernCape as "Vehicles per Region (Northern Cape)", Client.ucARVehiclesPerRegionNorthWest as "Vehicles per Region (North West)", Client.ucAROperatingLicenseNoVehicleReg as "Operating License Number (Vehicle Registration)", Client.ucAROperatingLicenseNoPermitExp as "Operating License Number (Permit Expiration)", Client.ucAROperatingLicenseNoPermitNo as "Operating License Number (Permit Number)", Client.ucAROperatingLicenseNoSeats as "Operating License Number (Number of Seats)", Client.ucAROperatingLicenseNoYearModel as "Operating License Number (Year Model)", Client.ucAROperatingLicenseNoColour as "Operating License Number (Colour)", Client.ucAROperatingLicesneNoModel as "Operating License Number (Model)", Client.ucAROperatingLicenseNoMake as "Operating License Number (Make)", Client.udARucDateExpired as "Expiry Date", Client.udARucDateReinstated as "Date Reinstated", Client.udARucDateApproved as "Date Approved", Client.udARucdateTerminated as "Date Terminated", Client.ucARMembershipNo as "Membership No", _etblpricelistname.cName as "Member Status" FROM Client JOIN BankMain ON Client.BankLink = BankMain.Counter JOIN CliClass ON Client.iClassID = CliClass.IdCliClass JOIN _etblPriceListName ON Client.iARPriceListNameID = _etblPriceListName.IDPriceListName
sql-server-2008-r2syntax
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There is some syntax missing I see. It is just a join command between the different tables.
0 Likes 0 ·
I will look into this tonight and revert back. Thanks guys.
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
Thanks gentlemen, that was exactly it. Not all the join fields had relevant info in them. Ta
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
A view is nothing else than a Query, when run is interpreted as a sub-select. I'm guessing that if you add a row to one of the tables in the Query, and that row doesn't show up when selecting from the view, you have a column value which doesn't satisfy one or more of the join conditions, and therefore the client row is not displayed.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Beat me by 26 seconds!! HA!
1 Like 1 ·
JohnM avatar image
JohnM answered
I don't see anything wrong with the syntax. My initial guess is that the record your adding doesn't have corresponding records in the three inner joined tables, thus filtering out the record. Does the inserted record(s) have values for these columns: BankMain.Counter, CliClass.idCliClass, _etblPriceListName.IDPriceListname Hope that helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.