Follow up to this question. Say that on postgres, I have a table TABLE1 containing the columns id (integer), name (string): And attempt to run the following queries: On sqlfiddle, the former yields a result, whilst the latter fails with the message: On postgres 13.3 which I have installed locally, however, ne…
JSON array to a single row in SQL table
I have a table in PostgreSQL database. With some data in it: receipt column contains lists of 3-element lists. What I’m trying to achieve is write an SQL query that returns table in the form The best I’ve got so far is but I’m stuck on unnesting the inner arrays. I tried using jsonb_to_recor…
Need to add double quotes to SQL SERVER string values
I have integer values that are being passed from a parameter that needed to be input as a string and padded so that they are 7 digits. This will then be passed into another query. This codes sets @control_num as (‘0005675, 0045464, 0002323’). I need this to be passed as (“0005675”, …
I am trying to return Col1 as a value for a list, IF Col2 = NULL
I am trying to return all Col1 values IF the respective Col2 value = NULL I have thought of IF Col2 = NULL Return Col2 END IF, But I am unsure of how to format this, because when I run it I get errors saying invalid relational operator. Thank you for your time. SELECT Col1 FROM mytable WHERE IF NULL
Conversion failed when converting the varchar to numeric and displaying numeric values only
got an error converting data type varchar to numeric. when tried to convert numeric to varchar but that is also an error Conversion failed when converting the varchar value ‘7.350000’ to data type int. Answer I strongly recommend case instead of iif(), but it is the same problem. The expression re…
Adding x work days onto a date in SQL Server?
I’m a bit confused if there is a simple way to do this. I have a field called receipt_date in my data table and I wish to add 10 working days to this (with bank holidays). I’m not sure if there is any sort of query I could use to join onto this table from my original to calculate 10
Mysql duplicate results of query relation with 2 other table , IN operator
I have 3 main table : products, stores and categories product relate many-to-many with stores via table product_store product relate many-to-many with categories via table product_category How can i query all products, belongs a store ( with store_id : 1 ) and belongs one or many category ( with category_id i…
Extract data from a historical table keeps only data with changes
I have a table like this and here is the log table So as you understood, my main table contains data from a source every day. But keeps only those with changes. Thus if everyday if 100k records comming normally, but very few (<100) are really has some changes. Thus my table tracks all the records using the…
MYSQL joining two table slow
I have 2 main table in MYSQL DB. I will call it entityA and entityB. There are some logic that user will start to create a entityA and then convert it to entityB but some time they will not create entityA and only create entityB Therefore, I have created a middle table which stored entityAId and entityBId. en…
Equivalent of jsonb_path_query_array in Postgresql-11
Given a column containing the above data, I use select jsonb_path_query_array(column, ‘$.key’) to get the output [KA, KB] However this doesn’t work in Postgres-11. Are there any alternatives for the same ? Answer Yes. This yields a Postgres array. Use jsonb_agg instead of array_agg if you ne…