You can’t declare variables in a view. Could you make it into a function or stored procedure? Edit – you might also be able to put something into a CTE (Common Table Expression) and keep it as a view. e.g. WITH conditions as ( … do the STUFF here ) SELECT blah FROM blah INNER … Read more
Kept having to return here to look up pg_get_viewdef (how to remember that!!), so searched for a more memorable command… and got it: \d+ viewname You can see similar sorts of commands by typing \? at the pgsql command line. Bonus tip: The emacs command sql-postgres makes pgsql a lot more pleasant (edit, copy, paste, … Read more
Views can be indexed but CTE can’t. So this is one important point. CTE work excellent on tree hierarchyi.e. recursive Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, … Read more
You cannot create a primary key on a view. In SQL Server you can create an index on a view but that is different to creating a primary key. If you give us more information as to why you want a key on your view, perhaps we can help with that.
information_schema.columns.Table_name (at least under Sql Server 2000) includes views, so just use SELECT * FROM information_schema.columns WHERE table_name=”VIEW_NAME”
A view works like a table, but it is not a table. It never exists; it is only a prepared SQL statement that is run when you reference the view name. IE: CREATE VIEW foo AS SELECT * FROM bar SELECT * FROM foo …is equivalent to running: SELECT x.* FROM (SELECT * FROM bar) … Read more
In Entity Framework Core 2.1 we can use Query Types as Yuriy N suggested. A more detailed article on how to use them can be found here The most straight forward approach according to the article’s examples would be: 1.We have for example the following entity Models to manage publications public class Magazine { public … Read more
You are correct. Local variables are not allowed in a VIEW. You can set a local variable in a table valued function, which returns a result set (like a view does.) http://msdn.microsoft.com/en-us/library/ms191165.aspx e.g. CREATE FUNCTION dbo.udf_foo() RETURNS @ret TABLE (col INT) AS BEGIN DECLARE @myvar INT; SELECT @myvar = 1; INSERT INTO @ret SELECT @myvar; … Read more
A parameterless inline TVF and a non materialized View are very similar. A few functional differences that spring to mind are below. Views Accepts Parameters – No Expanded out by Optimiser – Yes Can be Materialized in advance – Yes (through indexed views) Is Updatable – Yes Can contain Multiple Statements – No Can have … Read more
You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULL has to be on the outside before SQL Server will understand that the resulting value can never be NULL. SELECT ISNULL(CAST( CASE Status WHEN 3 THEN 1 ELSE 0 END AS bit), 0) AS HasStatus FROM dbo.Product … Read more