'SQL View output different to when query run independently

I have an SQL View that outputs a different result compared to when the same SQL query is run outside of the View, bizarre but there must be an answer.

With the View there are 2 records

Query run outside the View there are 3 records which is what the requirement is.

The SQL View uses a selection of unions to stack rows but in this case I am querying one part of SQL View. I use an additional filter to query the SQL View.

    select * from 
    [dbo].[BlockAllComponents]
    where 
    [PLACE-REF] =
    
    ('25928') and [Component Type] = 'lift'

|PLACE-REF| |Component Type|    Component Description   |Component Quantity|    |Lifecycle| |Unit of measure|   |Installation Date| |Progamme Year| |Unit Cost| |Total Cost per install|    |Leaseholder Contribution|  |Notes|
|25928| |Lift|  |Passenger Lift LOLER|  |1| |20|    |Number|    |01/01/2014|    |2034|  |56000| |56000| |NULL|  ||
|25928| |Lift|  |Passenger Lift LOLER|  |1| |20|    |Number|    |01/01/2013|    |2033|  |56000| |56000| |NULL|  ||

This is one part of the union of the source SQL from the View;

    select 
 comp.[PLACE-REF]
,[Component Type] =
case complook.[COMP-TYPE] 
WHEN 'WIND' THEN 'Window'
WHEN 'BATH' THEN 'Bathroom'
WHEN 'ROOF' THEN 'Roof'
WHEN 'COMM' THEN 'Communal'
WHEN 'KITC' THEN 'Kitchen'
WHEN 'DECO' THEN 'Decoration'
WHEN 'DOOR' THEN 'Door'
WHEN 'ELEC' THEN 'Electric'
WHEN 'HEHW' THEN 'Heating'
WHEN 'GAS' THEN 'Heating'
WHEN 'RENW' THEN 'Heating'
WHEN 'FLOR' THEN 'Floor'
WHEN 'ACES' THEN 'Access'
WHEN 'FIRE' THEN 'Fire'
WHEN 'LIFT' THEN 'Lift'
ELSE complook.[COMP-TYPE]
END
,complook.DESCRIPTION as 'Component Description'
, comp.QTY 'Component Quantity'
,cycles.[CYCLE-LENGTH] as 'Lifecycle'
,complook.UNIT 'Unit of measure'
,cast(comp.[INSTALLED-DATE] as date)[Installation Date]
, year(cycles.[PROG-YEARS]) as 'Progamme Year'
, '£' + convert(varchar,convert(decimal(10,2),cost.[BLOCK_unitCost])) as 'Unit Cost'
, '£' + convert(varchar,convert(decimal(10,2),(comp.QTY*cost.[BLOCK_unitCost]))) as 'Total Cost per install'
, '£' + convert(varchar,convert(decimal(10,2),isnull(((comp.QTY*cost.[BLOCK_unitCost]) / nullif(bp.[BLOCK-SIZE],0)),0) * nullif(bp.[LH/SO],0))) as 'Leaseholder Contribution'
, cycles.NOTES as 'Notes'
from   [SQLViewsPro2EOD].[dbo].[RM-LOC-COMPONENT] comp 
 JOIN [SQLViewsPro2EOD].[dbo].[RM-LOC-COMPONENT-CYCLES] cycles ON cycles.[PLACE-REF] = comp.[PLACE-REF] and cycles.[LIFE-CYCLE] in ( 'LREP')
 JOIN [SQLViewsPro2EOD].[dbo].[RM_COMPONENTS] complook ON complook.component = comp.component and complook.[COMP-TYPE] = 'LIFT' 
 AND comp.component = cycles.component AND comp.[SUB-LOC-CODE] = cycles.[SUB-LOC-CODE]
and comp.[BAR-CODE] = cycles.[BAR-CODE]
 join [DBAdmin].dbo.[BlockAssetsBusinessPlan] bp on comp.[PLACE-REF] = bp.[PLACE-REF]
 left join [DBAdmin].[dbo].[ComponentCostData] cost on complook.COMPONENT = cost.COMPONENT
-- left join (select [PLACE-REF],  [QTY-VALUE]  from [SQLViewsPro2EOD].dbo.[IH-LOC-ATTR] where [ATTRIB-CODE] = 'Stor' and [TERM-DATE] is null) stor on comp.[PLACE-REF] = stor.[PLACE-REF] 

where complook.COMPONENT in (
'LFTPASS'
,'LFTPANOM'
,'LFTPASMO'
,'LFTPAONM'

) and comp.[PLACE-REF] = '25928'

Output

|PLACE-REF| |Component Type|    Component Description   |Component Quantity|    |Lifecycle| |Unit of measure|   |Installation Date| |Progamme Year| |Unit Cost| |Total Cost per install|    |Leaseholder Contribution|  |Notes|
|25928| |Lift|  |Passenger Lift LOLER|  |1| |20|    |Number|    |01/01/2014|    |2034|  |56000| |56000| |NULL|  ||
|25928| |Lift|  |Passenger Lift LOLER|  |1| |20|    |Number|    |01/01/2014|    |2034|  |56000| |56000| |NULL|  ||
|25928| |Lift|  |Passenger Lift LOLER|  |1| |20|    |Number|    |01/01/2013|    |2033|  |56000| |56000| |NULL|  ||

Any ideas why this could be happening?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source