sql query anaysis

Database
Enthusiast

sql query anaysis

SELECT  TO_CHAR(B.LOAD_DATE), B.SERVICE, A.CIRCLE, A.CIRCLE_DESCRIPTION, SUM(B.TOT_RECORDS_TRANSMITTED-1), COUNT(B.UPDATED_FILE_NAME), SUM(C.MERGING_COUNT), COUNT(C.FILE_NAME), SUM(D.TARGET_COUNT), D.RECORD_TYPE

FROM RCN_SA_MAP_ALL_CIRCLE_PREFIX A LEFT OUTER JOIN RCN_SA_OTHERSTR_MEDIATION_DATA B

ON SUBSTR(A.FILE_NAME_PREFIX, 0, 6) = substr(B.UPDATED_FILE_NAME, 0, 6) LEFT OUTER JOIN RCN_SA_OTHERSTR_FILE_DATA C

ON B.UPDATED_FILE_NAME = C.FILE_NAME LEFT OUTER JOIN RCN_SA_OTHERSTR_TARGET_DATA D

ON B.UPDATED_FILE_NAME = D.FILE_NAME

WHERE B.SERVICE = 'TAPIN_CSV'

AND C.SERVICE = 'TAPIN_CSV'

AND D.SERVICE = 'TAPIN_CSV'

GROUP BY TO_CHAR(B.LOAD_DATE, 'DD-MON-YYYY'), B.SERVICE, A.CIRCLE, A.CIRCLE_DESCRIPTION, D.RECORD_TYPE);

I/p: 

we have data in RCN_SA_MAP_ALL_CIRCLE_PREFIX ,RCN_SA_OTHERSTR_MEDIATION_DATA ,RCN_SA_OTHERSTR_FILE_DATA 

these 3 tables but we dont have data in RCN_SA_OTHERSTR_TARGET_DATA .

o/p:

I am getting no rows

(o/p need):

need all 3 table columns data (which has data) and blanks with colums of RCN_SA_OTHERSTR_TARGET_DATA (empty table)

please, suggest me iam using left outer join , iam not getting were it going wrong.

2 REPLIES
Enthusiast

Re: sql query anaysis

You have a condition "AND D.SERVICE = 'TAPIN_CSV"

If D is empty this condition limits your answerset to 0 rows.

Rglass

Enthusiast

Re: sql query anaysis

HI Glass,

Thanks .. right