I’m using utf8_encode to make sure that the input from my users is transforming the special characters to a character where my MYSQL can do a search on. (My database is set in latin1_swedish.ci). When …
Is there a more efficient query to check multiple conditions in a self-join?
The question is to find a state in which all three modes of transportation have taken place in. So, in a table listing different trip details – you want to return a state that has a TripID of a car …
Split text into multiline chunks based on fixed character length using SQL
I am looking for SQL that takes a string of text (coming in as up to 3 lines, but usually 1) and breaks it into at most 3 lines with a max of 30 characters per line. (The background is I’m trying to insert text into address line fields and sometimes the incoming values are longer than 30 characters and
Not able to resolve “Numeric Value ‘-‘ is not recognized” error in Snowflake SQL Query
I’m working on converting the sql query below over to Snowflake, and got the error “Numeric Value ‘-‘ is not recognized” but it did not have a line number. I assume it’s in reference to the various “-1” instances throughout the query but there’s no line re…
UPDATE TABLE SQL Missing expression error
Error: 00000 – “missing expression” Why doesn’t this work? Answer Remove the table prefixes and the extra *. Careful with updates without a where clause, as they will update all the records from your table.
Why does Hive throw me an error while using Order by date?
I am trying to write a query In hive and I am seeing the following error. “Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Primitve …
How to express “either the single resulting record or NULL”, without an inner-query LIMIT?
Consider the following query: Assuming col1 is non-NULLable, this will yield either true or false – or possibly NULL when table1 is empty; But now suppose I have: (and assume col2 is also non-NULLable for simplicity.) If there is a unique col2 value for the lowest col1 value, or the table is empty, then…
Change null to empty array in databricks SQL?
I have a value in a JSON column that is sometimes all null in an Azure Databricks table. The full process to get to JSON_TABLE is: read parquet, infer schema of JSON column, convert the column from JSON string to deeply nested structure, explode any arrays within. I am working in SQL with python-defined UDFs …
SAS computing multiple new variables from one row
I have a dataset as listed below: and I want to calculate 3 new variables (ERR_CODE, ERR_DETAIL, ERR_ID) according to behavior of certain columns. If V1 is greater than 4 then ERR_CODE = A and ERR_DETAIL = “Out of range” and ERR_ID = [ID]_A If V2 is greater than 4 then ERR_CODE = B and ERR_DETAIL …
Fill in Missing Values in Query
Say we have this dataset: Two students are enrolled in two different courses and attendance is taken for each student. I am interested in finding the attendance_dates for the students for which either one of them or both of them missed the respective subject. For the subject and the attendance_dates the stude…