Table functions (functions defined as returns table or returns setof) need to be used in the from clause like a table. So you need to use: select * from get_avg_prices(…); Only scalar functions (functions which return only a single value, e.g. a number) should be put into the select list.
Bulk insert You can modify the bulk insert of three columns by @Ketema: INSERT INTO “table” (col1, col2, col3) VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33); It becomes: INSERT INTO “table” (col1, col2, col3) VALUES (unnest(array[11,21,31]), unnest(array[12,22,32]), unnest(array[13,23,33])) Replacing the values with placeholders: INSERT INTO “table” (col1, col2, col3) VALUES … Read more
Comparing COALESCE and ISNULL The ISNULL function and the COALESCE expression have a similar purpose but can behave differently. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times. Data type determination of the resulting expression is different. ISNULL uses … Read more
You can do the following: Inscription.includes(item1: { item2: :banner }) The relations names item1, item2 and banner need to match the names given to each relation. If you want to set a where statement on this query, you can do: scope = Inscription.includes(item1: { item2: :banner }) scope = scope.where(banner: { name: “MOTD: Hello World!” … Read more
You should be storing the codes in a separate table, since you have a many to many relationship. If you separate them, then you will easily be able to check. It would be possible to do in the type of system you have now, but would require text searching of the columns, with multiple searches … Read more
SELECT contentID FROM tags WHERE tagID in (334, 338, 342) GROUP BY contentID HAVING COUNT(DISTINCT tagID) = 3 –In general SELECT contentID FROM tags WHERE tagID in (…) –taglist GROUP BY contentID HAVING COUNT(DISTINCT tagID) = … –tagcount
This is a bit tricky. You may want to use variables, such as in the following example: SELECT ( CASE cust_type WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := cust_type END ) + 1 AS rank, cust_type, cust_name, revenue FROM sales, (SELECT @curRow := 0, @curType := ”) … Read more
Think of a recursive CTE as of an endless UNION ALL: WITH rows AS ( SELECT * FROM mytable WHERE anchor_condition ), rows2 AS ( SELECT * FROM set_operation(mytable, rows) ), rows3 AS ( SELECT * FROM set_operation(mytable, rows2) ), … SELECT * FROM rows UNION ALL SELECT * FROM rows2 UNION ALL SELECT * … Read more
Given a very minor tweak (replace double quotes with single and add the VALUES keyword), your proposed syntax is valid Standard SQL-92 syntax i.e. SELECT * FROM mytable WHERE (group_id, group_type) IN ( VALUES (‘1234-567’, 2), (‘4321-765’, 3), (‘1111-222′, 5) ); Sadly, MSFT have not added it to SQL Server and consider it an ‘unplanned’ … Read more
Try this: select o.name,c.name from sys.columns c inner join sys.objects o on c.object_id=o.object_id order by o.name,c.column_id With resulting column names this would be: select o.name as [Table], c.name as [Column] from sys.columns c inner join sys.objects o on c.object_id=o.object_id –where c.name=”column you want to find” order by o.name,c.name Or for more detail: SELECT s.name as … Read more