I have SDO_GEOMETRY objects in Oracle 18c: In a query, I want to select the SDO_GEOMETRY’s sdo_point attribute as literal text (for concatenation purposes). Example: (fails) I don’t know how to convert that object attribute to text. I’ve tried using the SDO_UTIL.TO_WKTGEOMETRY() function. Bu…
Tag: sql
ROW type/constructor in BigQuery
Does BigQuery have the concept of a ROW, for example, similar to MySQL or Postgres or Oracle or Snowflake? I know it sort of implicitly uses it when doing an INSERT … VALUES (…) , for example: Each of the values would be implicitly be a ROW type of the Inventory table, but is this construction all…
Find records that do not exist in 2nd table, and also the values mapped against them from first
Hi so this question has been asked before, but I am not only just trying to extract the records that do not just exist in the first table, but also the mapping from the first table where they don’t? Eg. Assuming I will join using Month and Year– Table 1: Month Year 3 2021 4 2021 5 2021 6 2023
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 …
Oracle using MOD command to set Y/N on INSERT
I’m trying to generate some sample data for the following table see below. I have functions that can generate a random DATE and timestamp within a date range, which is working fine. I want to use the MOD command to populate the active field to ‘Y’ OR ‘N’. I want to set every row …
Extract all records from a JSON column, using JSON type
I have a couple tables (see reproducible code at the bottom): tbl1_have tbl2_have I wish to extract all json columns without providing explicit data type conversion for each columns since in my use case the names and amounts of nested attributes vary. The expected output is the same for both cases: tbl_want w…
Need help by populating a PrimeReact Tree
I’m trying to populate a PrimeReact Tree with data from sqlite3 database, but it doesnt work. Here is my SQL-Query: I get following JSON-Code: This is OK but i figured out, that the children are not rendered, because there are quotes around the square brackets at the children path – how can i fix …
SQL cummulative sum by group
I am struggling in finding a solution to create a cumulative sum by group in SQL. I have a table which have different events. What I want to accomplish is to sum the duration time of an event. What I need is a cumulative sum that gives me the total duration of Event1 as column besides ‘Event1_Start̵…
SQL Get count of each item within a SQL query
I have this table ID PhoneID PhoneName Active Status 1 1 a 1 1 2 1 b 0 2 3 2 c 1 1 4 2 d 1 1 5 2 e 0 3 6 3 f 1 1 7 3 g 1 1 8 3 h 1 1 9 4 i 0 4 10 4 j 1 1 And
I’m trying to join 2 tables that don’t have same columns and using case select. Is this possible?
table 1 code value 0 None 1 R 2 W 4 C 8 D 16 U 32 Uown Table 2 number 0 1 2 3 4 5 8 12 13 16 20 25 26 27 32 43 44 45 60 61 62 63 64 68 70 expected output is number output 0 0 1 1 2 2 3 2,1 4