All Forums General
BI_guy 3 posts Joined 06/16
30 Jun 2016
Query issue

Query -

SELECT 
        PMORTG.BDE_TFCLSTEP_PT.LOAN_NUM, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_SETUP_DT, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_SEQ_NUM, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_ACTL_CMPLN_DT, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_ACTL_CMPLN_NULL, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_CRTCL_STEP_CD, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_NUM_OF_FLOAT_DAYS, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_PREDECESSOR_STEP_CD, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_SCHDD_CMPLN_DT, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_SCHDD_CMPLN_NULL, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_CD, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_CMNT_CD, 
        PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_COST, 
        PMORTG.BDE_TFCLSTEP_PT.CLNT_NUM, 
        PMORTG.BDE_TFCLSTEP_PT.SNAP_END_DATE, 
        PMORTG.BDE_TFCLSTEP_PT.MORT_PUBLN_ID, 
        PMORTG.BDE_TFCLSTEP_PT.SNAP_DT 
FROM 
        PMORTG.BDE_TFCLSTEP_PT 
WHERE   (snap_dt = ADD_MONTHS(CURRENT_DATE,0)-EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE,0)) 
         and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_ACTL_CMPLN_DT is not null 
         and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_CD ='078' 
​         QUALIFY ROW_NUMBER() OVER (PARTITION BY LOAN_NUM ORDER BY FCLSR_SETUP_DT DESC)=1 
        )

ERROR-
SELECT Failed. 3707:  Syntax error, expected something like ')' between a string or a Unicode character literal and the 'QUALIFY' keyword. 

BI_guy 3 posts Joined 06/16
30 Jun 2016

Need some help ???

BI_guy 3 posts Joined 06/16
30 Jun 2016

HELP WITH THE QUERY  -TERADATA  :(((

dnoeth 4628 posts Joined 11/04
30 Jun 2016

QUALIFY must be moved out of the WHERE:

-- move the closing paren
WHERE   (snap_dt = ADD_MONTHS(CURRENT_DATE,0)-EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE,0)) 
         and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_ACTL_CMPLN_DT is not null
         and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_CD ='078'
        )
​QUALIFY ROW_NUMBER() OVER (PARTITION BY LOAN_NUM ORDER BY FCLSR_SETUP_DT DESC)=1 

-- or remove the parens 

WHERE   snap_dt = ADD_MONTHS(CURRENT_DATE,0)-EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE,0)) 
        and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_ACTL_CMPLN_DT is not null
        and PMORTG.BDE_TFCLSTEP_PT.FCLSR_STEP_CD ='078'
​QUALIFY ROW_NUMBER() OVER (PARTITION BY LOAN_NUM ORDER BY FCLSR_SETUP_DT DESC)=1 

 

Dieter

You must sign in to leave a comment.