I want to export all my datas as insert statement. In Sql Developer, the command “set sqlformat insert” work very well… but when I want to use it on sqlplus, I have this error message : SP2-0158 unknown SET option “sqlformat” This command work when it’s use on a script but …
Tag: sql
finding missing dates in a time interval (SQL)
I am a bit of a noob with SQL, so I was searching for some bit of code that might help me find missing date values for a time interval when I stumbled upon this code. Link to the code It works really well for my problem but I am not able to understand how it increments the date. I
Get difference from top ranked item
I’m using the following query to rank items, but I need it to also show the difference between the item and the top ranked item (The top ranked item will show the difference between it and the 2nd ranked item.), like so: How do I create the DiffTop column? Answer You can use window functions for this as…
How do I check if a column have more than two items and if it have than do the avg() on it in SQL?
I have 4 tables The user and item from buys and rates are foreign keys. I can`t figure out how to put a condition to take out the items which have only one rating and do the average only on the ones who have 2 or more ratings. This is the question in the exercises (For each item that has
temporal joins in hierarchical query
I want to join various nodes of a tree, making sure the the returned root-to-leaf path is temporally valid. The tricky part is that the data source is dated with validity from-to dates. ID NVALUE VFROM VTO 1 A 2021-01-01 2021-01-31 1 B 2021-02-01 2021-02-28 2 C 2021-01-01 2021-02-28 3 D 2021-01-01 2021-01-31 …
Oracle 10g sql query
I have some data like this ↓ T_ID T_PERIOD T_COUNT T_SUM T_UPDATE_COUNT 1 2013-2014 3436 20118043 0 2 2014-2015 4298 27101356 0 3 2015-…
Order By in Union Nested selects
I have two really long selects that are both nested How can I order these (both queries combined into one table results) by NewestMessage which is a type of datetime? Answer I would slightly alter your query to: I am basically replacing your union of tuples to a subquery on a union query, using an ORDER BY wi…
How to filter the record based on two columns in sql
i have a table with 2 columns and i wanted to filter the records where column1 in (Hierarchy,Single) and column2 in (‘Relate’, ‘NoRelate’). but not return the record when Column1 = Single and Column2 =…
Regexp search SQL query fields
I have a repository of SQL queries and I want to understand which queries use certain tables or fields. Let’s say I want to understand what queries use the email field, how can I write it? Example SQL …
SQL Remove all leading zeros
My join returns null because I am not able to remove the leading zeros. Basically, I am trying to replace the pol.Policy_Number with src.Policy_Number for a large number of policies. Is there a way I can remove the leading 0s in the ON statement for src.Policy_Number? I think Padding is needed, but I am new t…