more than one value was returned by subquery

General
MMM
Enthusiast

more than one value was returned by subquery

Here is the query code that causes an error "more than one value was returned by subquery'. What is the best way to re-write the query so it can run efficiently and also without this error message to capture the mismatch either emp_dt or eff dt.  Any suggestion. I do really appreciate your help

SELECT

D.MIN_DERV_EFF_BEG_DT,

D. EMP_DT,

D.APPL_EMP_NBR,

D.EMP_NBR,

D.APPL_HISTORY_NBR,

D.APPL_GRP_NBR,

D.CREAT_DT FROM

(SELECT

DISTINCT B.EMP_DT,

A.DERV_EFF_BEG_DT AS MIN_DERV_EFF_BEG_DT,

A.APPL_EMP_NBR,

A.EMP_NBR,

A.APPL_HISTORY_NBR,

A.APPL_GRP_NBR,

A.CREAT_DT

 FROM   EMP_MTL A, EMP_CURR_MTL B

 WHERE

 B.BIRTH_DT > ( SELECT MIN(C.DERV_EFF_BEG_DT)

                            FROM EMP_MTL C

                            WHERE C.EMP_PI_JOIN_KEY = B.EMP_PI_JOIN_KEY

                            AND C.DERV_EFF_END_DT > CURRENT_DATE - 1095

                            GROUP BY C.EMP_NBR)

AND A.EMP_PI_JOIN_KEY  = B.EMP_PI_JOIN_KEY

AND A.DERV_EFF_END_DT > CURRENT_DATE - 1095

) D

JOIN

(

SELECT MIN(E.DERV_EFF_BEG_DT) AS MIN_DERV_EFF_BEG_DT , E.EMP_NBR

FROM  EMP_MTL E

WHERE E.DERV_EFF_END_DT > CURRENT_DATE - 1095

GROUP BY E.EMP_NBR

) F

ON D.EMP_NBR = F.EMP_NBR

AND D.MIN_DERV_EFF_BEG_DT= F.MIN_DERV_EFF_BEG_DT

ORDER BY D.EMP_NBR

;

3 REPLIES
Teradata Employee

Re: more than one value was returned by subquery

It seems that the same EMP_NBR may have multiple EMP_PI_JOIN_KEY values. Why not GROUP BY C.EMP_PI_JOIN_KEY in the scalar subquery?

MMM
Enthusiast

Re: more than one value was returned by subquery

HI. That would be working fine but  Should I re-write the query to make it working perfectly? Any suggestion would be appreciated.

MMM
Enthusiast

Re: more than one value was returned by subquery

I am all set and closed this case