I have many tables with a date in their name. For example MY_TABLE_2021_06_01, MY_TABLE_2021_06_02, etc. I’m trying to extract the date from the table name and see if any tables are more than an year old. This is my code: The above code works fine if I don’t include the where clause where table_da…
Tag: oracle
single-row subquery returns more than one row in oracle in HR schema
and I found an error like the following : Answer Looks like there are “many” employees whose job_id = ‘IT_PROG’. In that case, subquery returns “many rows” and your query results in an error. It depends on what you want to do in such a case; for example, you might want to r…
SQL Query OneToMany Filtering with child entity but returning parent
Hey I need some help with an SQL Query I just can’t figure out. I got these two Tables with an OneToMany/ManyToOne relation: Now I got this select query to get me all Books that have been written before the current date: (I’m doing this in Java JPA via the Query Annotation) In this case I am retur…
How to transpose row to columns in Oracle as shown below using Unpivot?
Source Table Cost Category Cost Category Type Q1-2020 Q2-2020 Q3-2020 Q4-2020 Employee Impacted Period Cost 10 20 0 4000 Achieved Result Cost Category Cost Category Type Quarter Year Value Employee Impacted Period Cost Q1-2020 10 Employee Impacted Period Cost Q2-2020 20 Employee Impacted Period Cost Q3-2020 0…
Does Oracle always resolve CTE with clauses even if they are not used in the result set
If I have an Oracle SQL query like this: Will the DBMS actually do the work of resolving/running query2, or does Oracle know that query2 is not required by the final output, so the work of that CTE/with should be skipped? Oracle version is 12c Enterprise. Answer I was going going to say “it’s up t…
Write a query to display the total month wise sales amount received in the past 1 year
Table StructureWrite a query to display the total month wise sales amount received in the past 1 year . Display details like sales month, total sales amount. Give an alias_name as MONTH for retrieved sales month, TURN_OVER for sales amount. Sort the result by amount in descending order. (Hint: Use table Sales…
HOW TO CHECK CONDITIONS FOR PREVIOUS MONTH
My table is like I am trying to get customer_ids where they didn’t do any transactions previous month and made transactions with at least 2 transaction_type at this_month.While doing this I am converting rundate to MM-YYYY format. What I’ve done for getting previous month and this month on MM-YYY …
Oracle bug when using JSON_ARRAYAGG with CASE expression to emulate standard SQL FILTER
I’m using Oracle 18c (reproduces in 19c as well) and I’d like to emulate the standard SQL FILTER clause in the JSON_ARRAYAGG() aggregate function to filter out values from the aggregation. However, this produces an error: The error being: ORA-40590: invalid format dbfiddle here. What’s causi…
Oracle 11g SQL Query – Specify which duplicates to Exclude from Select Statement
I have a question about excluding duplicate values with my Oracle SQL query. Using select distinct does not work in this case. I only want to select one row for each event#, the row where Mobile = Yes. Actual Result of Current Query: Desired Result: In this ideal result, one of the rows for Event# 1 has not b…
Combine lead and lag function without overlap
I have this table: I need to get an output like this: I’ve tried a normal lead window function, but that would only show the first ‘Creation’ and not that last ‘Pending’ that I need. Answer Your table has N records (3 in the example provided) when query must return N + 1 (4); let…