I work in an Oracle instance that has hundreds of schemas and multiple developers. We have a development instance where developers can integrate their work before test or production. We want to have source control for all the DDL run in this integrated development database. Currently this is done through a pr…
Tag: sql
How to return a value from a function if no value is found
I’m attempting to return 0.0 if the following function does not return anything: I’ve tried searching for it and found that COALESCE does not work. Does anyone have any ideas how to solve this? Table structure: Example data: Answer Here is the script I used. As you can see I run PostgreSQL 9.4.1. …
Do I need a where clause in a conditional UPDATE?
We imported a lot of data from another table. Now I’m trying to correct some of them. I didn’t use ELSE because many rows have valid country. My question is to know whether I need to the WHERE clause to filter the rows that will be affected? The reason I’m asking this question is that, IR…
How to use between clause in select statement in codeigneiter
I’m trying to get data from database by taking one data row from one table and use those values as where condition. and then i tried pass json string to controller class. but i’m unable to reach this. how should i write this code This is code i used to get data. Answer Use this for between
Query with rownum got slow
I have got a table table with 21 millions records of which 20 millions meet the criterion col1= ‘text’. I then started iteratively to set the value of col2 to a value unequal to NULL. After I have mutated 10 million records, the following query got slow, that was fast in the beginning: I noticed t…
SQL query to search for room availability
The follow tables I have are: CUSTOMERS (CustomerID, firstname, surname etc) ROOMS (RoomID, RoomType, Description) BOOKING (BookingID, CustomerID, Arrival Date, Departure Date, number of people, …
Create MySQL Tables with Ansible
I’m using ansible to manage a small mail server using ubuntu. I wanted to use ansible to create a database which I can do and also create users for the database(s) which I can do also. But I’m not …
jsonb query with nested objects in an array
I’m using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The table contains two rows with the following json data: First row: second row: How does the query have to lo…
Sqlite get max id not working (?)
Im using this: But my query is empty. I have also tried this (This one works): But obviusly my query only contains the max_id key. What am I doing wrong with the first one? Answer You need to add another level of select for the MAX, like this: A better approach would be to order by id in descending order,
SQL to retrieve parent-child relationship in parent-child order, from a self referencing table
I use the following query to retrieve the parent-child relationship data, from a table which is self referencing to the parent. — go down the hierarchy and get the childs WITH ChildLocations(…