I have a table that links users. Consider the following: The user_links table can have 0 rows for a particular user_id, given the user doesn’t have linked users, so a select statement can return either a row or NULL. The approach with left join user_links ul on ul.user_id = contracts.user_id OR ul.linke…
Add minutes to an Oracle date
I am trying to add minutes to my sql but no error and no data. This is my sql And I want to add 5 minutes to (MO.INSERT_TIME,’DD-MM-YYYY HH24:MI:SS’) So I want my sql show me after 5 minutes for this column (MO.INSERT_TIME,’DD-MM-YYYY HH24:MI:SS’) I wrote sql like that And after 5 minu…
Remove varchar data with non-numeric characters, then convert to numeric data
I have a column where most values are numeric but a few contain letters or special characters: Records 90000 5200 unknown approximately 25 areas TBC 5000 (approx) I would like to remove any non-numeric entries and replace them with null like this: Records 90000 5200 NULL NULL NULL NULL I tried to remove non-n…
How to get max() function working without grouping? Databricks SQL
I have a table which requires filtering based on the dates. The goal is to select the latest date when there is multiple data points like in the example above. The account 3000 in the dataframe occurs under two Groups but the up-to-date and correct result should only reflect the group X because it was ingeste…
Converting weight to grams in a SELECT statement
I have a Table called parts which looks like this: Now I want to select the PNO, color,weight and city for “non-Paris” parts with a weight greater than ten pounds. This is not that difficult: Now to the part I don’t understand. How can I add a column to my result which shows the weight conve…
Big query/sql to maintain the same data from next row onwards in the same column itself, got based on some condition in the current row
I am working on one of the usecase where I need to implement one of the logic to get the relevant data . eg: If you will see the image : The column = new_TARIFF_ALLOWANCE_DATA is calculated as case when gift_given!=0 or gift_received!=0 then TARIFF_ALLOWANCE_DATA – gift_given + gift_received else remain…
Update Customer table from selected Ids and assign one of three distinct values to each
I have a fairly complex query which results in a list of Customer Ids where they have not ordered within a defined period, have no Rep assigned to them, and are business customers rather than private. This simply returns a sequential list of Ids which match the BIGINT primary keys of the matching customers, l…
Update multiple rows from one SQL UPDATE INNER JOIN with different values
The scenario is a set of tables that contain data related to markets and goods produced at those markets. In the basic example below, you have Ice and this produces water. I have a SQL query which works well and is performant (5ms~). It calculates the stock of Ice required to produce X amount of Water (they a…
Oracle SQL ignores condition in WHERE clause
I have this simple part of SQL query I have 4 parameters, invoice_num, start_date, end_date and Payee, I want it to be just like a filter, If one of the conditions above is present then it must be applied, if not present then it must be ignored, but it just doesn’t work as expected, if I pass invoice_nu…
Cross Join, Compare Values, and Select Closest Match – More Efficient Way?
I have two tables with 2 columns. I cross join and subtract the values. I then find the row_number ordered by the subtraction and choose where row = 1. I’m finding the t2.id that has the closest val to t1.id These tables are quite large. Is the row_number function doing a lot of extra unneeded work by o…