one of the columns in my database contains an imported JSON file. Format of JSON: There are hundreds of objects with taskId (3271012,3261201,…) how can i parse this tasks from one column to multiple rows? expected output: Answer First you can get all the “data” items as key-value pairs with …
Tag: sql
Generate Monthly Aggregated Values in SQlite
I am new in SQLite and i have been working on SQL Query for quite some time. Lets say we have database table say tbl_expense with the following table structure. tbl_expense Assume we have the following data stored in the table. Expected Output Assuming we have expense_date as timestamp, How can i write a SQL …
How can I count the number of items in inventory available on any given day?
I have a table that contains records for items that are available. Each of the records contains a field for the date it was added to the inventory, the date it was removed from inventory (otherwise NULL) and a unique serial number (separate from the primary key). There are also other fields giving metadata ab…
Unable to Convert to Date
I am trying to return just the date from a datetime field When I run the Select Claus with just the first line, it work as expected. But when I add the second line, I receive From keyword not found error. ORA-00923: FROM keyword not found where expected Answer In Oracle, DATE is a reserved word and you cannot…
Using a subquery in a case statement in a where clause
I want to select different results (from a temp table) based on what a certain parameter is set to in the query. I want to exclude certain inventory SKUs if a particular warehouse is selected. I am getting this error, unfortunately. Msg 512, Level 16, State 1, Line 310 Subquery returned more than 1 value. Thi…
Displaying Unique Temperature Values for the US
I am looking to do something similar to what I did in R (below) in SQL: The output of this code looks like this: So far in SQL I have managed to output a single state using the following code: The output looks like this: However, I am unable to get the unique avgTemp for each state. When I take
USE ELSE 0 doesn’t work as expected in SQL
I have the following SQL query: This doesn’t work as expected (it will count more than expected), but when I remove the ELSE 0 in aggregate function, it works as expected: Could someone explain me why having the ELSE 0 will make it count more data than it should be? *It will also work if I use ELSE NULL…
MySQL: LEFT JOIN TWO tables with preference for specific rows
Offshoot of this question: MySQL: LEFT JOIN table with preference for specific rows I have a Contacts table, a PhoneNumbers table, and a ContactPhoneNumber table. Additionally, I have a ContactAddress and Address tables. A Contact can haveMany PhoneNumbers via the ContactPhoneNumber pivot table and can also h…
Oracle INSERT with a CASE statement
I have the following table declaration and I’m trying to use a case statement to INSERT some rows and I’m getting the following error ORA-00976: Specified pseudocolumn or operator not allowed here. Can someone please let me know how to rectify the INSERT code. Thanks in advance to all who answer. …
Dates returned as columns in SQL Select
My user will submit a FromDate and a ToDate. What I want to happen is to select the dates that fall in between these dates, which I have accomplished with the script below. The dates will by dynamic. This returns that dates as rows. What I am looking for is a way to make these dates return as the columns