x
Create View [lossrun].vw_StagingDailyClaim as SELECT TPA,ClaimNumber,ClientName,ClaimStatus,DateReported,DateOfLoss,EntryDate,DateClosed,DateReopened,AccidentDescription,EffectiveDate,ExpirationDate,PolicyNumber,AccidentState,RiskState,JurisdictionState,Carrier,LOBDescription,ProgramCode,PEO,LossPaid,MedicalPaid,SubroRecovery,SalvageRecovery,DeductRecovery,ExpensePaid,Recovery,LossOS,MedicalOS,ExpenseOS,Incurred,InsuredName,DateCreated,CreatedBy,UpdatedBy,DateUpdated,CatastropheNumb,IndemnityRecovery,ExpenseRecovery,CurrentAdjusterFROM(SELECT 'Athens' as TPA , ClaimNumber, ClaimStatus, DateReported, DateOfLoss, EntryDate, DateClosed, DateReopened, AccidentDescription, EffectiveDate, ExpirationDate, PolicyNumber, AccidentState, RiskState, JurisdictionState, Carrier, LOBDescription, ProgramCode, PEO, LossPaid, ExpensePaid, Recovery, LossOS, ExpenseOS, Incurred, InsuredName, DateCreated, CreatedBy, UpdatedBy, DateUpdatedFROM Lossrun.LandingAthensDailyClaimUNION SELECT 'CBSC' as TPA,ClaimNumber, ClaimStatus, DateReported, DateOfLoss, EntryDate, DateClosed, DateReopened, AccidentDescription, EffectiveDate, ExpirationDate, PolicyNumber, AccidentState, RiskState, JurisdictionState, Carrier, LOBDescription, ProgramCode, PEO, LossPaid, ExpensePaid, Recovery, LossOS, ExpenseOS, Incurred, InsuredName, DateCreated, CreatedBy, UpdatedBy, DateUpdatedFROM Lossrun.LandingCBSCDailyClaimUNION ALLSELECT 'NARS' as TPA,ClaimNumber,ClientName,ClaimStatus,cast(DateReported as Date) DateReported,cast(DateOfLoss as Date) DateOfLoss,EntryDate,cast(DateClosed as Date) DateClosed,cast(DateReopened as Date) DateReopened,AccidentDescription,EffectiveDate,ExpirationDate,PolicyNumber,AccidentState,RiskState,JurisdictionState,Carrier,LOBDescription,ProgramCode,PEO,LossPaid,MedicalPaid,SubroRecovery,SalvageRecovery,DeductRecovery,ExpensePaid,Recovery,LossOS,MedicalOS,ExpenseOS,Incurred,InsuredName,DateCreated,CreatedBy,UpdatedBy,DateUpdated,CatastropheNumb from Lossrun.LandingNARSDailyClaim UNIONSELECT'RMA' as TPA,ClaimNumber,ClientName,ClaimStatus,cast(DateReported as Date) DateReported,cast(DateOfLoss as Date) DateOfLoss,EntryDate,cast(DateClosed as Date) DateClosed,cast(DateReopened as Date) DateReopened,AccidentDescription,EffectiveDate,ExpirationDate,PolicyNumber,AccidentState,RiskState,JurisdictionState,Carrier,LOBDescription,ProgramCode,PEO,LossPaid,MedicalPaid,SubroRecovery,SalvageRecovery,DeductRecovery,ExpensePaid,Recovery,LossOS,MedicalOS,ExpenseOS,Incurred,InsuredName,DateCreated,CreatedBy,UpdatedBy,DateUpdated,CatastropheNumb from Lossrun.LandingNARSDailyClaim )Q This is error statement in SSMS “All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.” why can’t i create view like this?
Advertisement
Answer
All the sub queries need to have the same number of columns, with the same names and datatypes, and in the same order. If some subqueries/tables don’t have columns that other subqueries/tables do, you still need to define those columns.
For example, Lossrun.LandingAthensDailyClaim doesn’t have a ClientName column defined in its subquery. But based on the view definition and other subqueries, we still need to add it. We can use NULL for the value in this case.
Taking your view definition, I came up with the following query:
CREATE VIEW lossrun.vw_StagingDailyClaimASSELECT TPA ,ClaimNumber ,ClientName ,ClaimStatus ,DateReported ,DateOfLoss ,EntryDate ,DateClosed ,DateReopened ,AccidentDescription ,EffectiveDate ,ExpirationDate ,PolicyNumber ,AccidentState ,RiskState ,JurisdictionState ,Carrier ,LOBDescription ,ProgramCode ,PEO ,LossPaid ,MedicalPaid ,SubroRecovery ,SalvageRecovery ,DeductRecovery ,ExpensePaid ,Recovery ,LossOS ,MedicalOS ,ExpenseOS ,Incurred ,InsuredName ,DateCreated ,CreatedBy ,UpdatedBy ,DateUpdated ,CatastropheNumb ,IndemnityRecovery ,ExpenseRecovery ,CurrentAdjusterFROM( SELECT TPA = 'Athens' ,ClaimNumber ,ClientName = NULL ,ClaimStatus ,DateReported ,DateOfLoss ,EntryDate ,DateClosed ,DateReopened ,AccidentDescription ,EffectiveDate ,ExpirationDate ,PolicyNumber ,AccidentState ,RiskState ,JurisdictionState ,Carrier ,LOBDescription ,ProgramCode ,PEO ,LossPaid ,MedicalPaid = NULL ,SubroRecovery = NULL ,SalvageRecovery = NULL ,DeductRecovery = NULL ,ExpensePaid ,Recovery ,LossOS ,MedicalOS = NULL ,ExpenseOS ,Incurred ,InsuredName ,DateCreated ,CreatedBy ,UpdatedBy ,DateUpdated ,CatastropheNumb = NULL ,IndemnityRecovery = NULL ,ExpenseRecovery = NULL ,CurrentAdjuster = NULL FROM Lossrun.LandingAthensDailyClaim UNION SELECT TPA = 'CBSC' ,ClaimNumber ,ClientName = NULL ,ClaimStatus ,DateReported ,DateOfLoss ,EntryDate ,DateClosed ,DateReopened ,AccidentDescription ,EffectiveDate ,ExpirationDate ,PolicyNumber ,AccidentState ,RiskState ,JurisdictionState ,Carrier ,LOBDescription ,ProgramCode ,PEO ,LossPaid ,MedicalPaid = NULL ,SubroRecovery = NULL ,SalvageRecovery = NULL ,DeductRecovery = NULL ,ExpensePaid ,Recovery ,LossOS ,MedicalOS = NULL ,ExpenseOS ,Incurred ,InsuredName ,DateCreated ,CreatedBy ,UpdatedBy ,DateUpdated ,CatastropheNumb = NULL ,IndemnityRecovery = NULL ,ExpenseRecovery = NULL ,CurrentAdjuster = NULL FROM Lossrun.LandingCBSCDailyClaim UNION ALL SELECT TPA = 'NARS' ,ClaimNumber ,ClientName ,ClaimStatus ,DateReported = CAST(DateReported AS DATE) ,DateOfLoss = CAST(DateOfLoss AS DATE) ,EntryDate ,DateClosed = CAST(DateClosed AS DATE) ,DateReopened = CAST(DateReopened AS DATE) ,AccidentDescription ,EffectiveDate ,ExpirationDate ,PolicyNumber ,AccidentState ,RiskState ,JurisdictionState ,Carrier ,LOBDescription ,ProgramCode ,PEO ,LossPaid ,MedicalPaid ,SubroRecovery ,SalvageRecovery ,DeductRecovery ,ExpensePaid ,Recovery ,LossOS ,MedicalOS ,ExpenseOS ,Incurred ,InsuredName ,DateCreated ,CreatedBy ,UpdatedBy ,DateUpdated ,CatastropheNumb ,IndemnityRecovery = NULL ,ExpenseRecovery = NULL ,CurrentAdjuster = NULL FROM Lossrun.LandingNARSDailyClaim UNION SELECT TPA = 'RMA' ,ClaimNumber ,ClientName ,ClaimStatus ,DateReported = CAST(DateReported AS DATE) ,DateOfLoss = CAST(DateOfLoss AS DATE) ,EntryDate ,DateClosed = CAST(DateClosed AS DATE) ,DateReopened = CAST(DateReopened AS DATE) ,AccidentDescription ,EffectiveDate ,ExpirationDate ,PolicyNumber ,AccidentState ,RiskState ,JurisdictionState ,Carrier ,LOBDescription ,ProgramCode ,PEO ,LossPaid ,MedicalPaid ,SubroRecovery ,SalvageRecovery ,DeductRecovery ,ExpensePaid ,Recovery ,LossOS ,MedicalOS ,ExpenseOS ,Incurred ,InsuredName ,DateCreated ,CreatedBy ,UpdatedBy ,DateUpdated ,CatastropheNumb ,IndemnityRecovery = NULL ,ExpenseRecovery = NULL ,CurrentAdjuster = NULL FROM Lossrun.LandingNARSDailyClaim) Q;