I have a subject table that has subject_id column. In the table I have one row that has subject_id null other than that subject_id has a distinct value. I am looking for single query I can fetch the data on basis of subject_id. If there is no data found w.r.t x than it should return the row with subject_id =
Tag: oracle
Get XY coordinates from GEOM_SEGMENT_START_PT() output
Oracle 18c: How can I get the X & Y coordinates from GEOM_SEGMENT_START_PT()’s resulting LRS point? Note: The coordinates are stored in the SDO_ORDINATE_ARRAY( ), not the SDO_POINT. Desired output: Normally, I’d use something like a.shape.sdo_point.x. But I don’t think that applies here …
Year, Next Year, Quarter e Next Quarter based in a FISCAL YEAR
I need to bring the results of this SQL showing all the data of “MY FISCAL YEAR” that started in 1st june, and other SQL to showing all the data of the “NEXT FISCAL YEAR”, because I made it based on the database calendar. btw, the last fiscal year ended in 31 MAY The user will sent the…
OOTB Oracle IFERROR function to handle rows that cause errors in a query
In a related post, @MTO provided a custom function for determining what rows cause errors in a query: That custom function works well. In a query, if a function errors-out for a given row, then the custom function flags the row. That lets me find the problem rows and determine what the issue is, instead of th…
How to show data that’s not in a table. SQL ORACLE
I’ve a data base with two tables. I need a SQL query which show “The players who have not won any game”. I tried but I don’t know even how to begin. Thank you Answer You need all the rows from players that don’t have corresponding rows in wins. For this you need a left join, filt…
Oracle: WITH-clause querying two tables
Using an Oracle database, I have two tables: I am supposed to find – using the WITH-clause – all the employees who currently work in the same department where they started to work (hire_date = start_date and same department_id). I easily got the right result using a JOIN in the subquery: (Right) O…
Determine what rows are causing error in query
I have an Oracle 18c table that has 15,000 rows. As a test, I’m trying to run the following query on it: When I run that query in SQL Developer, it initially runs without errors, but that’s just because it’s only selecting the first 50 rows. If I try to run the query on all rows (via CTRL+EN…
Oracle SQL select greatest value of the result of two different select statements
I’m running select statements in a loop with a cursor to collect data from different tables; Quick (NOT WORKING) example; What I could like is just one insert statement which insert just the biggest value of boths select statements. I’ve tried to work with greatest function in my loop but i’…
Get id with same value
I have a table as below: May I know how can I get the studentNo who have exactly same course as student s001? Many thanks. Answer You want to count the number of courses for each student and then join the table to itself for different students taking the same course with the same total number of courses and t…
delete data only between may 6 and may 18, 2022
I have a table with CLIENT_CONTACT data, where there is call data from 2020 to today. How can I delete data only between May 6 and May 18, 2022, did I write correctly? Answer did I write correctly? Maybe…. in Oracle, a DATE is a binary data type consisting of 7 bytes representing century, year-of-centur…