We’re complementing null value to all zero value like ’00’ on Redshift. Sometimes, I found coalesce function can’t work as we expected. If we use case and len, it can work fine as follows; So far, I have no idea why coalesce can’t work. Any advice would be appreciated. Thank you.…
optionally enclosed not working, commas breaking upload
I have this table: Here is my load statement: In spend I’ll have a decimal value with a comma like: 1193.72. The spend field gets uploaded as 1. The impressions field that follows is correct and unaffected. Example record: 1/19/2021 | 1,193.72 | 92780 Output: 1/19/2021 | 1 | 92780 I was under the impres…
SQL subquery in SELECT clause
I’m trying to find admin activity within the last 30 days. The accounts table stores the user data (username, password, etc.) At the end of each day, if a user had logged in, it will create a new entry in the player_history table with their updated data. This is so we can track progress over time. accou…
while Convert sql query to json getting wrong output
I am getting wrong output Output should be: Answer The problem is with your nested loops. You actually want to parallely iterate both lists. The lists themselves are quite complex due to the nesting of lists and tuples. That’s why you may loose track here. You could try simplifying the lists/objects if …
Using NVL with JOIN
I want to display 2 values in table but only Student 301 is displayed. Student 102 doesn’t received any grades. I joined two tables. Here’s my query. Thanks for your help Answer Presumably, both students are in enrollment_info. If so, you can use: Note the use of LEFT JOIN. The table where you wan…
Find minimum in one column based on value frrom another column after joinng 2 tables
I’ve searched but can’t find an answer to this one. Any help would be appreciated. I’m running a select statement joining to columns which returns some results but I then want to return the value from one column based on the minimum from another column. Heres what I wrote to get the first se…
SQL CASE with CASE
I have a CASE stmt within CASE and the code is something like below: I need to achieve below condition But below code is not working. Can anyone correct this code? Here If mgmt.id IS NOT NULL then status_fl should be set as ‘Y’, and If mgmt.id IS NULL then we need to check the condition If staff.N…
SQL selecting date by month and year but select another month/year rows by column relation
I have the following example in my database: workshop_class_id workshop_session_id workshop_class_date 209 29 2021-06-20 210 29 2021-06-21 213 31 2021-06-30 211 30 2021-06-30 214 31 2021-07-01 212 30 2021-07-03 219 33 2021-07-12 I would like to select by month and year, for example June 2021, but if the rows …
SQL row value expression with null failing in where-in query
I am struggling to understand why this where-in query is failing when there are nulls in the row value expression. The query is not returning a single record from the below table. I expected each row value expression to match exactly one record. I appears the null spoils the fun and I don’t understand w…
Define enum as the column names of table PostgreSQL
Is there a way to define an enum using a table’s column names? I have a users table defined as follows: And then I want to define a required_profile_fields column in another table to be an enum containing only the column names in that public.users table: Is there a way that I can define that profile_fie…