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
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
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
This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don’t have too many nullable columns to add up here… SELECT ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col3 IS NULL … Read more
It was meant for readability. Before, SQL was written in plain-text editors (no syntax/code highlighting) and keywords needed to be differentiated for better readability and maintenance. SELECT column_name FROM table_name WHERE column_name = column_value vs select column_name from table_name where column_name = column_value See the difference? The first word in each line told the reader … Read more
Generally all modifications can cause a deadlock and selects will not (get to that later). So No you cannot ignore these. You can somewhat ignore select depending on your database and settings but the others will give you deadlocks. You don’t even need multiple tables. The best way to create a deadlock is to do … Read more