Hi to all,
Here i am writing 2 queries. I just want to know that is the result is same by both or not for "SUMS" field.
Because when i use Query1 in the 'group left' report the sums field is different in comparison to Query2 'sums' field(using this query on SQL>).
Query1:
SELECT SUBSTR(A.NOTE_NO,1,5)||SUBSTR(A.NOTE_NO,6) NUM,
G.FNAME||' '||G.MNAME||' '||G.LNAME NAME,
TRUNC(NVL(SUM(TRAVEL_EXP),0)+NVL(SUM(DA_EXP),0)+NVL(SUM(RMA_EXP),0),-2) SUMS,
D.MO_DATE MDATE,D.MO_STATUS STATUS, F.GROUP_NAME, G.PERSONALID
FROM FINANCE.MO_DETAILS A,
FINANCE.MO_CITY_MASTER B,
FINANCE.MO_CITY_MASTER C,
FINANCE.MO_MASTER D,
PAY.C_GROUP_MASTER F,
PAY.EMPLOYEES G
WHERE
D.MO_STATUS='A'
AND
D.MO_DATE BETWEEN '01-APR-2005' AND '31-MAR-2006'
AND
D.NOTE_NO=A.NOTE_NO
AND
G.PERSONALID(+)=D.PERSONALID
AND
D.GROUP_NAME=F.GROUP_NAME
GROUP BY A.NOTE_NO,D.MO_DATE,
D.DEP_DATE, G.FNAME||' '||G.MNAME||' '||G.LNAME, D.MO_STATUS, F.GROUP_NAME, G.PERSONALID
ORDER BY TO_NUMBER(SUBSTR(A.NOTE_NO,6)),F.GROUP_NAME
Query2:
SELECT TRUNC((NVL(SUM(NVL(TRAVEL_EXP,0)),0)+NVL(SUM(NVL(DA_EXP,0)),0)+NVL(SUM(NVL(RMA_EXP,0)),0)),-2) SUMS, F.GROUP_NAME
FROM FINANCE.MO_MASTER A,
FINANCE.MO_DETAILS B,
PAY.C_GROUP_MASTER F,PAY.EMPLOYEES D
WHERE
MO_STATUS='A'
AND
D.PERSONALID(+)=A.PERSONALID
AND
F.GROUP_NAME=A.GROUP_NAME AND
A.NOTE_NO=B.NOTE_NO AND
MO_DATE BETWEEN '01-APR-2005' AND '31-MAR-2006'
GROUP BY
F.GROUP_NAME
Waiting for reply
Shaila Mehra.
] 
Blog
Search
Help
Members
Register
Login
Home
