Search code examples
sqlsql-serversql-server-2016temporal

SQL Server 2016 - Hidden Column Doesn't Return from Nested Select

I hope someone can help me here. I'm not a SQL developer so my knowledge is quite limited. I'm trying to return the ValidFrom column in a nested SQL query from scala, but it doesn't appear. I can return it from a non-nested query, but it needs to be nested. In its simplest format it looks like this: 

select * from (select *, ValidFrom from [dbo].[Sale]) s

Any help on how to do this would be greatly appreciated. Thank you.

Solution

  • The hidden status for the columns in the subquery will propagate to the outer query. Can you modify the outer query? Is so you can try something like:

    SELECT *, ValidFrom FROM (SELECT *, ValidFrom FROM [dbo].[Sale]) s

    If not, you can apply some transformation to the ValidFrom column in the inner query and the new column will not have a hidden status, like this:

    SELECT * FROM (SELECT *, CAST(ValidFrom AS DATETIME2(0)) AS ValidFrom FROM [dbo].[Sale]) s

    Try to CAST it to the same datatype you are already using for the column. It will introduce at least an extra Compute Scalar operator in your plan, so try it and validate the performance.