i want to extract a value from a json column. The schema is (- first level, — second level): Currently i extract a value this way: Is there a better way to handle the task? Answer Assuming that: event_params is an array of struct type. user_id is a unique key in each event_params Following code style wo…
Tag: sql
Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function
So I do have following SQL select Key is of the type VARCHAR(12). I wonder why the hell the numbers are sorted after the letters. Every other system including UTF-8 always begins with numbers. Answer So a solution you can do is to take advantage of the EBCDIC character order. Special characters are sorted bef…
Get all employee detail from employees table whose “First_name” start with any single character between ‘a-p’
is there any new way to rewrite the SQL Query to find the first_name starting from A to P Answer Use regexp_like: The regex breakdown: ^ means “start of text” [a-p] means “any character in the range à to p inclusive” The i flag means “ignore case”
Filtering in count function – Postgresql
I have the following query which gives me the amount of referrals users have. However, I would like to only count the referral if the referred user has activated the premium plan. How could this be achieved? Dbfiddle here. I am using PostgreSQL version 14. Answer You can try to use FILTER clause or condition …
SQL Update Table 1 when Table 2 contains a specific value
I want to update lasstock of table s_articles to 1 when laststock is 0 and is either in categoryID 242, 243 or 244 in table s_articles_categories_ro. I came up with this SQL, which doesn’t work. I have these 2 tables: s_articles id laststock 1 0 2 1 3 0 4 0 s_articles_categories_ro id articleID category…
MySQL Foreign Key ERROR. Constraint key error
I can’t add a foreign key to an existing table. The error is as follows Answer I’ve written the schema below which is working. I advise against using the same name student_id for the constraint as already used for the column. I would prefer fk_student_id to avoid risk of an ambiguity or conflict a…
Create new column and fill all rows with the same field values
I have a database that rates outfits. I want to take the value of predicted rating when predicted month = base month and fill it into rows with the same base month, shirt, pants, and shoes in a new column called actual rating, as shown below. predicted month base month shirt pants shoes predicted rating 4 0 1…
MariaDB Case statement
I have the following SQL-Code in a Mariadb-Database: (1) I need the following result: If Matrix=’AW’ => I need the field “FaktorAW” else => I need the field “FaktorGW” is it possible to formulate Statement (1) with a “case statement”? Answer Of course, thi…
DB2 (mainframe DB2) – Select sql – CASE WHEN
My data is like this OUTPUT: My requirement: If there are more than one CODE, then display CODE as ‘MULTI’, But if there is one CODE, then display that CODE itself (e.g. ‘1A’) I want my output like below (if the data as above) CODE AMOUNT MULTI 2.50 If my data is like this: then I want…
Unknown column sql error while using jooq
Query: Error: Database: MYSQL, Database Name: ‘users’, JOOQ Version: 3.16.6 Answer Correlating derived tables isn’t supported in MySQL 5.7. Support has been added only in MySQL 8.0.14: https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html jOOQ currently can’t work around this lim…