Problem in Query Analyzer with View Columns

[noodles1]

I have a view that uses a number of other views. The columns associated with this view do not appear in the Query Analyzer Object Browser. Running the view in Query Analyzer appears to produce satisfactory results.

If I alter the view by commenting out references to certain of the referenced views, and refresh the Object Browser, the columns of the main view return.

In fact a number of the referenced views are referenced multiple times within this main view, with different aliases through different join conditions. Commenting out different combinations of these referenced views allows the columns to appear in Object Browser.

All of the referenced views run satisfactorily when called individually.

Has anyone experienced this behaviour or can provide suggestions to remedy this?

 

[AngelB]

Haven't come across this before. Could you post the code for your main view please?

Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[noodles1]

Here is the code of the main view

ALTER  View dbo.vew_Rpt_ESP_AnnualBenefitStatement
As
Select	top 1000000 Product.ProductNo,
	Upper(IsNull(Product.ParentID, '')) As ParentID,
	Member.MbrNo, 
	Member.SchemeNo, 
	IsNull(Address.Line1, '') As Line1, 
 	IsNull(Address.Line2, '') As Line2, 
	IsNull(Address.City, '') As City,
	IsNull(dbo.fn_GetAUState(Address.PostalCode), '') As State, 
  	IsNull(Address.PostalCode, '') As PostalCode,
	IsNull(Address.Country, '') As Country, 
	IsNull(Person.FamilyName, '') As FamilyName, 
	IsNull(Person.GivenNames, '') As GivenNames, 
	dbo.fn_GetTitleFromSpecificSysCode(Person.Title, SchemeDetail.ProductNo) As Title,
	Case	When dbo.fn_GetTitleFromSpecificSysCode(Person.Title, SchemeDetail.ProductNo) = 'N/A' Then IsNull(Person.GivenNames,'')+' '+IsNull(Person.FamilyName,'')
		When IsNull(dbo.fn_GetTitleFromSpecificSysCode(Person.Title, SchemeDetail.ProductNo),'')='' Then IsNull(Person.GivenNames,'')+' '+IsNull(Person.FamilyName,'')
		Else dbo.fn_GetTitleFromSpecificSysCode(Person.Title, SchemeDetail.ProductNo)+' '+IsNull(Person.GivenNames,'')+' '+IsNull(Person.FamilyName,'')
		End As MemberName,
	Person.BirthDate, 
  	Case When IsNull(Person.SexCode,'')='F' Then 'Female' Else 'Male' End As Sex, 
	IsNull(Member.AltMbrNo, '') As AltMbrNo, 
	IsNull(Person.NationalIDNumber, '') As NationalIDNumber, 
	SchemeDetail.PrevRevDate As PrevReviewDate,
	SchemeDetail.CurrentRevDate As currentReviewDate, 
	IsNull(SchemeDetail.Name, '') As SchemeName, 
	IsNull(Company.CompanyName, '') As CompanyName, 
	Case When Member.SchemeNo = 415 Then 'Equity Trustees Limited' Else 'Trust Company Superannuation Services Limited' End As FooterTrusteeName,
	Case When Member.SchemeNo = 415 Then '46004031298' Else '49006421638' End As TrusteeABN,
	IsNull(SchemeDetail.TrusteeName, '') As TrusteeName,
	IsNull(SchemeDetail.RSELicenceNo, '') As RSELicenceNo,
	IsNull(SchemeDetail.RSERegistrationNo, '') As RSERegistrationNo,
	IsNull(SchemeDetail.AFSLicenceNo, '') As AFSLicenceNo,
	Case	When dbo.fn_GetTitleFromSpecificSysCode(TP.Title, SchemeDetail.ProductNo) = 'N/A' Then IsNull(TP.GivenNames, '')+' '+IsNull(TP.FamilyName, '')
		Else dbo.fn_GetTitleFromSpecificSysCode(TP.Title, SchemeDetail.ProductNo)+' '+IsNull(TP.GivenNames, '')+' '+IsNull(TP.FamilyName, '') 
		End As TrusteeContactName,
	IsNull(TP.Phone, '') As TrusteePhone,
	IsNull(TP.Fax, '') As TrusteeFax,
	IsNull(TP.EmailAddress, '') As TrusteeEmail,
	IsNull(TA.Line1, '') As TrusteeLine1, 
 	IsNull(TA.Line2, '') As TrusteeLine2, 
	IsNull(TA.City, '') As TrusteeCity, 
	IsNull(dbo.fn_GetAUState(TA.PostalCode), '') As TrusteeState, 
  	IsNull(TA.PostalCode, '') As TrusteePostalCode, 
 	IsNull(vew_ETP_ABatPrevReviewDate.ABTotAcct, 0.0) As OpeningBal, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_SG, 0.0) As Total_SG, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_DeemedMbr, 0.0) As Total_DeemedMbr, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_MbrVol, 0.0) As Total_MbrVol, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_EerVol, 0.0) As Total_EerVol, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_SpouseConts, 0.0) As Total_SpouseConts, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Child, 0.0) As Total_ChildConts, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_CoConts, 0.0) As Total_CoConts, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Transfers, 0.0) As Total_Transfers, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Income, 0.0) As TotIncome, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Mbr_Protect, 0.0) As TotMbrProt, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Rebate, 0.0) As TotTaxRebate,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.ContsTax, 0.0) As TotContTax,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.PAYGTax, 0.0) As TotPAYGTax, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.OtherTax, 0.0) As TotOtherTax, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Surcharge, 0.0) As TotSurchargeTax, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_GST, 0.0) As TotGST, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_RITC, 0.0) As TotRITC, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_GLP, 0.0) As TotGLP, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Fees, 0.0) As TotFee, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Payments, 0.0) As TotPayments,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Ins, 0.0) As Total_Insurance,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Brokerage, 0.0) As Total_Brokerage,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_ForgoneBenefits, 0.0) As Total_ForgoneBenefits,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_ManagementFees, 0.0) As [Management Fees],
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_WithdrawalFees, 0.0) As [Withdrawal Fees],
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_ContributionFees, 0.0) As [Contribution Fees],
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_SwitchFees, 0.0) As [Investment Switching Fees],
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_EstablishmentFees, 0.0) As [Establishment Fees],
	IsNull(vew_rpt_ESP_AnnReview_IRR.IRR_Ann, 0.0) As IRR,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_Commission, 0.0) As Commissions,
	IsNull(vew_ETP_ABatCurrReviewDate.ABPreserved, 0.0) As Preserved, 
	IsNull(vew_ETP_ABatCurrReviewDate.ABRNP, 0.0) As RestNonPres, 
 	IsNull(vew_ETP_ABatCurrReviewDate.ABUNP, 0.0) As UnRestNonPres, 
	IsNull(20000.0, 0.0) As DthIns, 
	IsNull(20000.0, 0.0) As Dth_TPDIns, 
	IsNull(20000.0, 0.0) As TPDIns, 
	IsNull(20000.0, 0.0) As SCIns, 
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.PAYGTax, 0.0) As TotPAYG,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_BenPmtFee, 0.0) As Total_BenPmtFee,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_AdminFees, 0.0) As Total_AdminFees,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_AssetFees, 0.0) As Total_AssetFees,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_DthPrems, 0.0) As Total_DthPrems,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_TPDPrems, 0.0) As Total_TPDPrems,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_DthTPDPrems, 0.0) As Total_DthTPDPrems,
	IsNull(vew_rpt_ESP_AnnReview_ContsHistSummary.Total_SCIPrems, 0.0) As Total_SCIPrems,
	IsNull(vew_RepMbrFundSummary.OMF, 0.0) As IndirectCosts,
	IsNull(vew_RepSchemeDetails.ABN, '') As ABN,
	IsNull(vew_RepSchemeDetails.[Contact Title], '') As [Contact Title],
	IsNull(vew_RepSchemeDetails.PrimaryContactPosition, '') As [Contact Position], 
	IsNull(vew_RepSchemeDetails.[Contact Surname], '') As [Contact Surname],
	IsNull(vew_RepSchemeDetails.[Contact GivenName], '') As [Contact GivenName],
	IsNull(vew_RepSchemeDetails.[Scheme AddLine1], '') As [Scheme AddLine1],
	IsNull(vew_RepSchemeDetails.[Scheme AddLine2], '') As [Scheme AddLine2], 
	IsNull(vew_RepSchemeDetails.[Scheme AddCity],  '') As [Scheme AddCity],
	IsNull(vew_RepSchemeDetails.[Scheme AddState],  '') As [Scheme AddState],
	IsNull(vew_RepSchemeDetails.[Scheme AddPostCode],  '') As [Scheme AddPostCode],
	IsNull(vew_RepSchemeDetails.[Scheme AddCountry], '') As [Scheme AddCountry],
	MJED.JoinedDate,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.Death_Cover, 0.0) As NewDthCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.TPD_Cover, 0.0) As NewTPDCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.Dth_TPD_Cover, 0.0) As NewDthTPDCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.SC_Cover, 0.0) As NewSCCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.Death_Prem, 0.0) As NewDthPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.TPD_Prem, 0.0) As NewTPDPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.Dth_TPD_Prem, 0.0) As NewDthTPDPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCoverNew.SC_Prem, 0.0) As NewSCPrem,
	SchemeDetail.InsuranceCoversEffFrom,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Death_Cover, 0.0) As DthCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.TPD_Cover, 0.0) As TPDCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Dth_TPD_Cover, 0.0) As DthTPDCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.SC_Cover, 0.0) As SCCover,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Death_Prem, 0.0) As DthPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.TPD_Prem, 0.0) As TPDPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Dth_TPD_Prem, 0.0) As DthTPDPrem,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.SC_Prem, 0.0) As SCPrem,
	IsNull(BPFee.FeeRate, 0.0) As SchemeWithdrawalFee,
	IsNull(PrevBenefit.AcctValue, 0.0) As PrevWithdrawalBenefit,
	IsNull(CurrentBenefit.AcctValue, 0.0) As WithdrawalBenefit,
 	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Death_Cover, 0.0) + 
		IsNull(CurrentBenefit.AcctValue, 0.0) As DeathBenefit,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.Dth_TPD_Cover, 0.0) + 
		IsNull(CurrentBenefit.AcctValue,0) As DTPDBenefit,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.TPD_Cover, 0.0) + 
		IsNull(CurrentBenefit.AcctValue,0) As TPDBenefit,
	IsNull(vew_rpt_ESP_AnnReview_InsuranceCover.SC_Cover, 0.0) As SCIBenefit,
	IsNull(AgentCompany.CompanyName, '') As AgentName,
	IsNull(AgentAddress.Line1,  '') As AgentAddressLine1,
	IsNull(AgentAddress.Line2, '') As AgentAddressLine2,
	IsNull(AgentAddress.City,  '') As AgentCity,
	IsNull(AgentAddress.PostalCode,  '') As AgentPostCode,
	IsNull(AgentAddress.State,  '') As AgentState,
	IsNull(AgentAddress.Country, '') As AgentCountry,
	IsNull(AgentPerson.Phone, '') As AgentPhone,
	IsNull(AgentPerson.Fax, '') As AgentFax,
	IsNull(AgentPerson.EmailAddress, '') As AgentEmail,
	IsNull(SchemeCompany.CompanyName, '') As FundAdministrator,
	IsNull(SchemeAddress.Line1, '') As FundAddressLine1,
	IsNull(SchemeAddress.Line2, '') As FundAddressLine2,
	IsNull(SchemeAddress.City, '') As FundCity,
	IsNull(SchemeAddress.State, '') As FundState,
	IsNull(SchemeAddress.PostalCode, '') As FundPostcode,
	IsNull(SchemeAddress.Country, '') As FundCountry,
	IsNull(SchemeContact.Phone, '') As FundPhone,
	IsNull(SchemeContact.Fax, '') As FundFax,
	IsNull(SchemeContact.EmailAddress, '') As FundEmail,
	Case	When isNull(Member.PayCentreNo, 0)=13608 Then 'Mike O''Connor (Employer Appointed)'
		When isNull(Member.PayCentreNo, 0)=13571 Then 'John Schrieber (Employer Appointed)'
		Else 'No Policy Committee in place'
		End As PolicyCommitteRep1,
	Case	When isNull(Member.PayCentreNo, 0)=13608 Then 'Gary Hopwood (Member Appointed)'
		When isNull(Member.PayCentreNo, 0)=13571 Then 'Alison Mulcahy (Member Appointed)'
		Else ''
		End As PolicyCommitteRep2

From dbo.Member
Inner Join dbo.SchemeDetail On Member.SchemeNo = SchemeDetail.SchemeNo 
Inner Join dbo.vew_RepSchemeDetails On SchemeDetail.SchemeNo = vew_RepSchemeDetails.SchemeNo 
Inner Join dbo.Product On SchemeDetail.ProductNo = Product.ProductNo 
Left Outer Join dbo.Person On Member.PersonNo=Person.PersonNo
Left Outer Join dbo.AddressUsage On Person.PersonNo=AddressUsage.EntityNo AND AddressUsage.EntityCode='PE'
Left Outer Join dbo.Address On AddressUsage.AddressNo=Address.AddressNo
Left Outer Join dbo.PayCentre On Member.PayCentreNo = PayCentre.PayCentreNo 
Left Outer Join dbo.Company On PayCentre.CompanyNo = Company.CompanyNo 
Left Outer Join dbo.Person TP On SchemeDetail.TrusteeContactNo=TP.PersonNo
Left Outer Join dbo.Address TA On SchemeDetail.TrusteeAddressNo=TA.AddressNo
Left Outer Join dbo.vew_rpt_ESP_AnnReview_IRR On vew_rpt_ESP_AnnReview_IRR.MbrNo = Member.MbrNo
Left Outer Join dbo.vew_RepContsHist_LatestMemberContributionDate_PdDate LCD On Member.MbrNo=LCD.MbrNo
Left Outer Join dbo.vew_Member_JoinedExitDates MJED On Member.MbrNO=MJED.MbrNo
Left Outer Join dbo.vew_rpt_ESP_AnnReview_ContsHistSummary On Member.MbrNo = vew_rpt_ESP_AnnReview_ContsHistSummary.MbrNo 
Left Outer Join dbo.vew_rpt_ESP_AnnReview_InsuranceCover On Member.MbrNo = vew_rpt_ESP_AnnReview_InsuranceCover.MbrNo
Left Outer Join dbo.vew_rpt_ESP_AnnReview_InsuranceCoverNew On Member.MbrNo = vew_rpt_ESP_AnnReview_InsuranceCoverNew.MbrNo
Left Outer Join dbo.vew_RepMbrFundSummary On (Member.MbrNo = vew_RepMbrFundSummary.MbrNo
					And vew_RepMbrFundSummary.EndDate = SchemeDetail.CurrentRevDate)
Left Outer Join (Select ProductNo, FeeBasisName, FeeRate, FeeBasis.FeeBasisNo, EntityNo As SchemeNo 
			From FeeBasis 
			Inner Join FeeList On FeeBasis.FeeBasisNo = FeeList.FeeBasisNo
			Where BasedOnCode = 'BP' And EntityType = 'SC' And FeeList.Inactive = 0) As BPFee
	On (Member.SchemeNo = BPFee.SchemeNo)
Left Outer Join dbo.vew_ETP_ABatCurrReviewDate On (Member.MbrNo = vew_ETP_ABatCurrReviewDate.MbrNo)
Left Outer Join dbo.vew_ETP_ABatPrevReviewDate On (Member.MbrNo = vew_ETP_ABatPrevReviewDate.MbrNo)
Left Outer Join AcctBal CurrentBenefit On (Member.MbrNo=CurrentBenefit.MbrNo)
					And (CurrentBenefit.EffDate=SchemeDetail.CurrentRevDate)
					And (CurrentBenefit.BaltypeCode='1000')
Left Outer Join AcctBal PrevBenefit On (Member.MbrNo=PrevBenefit.MbrNo)
					And (PrevBenefit.EffDate=SchemeDetail.PrevRevDate)
					And (PrevBenefit.BaltypeCode='1000')
Left Outer Join Agent On (Member.AgentNo = Agent.AgentNo)
Left Outer Join Company AgentCompany On (Agent.CompanyNo = AgentCompany.CompanyNo)
Left Outer Join Address AgentAddress On (Agent.AddressNo = AgentAddress.AddressNo)
Left Outer Join Person AgentPerson  on (Agent.ContactNo = AgentPerson.PersonNo)
Left Outer Join Company SchemeCompany On (SchemeDetail.SponsorNo = SchemeCompany.CompanyNo)
Left Outer Join Person SchemeContact On (SchemeCompany.PrimaryContactNo = SchemeContact.PersonNo)
Left Outer Join AddressUsage SAU On (SchemeDetail.SchemeNo = SAU.EntityNo And SAU.EntityCode = 'SC')
Left Outer Join Address SchemeAddress On (SAU.AddressNo = SchemeAddress.AddressNo)

Where Member.SchemeNo in (415, 433, 434, 435, 436)
And MJED.JoinedDate <= SchemeDetail.CurrentRevDate -- exlude members who joined after the review date
And isNull(MJED.ExitDate, SchemeDetail.CurrentRevDate) >= SchemeDetail.CurrentRevDate -- exclude members who exited before the review date
And MJED.StatusCode Not In ('VO') -- exclude void members
And isNull(Member.LostMbrsCode, '') <> 'L' -- exclude lost members
And IsNull(Address.Line1, '') <> '' -- exclude members with no address details

Order by Member.SchemeNo, Member.MbrNo

In doing some further testing, I found that if I remove the reference to the view, vew_rpt_ESP_AnnReviewContsHistSummary, (join statement & columns selected) and refresh on the Object Browser, no columns will be listed for this view in the Object Browser.

This referenced view appears OK in the Object Browser