I have a table of road condition ratings (roads are rated from 1-20; 20 being good). db<>fiddle In a query, for each road, I want to generate rows to fill in the gaps between the years. For a given road, starting at the first row (the earliest inspection), there should be consecutive rows for each year …
Tag: oracle18c
Why does CROSS JOIN LATERAL break up array’s SDO_GEOMETRY object into individual attributes?
Oracle 18c: I have mapping software that has a limitation where it can only handle a single geometry column per table. If there are multiple geometry columns in a given table, then it will throw an error. So, I want to find a way to add an additional geometry column to a table, but store it as a datatype that
Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?
Oracle 18c: In a related question, we determined: The syntax of extracting collection elements by index is not supported in SQL. So a query that uses this syntax will fail: (shape).sdo_ordinates(1), Source: Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL? However, I have a query that is similar…
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…
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…
Convert VARRAY to text (for concatenation)
I have a query that outputs a VARRAY: In SQL Developer, the VARRAY gets output as text: Question: Similar to what SQL Developer does, is there a way to convert the VARRAY to text using SQL — so that I can concatenate the value in a string? Example: (fails) Just a heads up: DB<>FIDDLE doesn’t seem …
Put a json inside another json on Oracle 19
i have the next problem. I need create a json from a table that contain another json inside in one of the columns Having this: i try with this query: But the result is not correct: Must be something like: There is a way to get the correct result? Regards. Answer after see examples and see the resuslts. I know
Group Records based on predefined date range in SQL (Oracle)
Is it possible to group records based on a predefined date range differences (e.g. 30 days) based on the start_date of a row and the end_date of the previous row for non-consecutive dates? I want to take the min(start_date) and max(end_date) of each group. I tried the lead and lag function with partition by i…
SQL in Oracle HR Schema
I have made a query in Oracle HR schema to see the following information: The city where the department is located The total number of employees in the department However, the query cannot be …
FOR loop in Oracle SQL or Apply SQL to multiple Oracle tables
My SQL is a bit rusty, so I don’t know whether the following is even possible: I have multiple tables t_a, t_b, t_c with the same column layout and I want to apply the same operation to them, namely output some aggregation into another table. For a table t_x this would look like this: I now want to exec…