I have two tables on a Oracle SQL DB. Article (id [PK]) File (id [PK], article_id [FK], insertion_date [DATETIME]). I want to select files from articles that are more than 15 days old without adding new files. For example, if post ID 1 has a file added 10 days ago and another 20 days ago, they should not be returned
Tag: oracle
How to resolve subquery return more than one value in oracle
Could somebody please help me how to achieve the following? Table: My output should look like Thanks Inadvance. Answer We can use conditional aggregation with the help of ROW_NUMBER(): To create a view, use:
Speed up removal of duplicates in Oracle with indexing
How to remove duplicate entries from a large Oracle table (200M rows, 20 columns)? The below query from 2014 is slow. It took 2 minutes to delete 1 duplicate entry for one specific combination of columns (i.e. where col1 = 1 and .. col20 = ‘Z’). Any way to speed it up, e.g. with indexing? Answer Rather than using an
Find departments with no employees
We were given an assignment in class to provide at the minimum two different solutions to find departments, which don’t have any employees. As you can see from below I completed the task successfully. An additional solution is extra credit, which I like to get. Unfortunately, I can’t think of a third solution and was hoping someone can point me
Update statement with inner join on Oracle ORA-01427
ORA-01427: single-row subquery returns more than one row ORA-06512: at “SYS.DBMS_SQL”, line 1721 I need to do update with inner join in ORACLE Answer The script you provided above is going to update all rows in the table because you do not have a where clause. If you really want to update all rows in the table, then this should
Best way to get high value of partition from oracle database?
Best way to get high value of partition from oracle database? Answer “Best” is subjective but you can retrieve the high values by quering the data dictionary:
Inserting data into table with select
i`m trying to do something…. I have to insert some data in to a table but….. So here is where I end up… any suggestion… Answer You cannot refer to an alias in the SELECT or WHERE clauses of a sub-query where it is defined. Generate the data in a sub-query (or a sub-query factoring clause) and then refer to
Searching for a string in a column
I need to show the results from this column where Product_name column contains ‘Documentation’ or ‘documentation’ in a result. The query must return a result regardless of whether the word is in lowercase or uppercase https://i.stack.imgur.com/bjLuY.png I found this solution, any suggestions Answer
plsql parameterised cursor in which the parameter is user input
here’s my cursor statement and call like this The variable countryName is declared along with the cursors, and this query runs fine but no data is fetched. no idea what I’m missing here.. Answer With some sample data like below: A_TBL ID COL_A COL_B IE 01-NOV-22 1 UK 02-NOV-22 2 FR 03-NOV-22 3 IT 04-NOV-22 4 this code works… Did
Compare two VAT numbers
I`m trying to list only the vat numbers which look like DDDDDDDDDLLDDDD else i have to list NULL https://i.stack.imgur.com/4PYR8.png Pretty sure that I`m close but still missing something 🙁 Answer You can use the regular expression ^d{9}[A-Z]{2}d{4}$ to match the start-of-the-string, then any 9 digits, then any 2 upper-case letters, then any 4 digits and, finally, the end-of-the-string: If you