|
HI,
I have used the following query
"select a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,B.BUD_AMT,b.ORD_NO,b.ORD_DT,b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,b.CV_CD,b.PNP_CD
from bud_temp_mon a,reapp_sm b
where a.fin_yr=b.fin_yr
and a.grnt_cd=b.grnt_cd
and a.mjh_cd=b.mjh_cd
and a.smjh_cd=b.smjh_cd
and a.mih_cd=b.mih_cd
and a.sbh_cd=b.sbh_cd
and a.dtlh_cd=b.dtlh_cd
and a.objh_cd=b.objh_cd
and A.CV_CD= B.CV_CD
and A.PNP_CD=B.PNP_CD
and A.DR_CR=B.DR_CR
and a.grnt_cd='03'
GROUP BY a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,B.BUD_AMT,b.ORD_NO,b.ORD_DT,b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,b.CV_CD,b.PNP_CD
order by grnt_cd,objh_cd"
the result of this query is whenever the column bud_amt has 2 values the values of other columns gets repeated again which results in the values being totaled twice. I would like to know if union can be used to filter the values .
The same query if i use it report builder, it gives me an error " fetches more than one value" and does not give any result. I can't use sum function for bud_amt column since i don't require net values
I would be thankful if the query can be corrected accordingly.
|
