One Line of Code Breaks Otherwise Working Query

Question

Everything in this query works just fine until I try to add the commented out portion (noted by '--') back into the query and run it. When I attempt to add the commented out WHERE clause back into the query body, it breaks the query. I have researched and experimented with adding the commented out line in different locations and in different ways, but nothing I have tried has worked so far.

    SELECT 

    Count(DISTINCT a.acct_id),

    CASE
    WHEN b.user_seg = 'MTR' AND b.user_func = 'Sales' THEN 'MTR Sales'
    WHEN b.user_seg = 'JMT' AND b.user_func = 'Sales' THEN 'JMT Sales'
    THEN 'Partner Account Executive'
    WHEN b.user_func IN ('Account Manager','Associate Account Manager','Outreach','Executive') THEN 
    'Customer Success'
    WHEN b.user_func IN ('Relationship Manager') THEN 'Account Executive'
    END AS sales_group
    FROM qrs_access_views.mxp_sf_acct a
    JOIN qrs_access_views.mxp_sf_user b
    ON a.acc_crtd_by_id = b.user_id
    AND a.is_sf_acct_del_y_n= 'n'
    AND a.owner_id= '22573PAA3'
    AND a.acct_mgr_user_id='22573PAA3'
    AND b.user_seg <> '#'
    --WHERE a.acc_crtd_date BETWEEN '2020-01-01' AND '2020-03-31'
    LEFT JOIN grs_access_views.mxp_sf_user f
    ON b.mgr_id = f.user_id
    GROUP BY 2

When I run the query as above, I get a nice little grouped table kicked back to me by Teradata, but it is for all values for all time. (I just want to see the Q1 2020 numbers.)

When I add the commented out line back into the query I get some variation of the following error:

SELECT Failed. [3706] Syntax error: expected something between a string or a Unicode character literal and the 'LEFT' keyword.

Any help or assistance is greatly appreciated.

I'm surprised you get anything back. That should generate a syntax error. — Gordon Linoff, Apr 22 '20 at 19:32
Yes, you are correct. I've just updated the original question to reflect that. — DKL44, Apr 22 '20 at 19:38

score 0 · Accepted Answer · answered Apr 22 '20 at 19:29

0

try this, Instead of adding it to where clause use AND.

  AND b.user_seg <> '#'
    AND a.acc_crtd_date BETWEEN '2020-01-01' AND '2020-03-31'

answered Apr 22 '20 at 19:29

ch2019

1,085
1
4
12

Or just put the WHERE clause after the very last ON clause and prior to the GROUP BY, according to standard SQL rules. – Fred Apr 22 '20 at 20:31

One Line of Code Breaks Otherwise Working Query

1 Answers1