I have 10000 jsons with different ids each has 10000 names. How to flatten nested arrays by merging values usin SQL Server? Jsons can be read in any language, I’m looking for any SQL dialect that can transform the data as I’m using spark connectors. I use many SQL dialects including not limiting S…
Tag: sql
Error while running very long SQL query with UNION ALL operator in Vertica
This is a sample of subquery used in Vertica Query which is a string generated from the application. There are some cases when this subquery becomes huge as the number of rows with UNION ALL increases. Vertica supports this query to some extent. But when there is let’s say 3000 + UNION ALL operator used…
Compare table column with a range of values and take the position of that value from another table
I got stuck with this problem, please help if possible. Suppose we have two tables, Table A and Table B. Table A looks like this: And Table B looks like this: In table B we have around 23 values for each date, now we need to match val1 of table A to val2 of tableB for each date and return
Create a Month function
I would like to run a query that runs monthly but it will check daily if the timestamp from the records is from the previous month. If yes then it will run the query and append to the previous monthly data. So, I want to create a simple function to return TRUE if the RecordTimestamp is from the previous month…
SQL good practices: should I use associative tables in this example?
I want to link two tables: a “company” table and a “branches” table. I find two ways to do it: Method 1: Method 2: In practice, I think that both ways work the same, but professionally speaking, I think Method 2, using associative tables, would be the proper way to do it following SQL …
Conversion failed when converting the varchar value ‘ AND ID =’ to data type int
I was looking how I can parameterize table names and so I found dynamic sql queries. I finally got the proc saved, but when I execute it errors out with “Conversion failed when converting the varchar value ‘ AND ID =’ to data type int.” I have no idea what is going wrong when I try to …
create rows from columns in a apache spark dataset
I’m trying from a dataset to create a row from existing columns. Here is my case: InputDataset accountid payingaccountid billedaccountid startdate enddate 0011t00000MY1U3AAL 0011t00000MY1U3XXX 0011t00000ZZ1U3AAL 2020-06-10 00:00:00.000000 NULL And I would like to have sometthing like this accountid star…
Translating Oracle Date Functions to BigQuery
I’m having trouble translating these Oracle date functions to BigQuery because BigQuery doesn’t allow such user-friendly additions to dates/timestamps. Any help is appreciated. Below are the two date functions in Oracle and my attempted (failed) versions in BigQuery: Oracle: BigQuery attempt: Answ…
Pyspark: How to flatten nested arrays by merging values in spark
I have 10000 jsons with different ids each has 10000 names. How to flatten nested arrays by merging values by int or str in pyspark? EDIT: I have added column name_10000_xvz to explain better data structure. I have updated Notes, Input df, required output df and input json files as well. Notes: Input datafram…
Why does this inner join of two tables create duplicate rows
I have the following tables: Users Conversations Group_Members I need to select all the conversations where a user with a specific ID takes part in. Users and Group_Members are in a many-to-many relationship. Why does the following query create duplicate rows on the last select, as seen in this image? User.Id…