So I have a disabled foreign key (Manager_ID in the table Employees) and I wanted to enable it, but I get this error: ORA-02298: cannot validate (my_username.EMP_MANAGER_FK) – parent keys not found And this is the code: Here’s how the table was created: Answer There are values in the column that a…
Tag: sql
How to define minimum and maximum value as range for a postgresql field?
I would like to compress the values that a field can take in my PostgreSQL database, but could not find a way to do it in a query while creating the table. For instance, let’s say I have a table which is named Books, and it contains some essential information about my books. The fields might be like: bo…
How to insert values into the following table by incrementing the date column by 1 day for the next 23 days?
I am using SQL Server 2014. I have a table names T1 (extract shown below): I need to update table T1 for period 2020-10-13 to 2020-11-04 with the following logic: All rows to be appended to the table will have the same values as those for ReviewDate on 2020-10-12 except the ReviewDate which will increment by …
How to force Postgres to calculate with numeric precision
I have a .net core application using dapper with Postgresql. Windows, PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit When i do simple calculation in the select-statement, then i get different results if i do not provide fractions: 1258 1208 What’s the reason and what’s the best way to …
Order count per ID and calculate time between orders in BigQuery
I am working with customer purchase data and am trying to write a query in Google BigQuery that sorts all purchases by date and adds a purchase/order count per customer (order_count). Also, I would like to calculate a time-delay (in days) between the orders of one customer (purchase_latency). My query current…
Why do double quotes influence result in compareing datetime?
Why do these two results dont return the same results? Can anyone explain why？ (addtime is datetime type.) Answer This does not do what you want: Unquoted 2020-10-10 is an arithmetic operation, which returns integer value 2000. You are then asking MySQL to compare it with a date, so it tries to convert the in…
Create a PostgreSQL view from a unique list of exploded data
When I make this request: I get results like this: Each line has values separated with / (space, slash, space). How to create a view with a list of unique values with count ? Answer You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate: Demo on DB Fiddle: val | cnt :&…
mysql – Search for a key within inconsistent json structure
I know of the functions such as: JSON_SEARCH() and JSON_EXTRACT() etc. This issue is that I am searching for a key in a json string that is not standardized. for example: and the results could be something like this: so in this example I want to get john doe with the acctNum of 123. but, also, the location of…
how to count all rows are approved in sql?
I have this line data, where it also shows the status: approved, in progress, not yet started. I need to count how many lines are ALL APROVED based on the line colors. the data is similar to this: the query should show that there is only color (which is green) that all the status are approved, because red sti…
Dynamic columns from two tables
i need some help. I would like to combine two tables in sql with dynamical columns. Here my thoughts: Table one: Example Table jrincidents Table two: Example Table jrusers reporting like i don’t know how to dynamically add the steplabels from table jrincidents as columns of jrusers if where processname …