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 all the way to
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 (different datatype) that succeeds when I use seemingly similar syntax:
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 the entire query erroring-out.
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+END), then it
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 to output VARRAYS correctly.
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 in Oracle but couldn’t come up
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 execute something