Tag Info

New answers tagged

0

You could join the label table multiple times under different aliases specifying in the where clause that entries for the 2nd join be not equal to the 1st. Entries for the 3rd be not equal to the 1st or 2nd etc. e.g. Say we have a table of people id name 1 Paul 2 James 3 Scott and a table of places they have visited id peopleid placename 1 1 Africa 2 1 ...


0

You cannot have a view that dynamically adjusts the number of columns returned. That's something you need to implement with client-logic. At least with all databases I know of (you don't mention which database system you use).


2

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema? In that post I show these minimum privileges for a mysqldump SELECT SHOW VIEW (If any database has Views) TRIGGER (If any table has one or more triggers) LOCK TABLES (If you use an explicit ...


1

You're using po in the WHERE clause, and in effect, saying that it can't be null. Maybe use: IFNULL(po.tot2,0) instead of po.tot2


1

Export all the views of the database <DB>: mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \ information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql or: mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \ information_schema | ...


3

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet. To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row ...


5

A view, being just a query persisted in the database, should not theoretically have any performance hit. Same is true for column or table aliases. The performance hit, if any, will be barely noticeable once the execution plan is compiled and cached by the database engine. So, there is no performance justification not to use column aliases based on views. ...


6

A simple example: CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW dbo.vx AS SELECT a, b FROM dbo.x; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT a,b INTO #blat FROM dbo.vx; GO EXEC tempdb.dbo.sp_columns N'#blat'; GO DROP VIEW dbo.vx; DROP TABLE dbo.x, #blat; Partial output: COLUMN_NAME ...



Top 50 recent answers are included