Hot answers tagged

51

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


7

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 It means in order to determine whether subquery is correlated Oracle must try to resolve names in ...


5

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution. You have a single table that is crammed by several design entities. While this is not bad per se, you ...


3

Aggregate functions, but not on the same object, in a subquery, with addition. Use this: SELECT (SELECT COUNT(T1.FIELD) FROM T1) + (SELECT COUNT(T2.FIELD) FROM T2) FROM DUAL;


3

There is no name field in table_b so Oracle takes the one from table_a. I tried the EXPLAIN PLAN but this gave me only that there is a TABLE ACCESS FULL. I presume that this will generate some kind of Cartesian Product between both tables that result in a list of all the names in table_a is returned by the sub-query.


3

Your not exists sub-query doesn't work. It will always return a 1 if ANY employee exists that has a null statusChangeEndDate or that meets the criteria you are testing so basically will always exist. To continue using a Not Exists you'd need to make it correlated sub-query by adding e.employeeID = emp.employeeID so it is checking to see if that employee ...


2

Must be something like: select s1.id, s2.id from surveys s1, surveys s2 where (s2.start_date between s1.start_date and s1.end_date or s2.end_date between s1.start_date and s1.end_date) and s1.id!=s2.id; This fetches you the IDs where the START_DATE or END_DATE (of s2) is in the period of another START_DATE and END_DATE (s1).


2

this would be exact select min(id), max(id) from table group by start_da, end_date having count(*) > 1 start in select t1.*, t2* from table t1 join table t2 on t1.startdate between t2.startdate and t2.end_date


2

Apparently Oracle requires that an aggregate function stand alone (as opposed to being part of an expression) when there is no GROUP BY. So, since each operand in your case is the result of an aggregate function, both aggregations should be done in subqueries, as suggested by Balasz Papp. Or you can add GROUP BY () to your initial query: SELECT COUNT(T1....


1

Plan A: First get 1 row per user: SELECT user_id, GROUP_CONCAT(priv ORDER BY priv) AS privileges FROM tbl GROUP BY user_id Then look for dups SELECT GROUP_CONCAT(user_id) AS users, privileges FROM ( the select above ) x GROUP BY privileges HAVING COUNT(*) > 1; Plan B: This involves changing the data structure. By ...


1

Looks like you are sorting by close_date column in all the tables in DESC order then UNION. Instead of this approach SELECT UNION all the tables and, in the final result you can ORDER BY the Close Date:data:200 as DESC. The working query will be: SELECT T.* FROM ( SELECT sale.NAME AS "Name" ,sale.responsible AS "Responsible:data:200" ,...


1

You've got a few issues here. When using the UNION or UNION ALL operator, each query must return the same number of columns. The first query in your list has three columns in the SELECT but the queries that come later only have two. You can create a static value for these if you need to, something like this (using the second query as an example); SELECT ...


1

"last_active_user" sounds like ( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 ) count(comments.comment) sounds like ( SELECT COUNT(*) FROM abc_comments WHERE ... ) That is, get rid of the joins and replace the aggregate values by subqueries as above. JOIN explodes the number of rows, then You need a GROUP BY to get it back in ...



Only top voted, non community-wiki answers of a minimum length are eligible