You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression). UPDATE tab1 SET budgpost_gr1= CASE WHEN (budgpost in (‘1001′,’1012′,’50055’)) THEN ‘BP_GR_A’ WHEN (budgpost in (‘5′,’10’,’98’,’0′)) THEN ‘BP_GR_B’ WHEN (budgpost in … Read more
This is likely not a problem of indexing, but one of data design. Normalization, to be precise. The fact that you need to query distinct values of a field, and even willing to add an index, is a strong indicator that the field should be normalized into a separate table with a (small) join key. … Read more
You can do it like this without loops SELECT p.date, COALESCE(a.value, 0) value, p.grp_no FROM ( SELECT grp_no, date FROM ( SELECT grp_no, MIN(date) min_date, MAX(date) max_date FROM tableA GROUP BY grp_no ) q CROSS JOIN tableb b WHERE b.date BETWEEN q.min_date AND q.max_date ) p LEFT JOIN TableA a ON p.grp_no = a.grp_no AND … Read more
Figured this out, here’s a function that does it: CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$ DECLARE characters TEXT := ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789’; bytes BYTEA := gen_random_bytes(size); l INT := length(characters); i INT := 0; output TEXT := ”; BEGIN WHILE i < size LOOP output := output || substr(characters, get_byte(bytes, i) % l … Read more
They are speaking the same thing and it has to do with tuples (relational algebra) or rows (layman’s term). When it says high-cardinality are possible values of particular attribute (or field) that are unique and therefore the number of rows or tuples are higher: Example: StudentID Lastname Firstname Gender 101 Smith John M 102 Jones … Read more
USING (fieldname) is a shorthand way of saying ON table1.fieldname = table2.fieldname. SQL doesn’t define the ‘order’ in which JOINS are done because it is not the nature of the language. Obviously an order has to be specified in the statement, but an INNER JOIN can be considered commutative: you can list them in any … Read more
WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd, ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn FROM mytable ) SELECT MIN([date]), MAX([date]), crew AS name, dayType FROM q GROUP BY crew, dayType, rnd – rn This article may be of interest to you: Things … Read more
The query you have will give the error Msg 8156, Level 16, State 1, Line 59 The column ‘AnotherKey’ was specified multiple times for ‘tmpTable’. That is because you are using * in the using clause and AnotherKey is part of both table2 and table3. Specify the columns you need. Also there is no use … Read more
Entity Framework has no support for triggers, although you can certainly manually execute a statement that would create a trigger, but you would need to do this after the table was created (if using migrations). You can use the technique specified by Ladislav in EF 4.1 code-first adding a trigger to a table Take note … Read more
You can take the aggregations out into a CTE (common table expression): with minima as (select t.id, t.type, min(value) min_value from table2 t where t.type in (1,2,3,4) group by t.id, t.type) select a.id, a.name, (select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1, (select min_value from minima where minima.id = … Read more