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
Tag: sql
Sequelize: Avoid Race Condition When Updating Field with Unique Value?
I have a field in my postgres db called authorizedUserNumber. This field is set by default to 0, and does not auto-increment, because it is only asigned when a user has been fully onboarded. Okay, so let’s say a new user has been fully onboarded and I want to assign a unique number to the field authorizedUserNumber. In the event
Get cumulative distinct count of active ids(ids where deleted date is null as of/before the modified date)
I am facing a problem while getting the cumulative distinct count of resource ids as of different modified dates in vertica. If you see the below table I have resource id, modified date and deleted date and I want to calculate the count of distinct active resources as of all unique modified dates. A resource is considered active when deleted
How do I pivot values from rows into column names in Postgres?
I have a query in Postgres. It’s working well and returns exactly the rows I’m looking for. Here’s the query: This is what it returns: id status timestamp 1 started 2022-01-15 05:12:36 1 paused 2022-04-14 09:12:50 1 killed 2022-04-27 13:12:48 How can I pivot this table to return this exactly: id started paused killed 1 2022-01-15 05:12:36 2022-04-14 09:12:50 2022-04-27
How to create a trigger for this situation?
I have a problem inserting values into a Class table. I want to write a trigger to prevent happening “an instructor teaches in different class_Id at the same time”. How can I do this? This is the trigger which I’ve created: Answer Use inserted and JOIN to the Class table. Check for existence of rows in table that matches your
How to aggregate on multiple columns using SQL or spark SQL
I have following table: Expected output is: The aggregation computation involves 2 columns, is this supported in SQL? Answer In Spark SQL you can do it like this: or in one select: Higher-order aggregate function is used in this example. aggregate(expr, start, merge, finish) – Applies a binary operator to an initial state and all elements in the array, and
How to convert Base64 to a comma separated binary
I’m modifying the explaination so its clearer. I had this data in an image column within sql server. I used then used this query to get these results And using online converters i can get it to a binary/commma separated list. From here i need to convert each 8bit word into a decimal. The new list doesn’t need to show
Adding a condition to desplay a list of values in Oracle Apex
I need help with putting a condition on a List Of Values in Oracle apex. So I have 2 tables: and The list of values displays v.BROJ_VEZ but returns v.ID_VEZ. The LOV is displayed in the form for the table BRODICE. I want the LOV to display only the v.BROJ_VEZ (returning v.ID_VEZ) where v.MAX_DULJINA > b.DULJINA_BRODICE. How do I do
SQL query for EXCEPT or NOT IN
I’m new to SQL and I am trying to write a query on PostgreSQL to find the cust_ID and customer name of each customer at the bank who only has a loan at the bank, and no account. The database schema is: The results should have only these 3 customers: My two attempts for this query are: 1) Where my
Simplifying SQL query
I’m using the Bixi public dataset found at https://www.bixi.com/en/open-data and have been asked to find “the average number of trips a day for each year-month combination in the dataset”. Here’s an example of the table from which I’m querying: id start_date start_station_code end_date end_station_code duration_sec is_member 85843 2016-04-15 00:00:00 6315 2016-04-15 00:05:00 6315 349 1 85844 2016-04-15 17:16:00 6315 2016-04-15