CODEWITHSUNDEEP
sqlteradata
tj919
tj919

Reputation: 41

SELECT Failed. 3707: Syntax error, expected something like an 'END' keyword between ')' and ')'

Am hoping someone can assist me with my CASE statement. I am receiving the following error message: SELECT Failed. 3707: Syntax error, expected something like an 'END' keyword between ')' and ')'.

select distinct
D.orl_rec_nbr, D.Terr, D.AE_Name, D.Segment,  D.Segment_Nbr, D.cust_nm, D.cntry_enti_nbr,
D.cust_acct_nbr, D.spcl_notation_desc, D.tot_projd_adnr_amt, D.Last_Phase, D.oprty_crte_dt, D.ramp_dt, D.first_shpmt_dt, 
D.Date_Phase_Last_Changed, D.Date_Phase_Modified, D.Historical_Phases, D.Age_in_Phase, D.Total_Pushes, D.Oppty_Age, D.Close_flg

from (select distinct F.oprty_key_nbr, F.first_shpmt_dt, CAST (F.rec_crte_tmstp AS date format 'MM/DD/YYYY') as Date_Phase_Modified, 
        A.phase_dt AS Date_Phase_Last_Changed, A.oprty_crte_dt, A.orl_rec_nbr, A.orl_stage_phase_nm AS Last_Phase, F.orl_stage_phase_nm AS Historical_Phases,
        TRIM (B.prim_terr_div_nbr) || '-' || TRIM (B.prim_terr_grp_nbr) || '-' || TRIM (B.prim_terr_sales_org_nbr) || '-' || TRIM (B.prim_terr_rgn_nbr)  || '-' || TRIM (B.prim_terr_area_nbr) || '-' || TRIM (B.prim_terr_dist_nbr) || '-' || TRIM (B.prim_terr_nbr)  as Terr,
        A.spcl_notation_desc, B.emp_nbr, B.user_title_desc, TRIM (B.first_nm) ||' '|| TRIM (B.last_nm) as AE_Name, A.cust_acct_nbr, G.cust_nm, E.cntry_enti_nbr, A.ramp_dt,
        B.prim_terr_seg_desc as Segment, B.prim_terr_seg_nbr as Segment_Nbr, A.tot_projd_adnr_amt, A.Close_flg,
        
        case when Historical_Phases <> 'Closed - Won' and 
                                Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc) is null then 
                                    ((Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr order by Date_Phase_Modified desc) * -1) else
                                    Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc)) else 0 end as Age_in_Phase,
        
        case when F.first_shpmt_dt - lag (F.first_shpmt_dt) over (partition by F.oprty_key_nbr order by F.first_shpmt_dt asc) >1 then 1 ELSE 0 end AS Total_Pushes,

        case when close_flg = 'Y' then Date_Phase_Last_Changed - oprty_crte_dt ELSE current_date - oprty_crte_dt end as Oppty_Age

        from isell_prod_view_db.sfdc_opportunity A
        left join  isell_prod_view_db.sfdc_user_profile B on A.oprty_owner_key_nbr = B.user_key_nbr
        left join  isell_prod_view_db.sfdc_opportunity_item C ON A.oprty_key_nbr = C.oprty_key_nbr and A.delt_flg = C.delt_flg and (A.oprty_outcm_resn_desc not in ('Deleted') or A.oprty_outcm_resn_desc is null) 
        left join ui_ish_prod_db.employee_assignment D on B.emp_nbr=D.emp_nbr and current_date between D.eff_dt and D.exp_dt
        left join isell_prod_view_db.sfdc_customer_info G  on A.cust_info_key_nbr = G.cust_info_key_nbr  
        left join ui_ish_prod_db.se_business_alignment E on A.cust_acct_nbr = E.cust_acct_nbr AND CURRENT_DATE BETWEEN E.eff_dt AND E.exp_dt AND prim_cvge_flg = 'Y' AND align_type_cd = 'P' AND sale_terr_globl_rgn_cd = 'CAN'
        left join  isell_prod_view_db.sfdc_opportunity_lifecycle F ON A.oprty_key_nbr = F.oprty_key_nbr AND A.delt_flg = F.delt_flg 
    
        where A.oprty_type_desc = 'Opportunity'
        
        Group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21) D

Group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
Order by 1 desc, 16 asc

This is the related resulting excerpt I am receiving:

date_Phase_modified historical phase Age in Phase What I would like to see
12/10/2020 Presenting 8
12/18/2020 Closed ? i would like to get of this null value(replace with 0)

I think this line where i multiply by *-1 is causing the issue:

((Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr order by Date_Phase_Modified desc) * -1) else

however if i don't * -1 then the value I receive is -8.

Upvotes: 0

Views: 11118

Answers (2)

Johnny Fitz
Johnny Fitz

Reputation: 542

I would suggest gradually building up your query using intermediate values and chaining successive with clauses. I use this method to free myself of distracting items and test the intermediate results.

With Opportunity as 
(
    Select * from isell_prod_view_db.sfdc_opportunity 
    where oprty_outcm_resn_desc not in ('Deleted') or oprty_outcm_resn_desc is null and oprty_type_desc = 'Opportunity'
)
,UserProfileTerritoryDesc
(
    select *, -- other fields of interest
        TRIM (B.prim_terr_div_nbr) || '-' || TRIM (B.prim_terr_grp_nbr) || '-' || TRIM (B.prim_terr_sales_org_nbr)
         || '-' || TRIM (B.prim_terr_rgn_nbr)  || '-' || TRIM (B.prim_terr_area_nbr) || '-' || TRIM (B.prim_terr_dist_nbr)
          || '-' || TRIM (B.prim_terr_nbr)  as Terr,

        A.spcl_notation_desc, B.emp_nbr, B.user_title_desc, TRIM (B.first_nm) ||' '|| TRIM (B.last_nm) as AE_Name
    from sfdc_user_profile
)
,HistoricalPhases as 
(
    select *, --other fields of interest
        Date_Phase_Modified,lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc) as 
        Age_in_Phase_Partition
     from sfdc_opportunity_lifecycle
)
--test selects on each of above to see intermediate values, partcularly fields like Age_in_Phase_Partition
, AllResultsTogether
(
    select 
        --...
        Terr,
        case when Age_in_Phase_Partition then -1*Age_in_Phase_Partition else Age_in_Phase_Partition end as Age_in_Phase_Final
    from HistoricalPhases A 
    join Opportunity on 
    --...
    join UserProfileTerritoryDesc
    --...
)
--test results with partitioned and final values.

Upvotes: 1

sam
sam

Reputation: 1985

Your case-when expression seems messed up. If you could explain the logic of that case statement, then it would help to write the correct logic. But the below should help you to figure out the error that you are seeing.

Please try with below:

case when Historical_Phases <> 'Closed - Won' and Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc) is null 
            then ((Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr order by Date_Phase_Modified desc) * -1)) 
     when Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc) is not null 
            then Date_Phase_Modified - lag (Date_Phase_Modified) over (partition by F.oprty_key_nbr, Historical_Phases order by Date_Phase_Modified asc) 
            else 0 end as Age_in_Phase,

Upvotes: 1

Related Questions