In my understanding constraints should generally be checked at the end of a transaction. So, in a simple example where we have one table A with the only column P, which is its primary key, and a table B with the primary key P and the foreign key F on A.P, the following should work (on empty tables): However, …
BigQuery SQL: do SELECT aliases have priority over FROM clause column names when used in GROUP BY clause?
I have a simple query that has the ambiguity regarding col: According to the documentation, the query validator should raise an error: Ambiguity between a FROM clause column name and a SELECT list alias in GROUP BY: The query above is ambiguous and will produce an error because LastName in the GROUP BY clause…
Oracle SQL Extract Year from VARCHAR2
Please don’t roast me for not being able to figure this out. I’m a beginner with SQL and I’ve been trying to find a solution and make it work for at least a couple hours in total. I know the date fields should be stored as dates, not varchar2, but I have no control over that. I feel like I&#…
How to split lines in SQL table according to rules?
so I have SQL table with many lines. It looks like this (just a couple lines from the top): I need to disperse revenue according to project duration equally every 2 months starting first month. So desired result would look like this: What’s the baest way to achieve this? Answer You can use recursive cte…
Oracle SQL – Assign number to distinct tech_spec
In Oracle SQL, I’m trying to assign a unique number to each distinct tech_spec. Output: I’m trying to get assign each distinct tech_spec a number. I have been searching for a way to do this but can’t come up with anything. Output desired: Thanks in advance. Answer Use dense_rank(): You shoul…
search date from date time field query not working
I have below query it print following line by debug. in table the b_date is dateTime field. ( linked table from SQL server in Msaccess) in this table there is data exist with 1/30/2020 2:00:00 PM where fromtime is also 6. why query didn’t return any data? can msaccess cannot search date in datetime fiel…
Default values for each key in a where-clause
Given a table: I need to set 0 for every ID in where-clause along with IDs: Result What query can I use here? Upd1: let it be Postgres. Upd2: can I do it without joins? Answer You didn’t mention your DBMS, but the following is standard ANSI SQL: It’s possible without a join, but that doesn’t…
Replace Null with ‘-‘ string in Dynamic Pivot query
I have the following code where I’m trying to dynamically Pivot the table. Everything seems to be working the way I want except I want to replace the null with ‘-‘. Any ideas? Answer You need to put the coalesce into the dynamic column list – something like this: The isnull(@Columns1&#…
Calculating values from two tables where one has key in header and one has it in column values
I have a simple problem that I dont know how to solve in sql. I have two tables, cost : second table: income Now i want to get a result that substract for each party A, B, C the income-cost and finds the net value. I cannot compare the column header to column value. I am quite new to this,
Appending Name based on Min and Max datetime
I have Event_No, Events, Date Range in my table like below. I need to show Min datetime respective Event name with ‘IN'(a concatenation of (Event-‘IN’))and Max datetime respective Event with Out(a concatenation of (Event-‘Out’)). I need My Final Output like below Thanks Answer Th…