Query issue

General
Fan

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. 

Tags (2)
3 REPLIES
Fan

Re: Query issue

Need some help ???

Fan

Re: Query issue

HELP WITH THE QUERY  -TERADATA  :(((

Senior Apprentice

Re: Query issue

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