I have a oracle DB table called CRS.CRS_FILES, with a column called FILE_DATA -inside that CLOB column is a large XML string. Here is the first few lines of it: it is set up with the following Xpath I want to query: I am not sure what I am doing – I know that sqlQuery when passing SQL queries there
Tag: sql
How to incrementally count the number of repeated instances
I want to incrementally count the number of repeated instances in a table, so if I have a table like this: Using the column “name” as reference, the output would be The DBMS implementation is MySQL, version 5.6 Answer In MySQL 5.x, where window functions are not available, one option uses a correl…
How do I sum until it meets a criteria?
I have the following data set for an user. I want to merge the False flag rows to the latest True row. initial set: user input_date starting_balance deposit flag user1 2020-08-20 100 …
MySQL Select all from Table 1 where Foreign key not exists or is not equal to specified key
Hello I can’t figure out how to write this select statement. I have the following schema: table 1 1:n table 2 n:1 table 3 So I got a n:m between table 1 and 3 what I want to do now is get all entries in table 1 that either don’t have an relation to table 3 via table 2 or
Query with offset returns overlapping data sets
Initial attempts at getting a very simple pagination, using fetch n rows and then a subsequent call with offset, gives overlapping entries in Oracle. I was expecting the following to give me two unique sets of results. 1-100 and then 101-200 of the results that would have been returned if the first line had b…
How can I select data from my database in time intervals?
Suppose I have the following table in a database: ID | Date | Data | —————————— 0 |2020-01-01 08:00 | 123 1 |2020-01-01 08:01 | 456 2 |2020-01-01 08:02 | 789 3 |…
Giving a specific number to fields in my Db
I want to give specific unique numbers to the following field in my db. The column looks like: August 2020 August 2020 August 2020 August 2020 September 2020 September 2020 September 2020 October 2020 …
dbt macro – using *args/**kwargs
Occasionally, our tables need to append multiple columns together to make a unique key. This can be done by doing something like: select *, col1 || “_” || col2 as unique_key from …
I need to use `OR` in SQL but in other way because i can’t use `OR`
I write a lot of information which might be not useful for you so if you want a simple question this is my question : (i want to know is there anyway to use OR on SQL in other ways like this : WHERE …
Order string_agg over partition | BigQuery
Here’s the piece of my query in question: string_agg(product_type, “|” order by product_type desc) over(partition by email, processed_date) as full_basket I’m having issues with ordering my …