Teradata SQL Asst query - Error in where clause

Question

I have included one 'where' clause which I was hoping to return output with reference to current_date but I am getting an error. Can someone please take a look at it and let me know what exactly I am doing wrong here. Snap attached here.

                      AND CASE WHEN (SELECT
                                        CASE TD_Day_of_Week (CURRENT_DATE)
                                        WHEN 1 THEN 'MON'
                                        WHEN 2 THEN 'TUE'
                                        WHEN 3 THEN 'WED'
                                        WHEN 4 THEN 'THU'
                                        WHEN 5 THEN 'FRI'
                                        WHEN 6 THEN 'SAT'
                                        WHEN 7 THEN 'SUN'
                                        ELSE 'Unknown'
                                        END) <> 'MON' THEN tpd."RELATIVE_GLOBAL_DAY" = -1
                                        ELSE tpd."RELATIVE_GLOBAL_DAY" = -3 
                                        END

Tim Biegeleisen · Accepted Answer · 2024-02-06 08:02:22Z

0

It appears that the intention here is to restrict the TD_Day_of_Week day of the week to not being Monday. If so, then just directly add that assertion, sans the bulky CASE expression:

WHERE (TD_Day_of_Week (CURRENT_DATE) <> 1 AND tpd."RELATIVE_GLOBAL_DAY" = -1) OR
      tpd."RELATIVE_GLOBAL_DAY" = -3

edited Feb 6, 2024 at 8:02

answered Feb 6, 2024 at 7:00

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Virendra Over a year ago

Thanks @Tim Here is what I am trying to do; When user checks data on Tue, they should see the data until until Mon (Prev day). But when the user does it on Mon, they should get data for Fri (Prev weeks). The db is defined as such that tpd."RELATIVE_GLOBAL_DAY" is '0' for today. If I wanted to go back one day, its then '-1'. I need to keep SELECT statement part and its output like 'Mon' because the same I can ref in the db

Tim Biegeleisen Over a year ago

I have rewritten your stated logic in a valid way.

Virendra Over a year ago

I tried above, its returns blank. That's incorrect output. I modified a little putting same in the 'case when' clause but looks like I am doing something wrong. Can you please take a look ----->> and case when TD_Day_of_Week (CURRENT_DATE) <> 1 then tpd."RELATIVE_GLOBAL_DAY" = -1 else tpd."RELATIVE_GLOBAL_DAY" = -3 end

Tim Biegeleisen Over a year ago

Answer has been updated per your requirements.

Virendra Over a year ago

Hey @Tim, Thanks much. Sorry, it looks like I am not defining the problem correctly here. This here ----> (TD_Day_of_Week (CURRENT_DATE) <> 1 AND tpd."RELATIVE_GLOBAL_DAY" = -1) ----> We cannot put in 'AND'. The ---> "RELATIVE_GLOBAL_DAY" = -1 ---> meant that we wanted to call yesterdays data. Eg. Today is Tue so condition has to ---> if TD_Day_of_Week (CURRENT_DATE) <> 1 then get data of Mon ---. which we can do by using db in td ods_vw_ac_db.TIME_PERIOD_DATE as tpd and col name as tpd."RELATIVE_GLOBAL_DAY" = -1

|

Collectives™ on Stack Overflow

Teradata SQL Asst query - Error in where clause

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related