Data that I wanted to download was about 45,000,000 rows and 38 columns in Oracle 11g database. When I tried to download the data with chunksize=50000 in python, DatabaseError: ORA-01555: …
Tag: sql
List of values as table
I’m looking for a smarter way to have a list of values as a table in Oracle. What I do nowadays is select ‘value1’ as val from dual union select ‘value2’ from dual What I’m hoping for is some …
Find missing rows between three related tables
I have three tables: Person Dates Absence I need to find all of the person_id’s in the Person table and the date’s from the Dates table who have not booked any absence matching the following criteria: Dates.date_type = ‘PUBLIC_HOLIDAY’ Absence.absence_type = ‘HOLIDAY’ Basically, I need to find the people and the dates which are public holidays they have not booked
how to comparison LAG function in mysql
I got complicated problem in mysql. I have some table on mydatabase. sample docs(this is only simple sample, actual data are so many table that I have to join) table “merchant” table “transaction” I want to know the information of merchants transaction daily “before” and “after” to comparison like this below I tried with this query but that query didn’t
Entity Framework – Left outer join on multiple columns with OR condition
Is it possible to write lambda left join on multiple columns that would generate OR condition instead of AND? This will generate following query I would like to get the same query but instead of AND condition I want it to be OR: Is there a way to achieve this? EDIT: version – EF 6.2.0 Answer LINQ join operators (Join,
How to transpose column to row and duplicate the original column header?
I have a table for example as below, the alphabet is header and the numbers are its records a b c d e f ——————— 3 4 5 6 2 3 5 3 2 9 8 7 I want to …
Dynamic bit-based flattening of multiple rows by pivoting into additional columns
I have data that looks like this: And I would like it to look like this: I think a dynamic conditional aggregation is required. Any help would be appreciated. Answer Here is a sample implementation of dynamic conditional aggregation: this is the result: Now you can add a value (for example id = 4 and value = ‘e’) replacing the
Using BOTH scalar values and JSON objects as JSON values
I have a local table variable that I’m trying to populate with JSON key-value pairs. Sometimes the values are themselves JSON strings An example of what this ends up looking like: After populating it, I attempt to build it all up into a single JSON string, like so: The problem with this is that it turns the JSON values into
UPDATE command in a JSON
I’m trying to update a column in my table, but that column stores a JSON Object, but for that I need to do a where with a JSON as well. This is how the data is stored in my RG column: { “Id”:”…
SQL group by ID for multiple IDs using case when statements
I started with a list of patients with multiple codes (at multiple times of year) and need to split the patients into groups based on if they have a code or combo of codes and those that don’t qualify …