673,727 questions
Score of 0
0 answers
126 views
ERROR 4016 (Internal error) on LEFT JOIN with NOT IN in ON clause
Update: Per comments, the original CASE NULL WHEN t0.c1 ... always evaluates to ELSE 6. Repro below uses 6 NOT IN (SELECT t0.c1 FROM t0) instead; PK vs no-PK behavior unchanged.
I'm seeing this on ...
- reputation score 11
Score of 1
0 answers
92 views
Ignite 3.1 SQL Join query performance issue
Environment:
Apache Ignite 3.1.0 (Native)
Two colocated tables with the same distribution zone.
Table details - PRODUCT_MAPPING:
CREATE TABLE PRODUCT_MAPPING
(
COMPANY_ID VARCHAR(10) ...
- reputation score 11
Score of -3
1 answer
135 views
Calculate dates of two different records in case expression [closed]
When the ContactType is TRINT5, I need to get that ContactDate up to 3 days from the ContactDate of ContactType TRINT2 to show 'Met' in my CASE expression. If more than 3 days, then 'Not Met'.
SELECT ...
- reputation score 37
Advice
0
votes
12
replies
125
views
Extract XML data from forms into tables - presence of namespaces in XML
I have a situation, where I am importing data stored in XML from a SQL Server table, into a temp table (#), also in SQL Server.
The sample form data is as below:
<ABCDE xmlns:i="http://www.w3....
- reputation score 465
Tooling
0
votes
3
replies
162
views
Replace values in SQL
How can I replace numerical values with NULL in SQL
You’re a junior data analyst working for a local news station and your team uses BigQuery to analyze weather trends. The station’s meteorologist ...
- reputation score 1
Score of -3
0 answers
132 views
Why does LEFT JOIN on the time column keep different rows than I expected? [closed]
I am using the Apache IoTDB 2.0.8 table model for an inspection page that joins temperature rows from a main device table with status rows from another table. I expected every left-side row to remain ...
- reputation score 31
Score of -1
0 answers
105 views
Why are some hot devices missing after hourly aggregation and filtering in table model? [closed]
My data is in Apache IoTDB 2.0.8 table model. A dashboard query groups temperature by hour and then keeps only devices above an alarm threshold. The raw rows contain the values I expect, but after ...
- reputation score 19
Score of 4
1 answer
182 views
Why are some device groups filtered out after aggregating a 10-minute subquery?
I have an alarm summary running on Apache IoTDB 2.0.8 table model. The SQL first builds 10-minute temperature buckets in a subquery, then the outer query counts how many buckets crossed the threshold. ...
- reputation score 88
Best practices
0
votes
8
replies
147
views
How to replace zero values with NULL in Google BigQuery without syntax errors?
My assignment is:
I am a junior data analyst working with a 2023 weather dataset in Google BigQuery. I need to analyze the relationship between wind speed and visibility. However, the raw data has ...
- reputation score 1
Score of 2
1 answer
144 views
JSON path query for full-text searching a value with language in another key
I have difficulty crafting a JSON path query that can be tuned using a Full-Text Search index effectively for what seems like a not too difficult use case. This would be for Postgres 14, but info on ...
- reputation score 2595
Score of 1
1 answer
153 views
Return the next OFF event after each ON event
I'm querying motion sensor data stored in GridDB. Each row represents a sensor state change.
Sample data:
dt
message
2021-06-30 10:00:00
ON
2021-06-30 10:00:13
OFF
2021-06-30 10:04:57
ON
2021-06-30 10:...
- reputation score 13
Best practices
0
votes
4
replies
63
views
Split overlapping date ranges and prioritize one record type over another
I have a table containing effective-dated records. For a given PID, I need to determine the correct CODE for any date based on a priority rule between PCD values.
Sample data:
PID
PCD
STDT
CODE
ETDT
...
- reputation score 163
Advice
0
votes
3
replies
86
views
Replicate SAS Merge happening in loop into Pyspark
I have an use case where I need to replicate a sas behaviour into pyspark. In Sas, the Merge between two datasets are happening and its not 1*1 key merge. Its a m*n key merge involving multiple keys. ...
- reputation score 331
Score of 1
1 answer
206 views
Query that shows if a member meets defined metrics from the metrics table
I am trying to figure out how to write a query that can show me if a member meets defined metrics from our metrics table. There is a table that holds member survey answers and we have business ...
- reputation score 125
Advice
0
votes
7
replies
139
views
Best way to start learning SQL for database design and analysis?
I am new to SQL and I'm currently studying database design and analysis. I understand the basic concepts of databases, but I would like to build a strong foundation in SQL before moving on to more ...
- reputation score 1
