Tag Info

New answers tagged

6

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


1

Normally you should never do so because other revisions may be deleted after compacting the database. You should include all necessary fields to recompose(reprint) the invoice in the invoice document, like: product description, product price, total, and so on. This is the same if you would store invoices in a rdbms.


0

Note that VĂ©race's answer is specific to the MySQL product, and not general for SQL Views. For instance, SQL Server allows the definition of Indexed Views which can have both clustered and non-clustered indices. These are of course just aggregate tables such as one could create and maintain oneself, but in this case the DBMS performs all that bookkeeping ...


0

You can't have a PRIMARY KEY on a VIEW. See here, here . You have to put PRIMARY KEYs and INDEXes on the underlying tables. A VIEW is a stored query - you can't have KEYs on a query.


0

Ok, I found the solution, I have to use the reduce function for that, like: function(keys, values, rereduce){ var result = null; var orders = [] for(var i =0; i< values.length; i++){ if(values[i].type=='customer') result = values[i]; else{ orders.push(values[i]) } } result.orders = orders; return result; }


0

If you wanted them randonly side by side you could use an adhoc ID value. If there are different numbers in each table then some will appear at the end without a partner. with cte1 as ( select a1.name, ROW_NUMBER() OVER (ORDER BY a1.name) as ID from a1 ), cte2 as ( select c1.theyear, ROW_NUMBER() OVER (ORDER by c1.theyear) as ID from c1 ) select cte1.name, ...


2

The effect of ORDER BY is only mandatory on the same query level. Your view acts as derived table and if you just SELECT * FROM inv_groups_vw, the order is preserved. However, when adding another WHERE clause to the outer SELECT, you would have to add ORDER BY to the outer query as well. In your particular case, the order of rows is not guaranteed, even ...


1

with cte1(Name, Year) as ( select Name, null as year from tablea), cte2(Name, Year) as (select NULL as Name, year from tableb) Select Name, Year from CTE1 UNION ALL select Name, Year from CTE2 This should work, didn't read the question properly last answer :)



Top 50 recent answers are included