Menu
  • HOME
  • TAGS

Rewriting a sub-query into a single query so it can be put into a view in SQL

mysql,join,inner-join,sql-view

Subqueries are an unnecessarily complicated way of going about this. Easier just to join emp to works, work to dept, and then dept back to emp. select e.*, e2.* from emp e inner join works w on w.eid = e.eid inner join dept d on w.did = d.did inner join...

SQL - CTE vs VIEW

sql,sql-server,common-table-expression,sql-view

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...

T-SQL force null value based on condition

sql,tsql,sql-view

Try this: SELECT e.Text AS StatusText, a.Created AS [DATE], CASE a.status WHEN 2 THEN NULL WHEN 3 THEN NULL ELSE a.Username END, b.Name AS CustomerName, c.Name AS ServiceName, d.Message AS DeviationMessage FROM dbo.StatusUpdate AS a LEFT OUTER JOIN dbo.Customer AS b ON a.CustomerId = b.CustomerID LEFT OUTER JOIN dbo.Service AS...

LINQ version of SQL View?

c#,linq,sql-view

You might look to SQL views as a result set, that may combine the values of multiple tables (by joining, using sub-select's etc.). If you query a SQL view, you get better performance. Linq 2 SQL returns you set of data and does a mapping to an object, so you...

SQL joins with views

sql,sql-server,performance,join,sql-view

You are not correct when you state "It appears to me that in general the join of views is not recommended." Views can be combined with other views and will perform well provided that all JOINs are optimizable and have the appropriate index created and any filtering done within the...

Mysql: Create a view with multiple self joins without duplicates in result

mysql,duplicates,self-join,sql-view

Try using conditional aggregation instead: select eav.entity_id, max(case when entity_attributes_id = 2 then eav.value end) as company_id, max(case when entity_attributes_id = 1 then eav.value end) as entity_name, max(case when entity_attributes_id = 3 then eav.value end) as company_name, . . . from entities_attributes_values eav group by eav.entity_id; This will make it...

Finding the minimum of two columns from two different rows in a SQL table

sql,sql-server,sql-view

Aggregate functions, such as MIN are for aggregating across multiple rows, and will not do what you expect here (as you've already found out). Instead, you should use a case to choose which table and column to show from. Yes, I mean "table" here because your two joined rows are...

Concat VARCHAR2 fields in Oracle DB

sql,oracle11g,concat,sql-view

SELECT TRIM(USER.PNAME || ' ' || USER.FNAME) AS "NAME" ...

Grouping MySQL view in a sequence of rolling dates

mysql,sql-view

Yes, you would need to create the "GroupBillRange" table. Otherwise you lose any record of what the Bill Start/Stop dates were on the old groups. (Sure, you could assume that they are always from the 15th to the 14th. But the one time that they are not, you will have...

Why *_PROCEDURES view doesn't show the PROCEDURE_NAME

oracle,stored-procedures,plsql,metadata,sql-view

From documentation, ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified. It doesn't clarify...

Updatable View via Form?

c#,entity-framework,asp.net-mvc-4,sql-view,updatable-views

Here is my work around, essentially using the data from the initial view to present data to the model, then on post map the data to the actual table (used within initial view) that contains a Primary Key: public ActionResult Customer(int id) { var viewModel = new Customer(); using (var...

Trigger for select function

sql,postgresql,pattern-matching,sql-view

First, you can simplify your SELECT. select concat_ws ('/' , c.abreviation , to_char(date_op,'yy/mm') , to_char(compteur_op_cat, 'FM000') ) AS num_bord from operation o join categories c USING (id_cat); But there are no "triggers on SELECT". You may be looking for a VIEW or a MATERIALIZED VIEW. The key to performance with...

MySQL Error 1349 SELECT in SubQuery Issue

mysql,subquery,sql-view

Your outer query isn't doing anything, so just remove it: SELECT H.username, H.variety, IFNULL(SUM(H.weight),0) - IFNULL(HU.weight,0) As qty FROM harvest H LEFT JOIN harvest_used HU ON H.variety = HU.variety AND H.username = HU.username WHERE H.username = 'palendrone' GROUP BY H.variety ORDER BY H.variety ASC; MySQL does not allow subqueries in...

Need an SQL to create a View

sql,oracle,sql-view

use this query: I was unable to connect to SQLfiddler. So, do check the code and tell me what it returned. select * from ( SELECT id, name, value FROM table A) pivot ( max(value) for name in ('dog name', 'cat name', 'childs') ) order by id You can learn...

SQL: Create view from 2 tables printing null values when no records

sql,average,sql-view

Use an OUTER JOIN: select `l`.`ID` AS `IDL`, `l`.`NAME` AS `NAME`, AVG(`lr`.`RATING`) AS `RATING` from `LESSONS` AS `l` LEFT JOIN `RATINGS` AS `lr` ON `l`.`ID` = `lr`.`ID` group by `l`.`ID`; Also, I don't think there is a need for the Case statement -- you can just use AVG(lr.rating). A Visual...

View based on SELECT with 'WITH' clause

sql,oracle,sql-view

Try dropping the parentheses: create view ex_view as with alias1 as (select...), alias2 as (select ... from alias1), alias3 as (select col1, col2 ... from alias2) from alias3; ...

Detecting Data Transitions in SQL Data using View and Lag()

sql,sql-server,sql-view

Use lag() with a subquery: create view alarming_data as select id, data1, data1high, data1.low from (select sd.*, lag(data1) over (order by id) as prevvalue from SensorData sd ) sd where data1 <> prevvalue; If you want the first value, then include or prevvalue is null in the where clause....

Combining two columns of a table and returning it as a single column separated by 'slash' if both the values are NOT NULL

sql,sql-server,sql-view

SELECT Id, CASE WHEN Give IS NOT NULL and Quanity IS NOT NULL THEN Give + '/' + Quanity WHEN Give IS NOT NULL and Quanity IS NULL THEN Give WHEN Give IS NULL and Quanity IS NOT NULL THEN Quanity WHEN Give IS NULL and Quanity IS NULL THEN...

Oracle SQL query to fetch the values with the help of “Case When” Clause

oracle,select,oracle11g,case,sql-view

What you are doing is similiar to: with d1 as (select 1 col1, 'A' col2 from dual union select 2, 'B' from dual), d2 as (select 1 col1, 'Q' col2, 'W' col3, 'E' col4 from dual), d3 as (select 2 col1, 'R' col2, 'T' col3, 'Y' col4 from dual) select...

Create A View, Select a Column As Primary Key

sql,entity,primary-key,sql-view

You cannot create primary keys on the view itself. To get around the no primary key issue, you can create a unique column that the entity framework will pick up and use. ISNULL(CAST((row_number() OVER (ORDER BY <columnName>)) AS int), 0) AS ID So: SELECT ISNULL(CAST((row_number() OVER (ORDER BY uniqueid)) AS...

Check If View Exists

c#,sql-view,sql-delete

why would the sql command be any different if executed as a command from C# ? the following should work: using (var command1 = connection.CreateCommand()) { command1.CommandText = "IF EXISTS(select * from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'ViewName') DROP VIEW dbo.ViewName"; //todo: execute command, etc... } ...

Spring JPA: Mapping views without a primary key

spring,hibernate,jpa,sql-view

You can add a UUID column to every row of the Views so then you can use the UUID column as an @Id.

Need a MySQL JOIN to return all categories a product is in, per product, even if only one category is searched for

mysql,join,sql-view,sqlperformance

The following query works: (although I am not using the view) select pc1.product_id, products.name, products.section, group_concat(categories.name) from products inner join product_categories pc1 on (pc1.product_id = products.product_id and pc1.category_id = 3) inner join product_categories pc2 on (pc2.product_id = products.product_id) inner join categories on (categories.category_id = pc2.category_id) group by pc1.product_id, products.name, products.section...

Incorrect sorting order of NULL values with a SQL view

sql-server,sql-view

Assuming your grades have a limit to how high they can go could you use an isnull in your orderby and set a default high value for null occurences and a tie breaker for when two values have the same rank (e.g 35) e.g CREATE VIEW [dbo].[test] AS SELECT distinct...

I have a view that runs fine when executed, but when I try to open it in design mode, it throws a parsing error

sql-server,tsql,sql-server-2008-r2,sql-server-2012,sql-view

There is no solution to the problem except using T-SQL in management studio. This is a bug, or as Microsoft calls it by design. The view designer has more issues then just BIt datatypes. There are several valid SQL syntaxes that are not supported by designer. That's the reason I...