New answers tagged

0

Another solution, probably with the same performance: SELECT * FROM ( SELECT post_id, COUNT(IF(vote > 0, vote, NULL) AS voters, AVG(IF(vote > 0, vote, NULL) AS average FROM wp_imdb_rating GROUP BY post_id ) ORDER BY voters * average DESC LIMIT 0, 100 Note: Whenever ...


0

It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY. You can get around it by using the full expression instead of the alias: ORDER BY COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) * AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)


2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


1

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause): create or replace function func_get_Open_Days_2 ( ARG_SUBMITTED_BY_PERSON_ID in ...


11

There are two problems here: For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;. It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype). If this were a Scalar UDF, then the syntax of specifying only the ...


3

Explanation: You declare user_info as record. The manual: Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. Bold emphasis mine. ...


0

The answer to this question depends on whether we are talking about T-SQL objects or SQLCLR objects. There are different (and more) factors to consider when dealing with SQLCLR objects. In terms of T-SQL objects, both answers here bring up valid points. As described in @Aaron's answer, central DB can be quite handy, and Synonyms can be used to make it easy ...



Top 50 recent answers are included