Home » SQL & PL/SQL » SQL & PL/SQL » Select query fetching more than expected records
Select query fetching more than expected records [message #245190] Fri, 15 June 2007 07:28 Go to next message
mailtonagaraja
Messages: 9
Registered: June 2007
Junior Member
Hi,
I have a problem..here all the tables are havind some meaningfull data..10 records each.. and in the code if i remove coments its giving 0 records..and with coments its giving 48000..records... can any one please help me...

Thanks and regards
Nagaraja.M




SELECT  count(*)

FROM    WPHDM_OC_STR_BASIC_HT A ,
        WPHDM_OB_DPTMT_RECORD B1,
	WPHDM_OB_STR_RANK_DPTMT B,
	WPHDM_OB_APPARATUS_RECORD D
	,WPHD_DM_ORG_TD_MV_TEMP CL1
	,WPHD_DM_DPMT_BASIC_TD_MV_TEMP DL1
	,WPHD_DM_ORG_TD_MV_TEMP CL2
	,WPHD_DM_DPMT_BASIC_TD_MV_TEMP DL2
	,WPHD_DM_ORG_TD_MV_TEMP CL3
	,WPHD_DM_ORG_TD_MV_TEMP CL4
       ,WPHD_DM_DPMT_BASIC_TD_MV_TEMP DL4,
	WPHDM_OB_SV_STR P,
	WPHDM_OB_SV Q,

	WPHDS_DM_STR_ATTR G,
	WPHDM_OA_DATE_CONTROL H,
	WPHDM_OB_ITM_DIVZONE AA  ,
	WPHDM_OB_ITM_DIVAREA BB   ,
	WPHDM_OB_DELIVERY_AREA EE ,
	WPHDM_OC_STR_OPE_MNG FF   ,
	    	(SELECT A.STR_CD
        ,MAX(A.BUSI_DATE) AS LAST_BUSI_DATE
         FROM WPHDS_DS_DAYNUM_1 A
         WHERE A.BUSI_DAY_NUM = 1
         GROUP BY A.STR_CD
        UNION
        SELECT A.STR_CD
        ,A.OPEN_DATE AS LAST_BUSI_DATE
        FROM WPHDM_OC_STR_HT       A
        ,WPHDM_OA_DATE_CONTROL B
        WHERE A.LATEST_FLG = 1
    AND A.DENY_FLG   = 0
    AND B.COMPANY_CD = '000081'
    AND B.SYSTEM_CLS = 'DW'
    AND A.OPEN_DATE >= B.TODAY -1
    AND A.OPEN_DATE <= B.TODAY +3
	 )K


WHERE 	H.COMPANY_CD = '000081'
    AND A.COMPANY_CD = H.COMPANY_CD
    AND H.SYSTEM_CLS = 'DW'
    AND A.STR_CD = K.STR_CD
    AND A.STR_CD=P.STR_CD -- FOR SV
    AND (H.TODAY -1) BETWEEN P.VALD_ST_DATE AND P.VALD_ED_DATE
	AND (H.TODAY -1) BETWEEN AA.VALD_ST_DATE AND AA.VALD_ED_DATE
	 AND (H.TODAY -1) BETWEEN BB.VALD_ST_DATE AND BB.VALD_ED_DATE
	 AND (H.TODAY -1) BETWEEN EE.VALD_ST_DATE AND EE.VALD_ED_DATE
	 AND (H.TODAY -1) BETWEEN FF.VALD_ST_DATE AND FF.VALD_ED_DATE
    AND P.PROC_CLS <> '7'
    AND P.SV_CD = Q.SV_CD
    AND A.DENY_FLG=0
	AND D.DENY_FLG = 0
    AND B.DENY_FLG = 0
	 AND B1.DENY_FLG = 0
    AND A.DENY_FLG = 0
    AND B.COMPANY_CD = D.COMPANY_CD
    AND B.ORGZ_SYSTEM_CLS = D.ORGZ_SYSTEM_CLS
	AND B.POST_CD = D.POST_CD
    AND B.ORGZ_INF_HIST_NO = D.ORGZ_INF_HIST_NO
    AND B.COMPANY_CD = H.COMPANY_CD
    AND (H.TODAY - 1) BETWEEN  D.VALD_ST_DATE AND  D.VALD_ED_DATE
	AND A.COMPANY_CD = B1.COMPANY_CD
    AND A.STR_CD = B1.POST_CD
    AND A.POST_INF_HIST_NO = B1.POST_INF_HIST_NO
    AND A.COMPANY_CD = H.COMPANY_CD
    AND (H.TODAY - 1) BETWEEN B1.VALD_ST_DATE AND B1.VALD_ED_DATE
    AND (H.TODAY - 1) <= B1.VALD_ED_DATE
    AND H.COMPANY_CD = '000081'
    AND H.SYSTEM_CLS = 'DW'
	-- uPTO THIS RUNNING INFINITELY
	 AND B.ORGZ_SYSTEM_CLS = '01'
     AND CL1.ORGZ_TREE_LEVEL = '1'
     AND CL2.ORGZ_TREE_LEVEL = '2'
     AND CL3.ORGZ_TREE_LEVEL = '3'
     AND CL4.ORGZ_TREE_LEVEL = '4'
	---- upto this its giving 1296000000 recorgs
	 AND A.COMPANY_CD = H.COMPANY_CD
     AND A.COMPANY_CD = B.COMPANY_CD
	--- upto this its giving 1296000000 recorgs
	 --AND A.STR_CD = B.POST_CD
     --AND B.UPPER_POST_CD = CL1.POST_CD
    --AND CL1.POST_CD = DL1.POST_CD
     AND CL1.COMPANY_CD = DL1.COMPANY_CD
     AND B.COMPANY_CD = CL1.COMPANY_CD
     AND CL1.ORGZ_SYSTEM_CLS = B.ORGZ_SYSTEM_CLS
---- upto this its giving 1296000000 recorgs
     AND AA.SKU_AREA_CD = BB.SKU_AREA_CD
	 --AND FF.STR_CD=A.STR_CD
	 AND FF.DLVR_AREA_CD=EE.DLVR_AREA_CD
	 AND AA.SKU_DPT_AREA_CD=EE.SKU_DPT_AREA_CD
---- upto this its giving 40000 recorgs
     
	--Upto this 40000 records
	--AND CL1.UPPER_POST_CD = CL2.POST_CD
     AND CL2.POST_CD = DL2.POST_CD
     AND CL2.COMPANY_CD = DL2.COMPANY_CD
     AND CL1.COMPANY_CD = CL2.COMPANY_CD
     AND CL2.ORGZ_SYSTEM_CLS = CL1.ORGZ_SYSTEM_CLS
   --AND CL2.UPPER_POST_CD = CL3.POST_CD
     AND CL2.COMPANY_CD = CL3.COMPANY_CD
     AND CL3.ORGZ_SYSTEM_CLS = CL2.ORGZ_SYSTEM_CLS
	-- upto this 6000 records
	--AND CL3.UPPER_POST_CD = CL4.POST_CD
    --AND CL4.POST_CD = DL4.POST_CD
     AND CL4.COMPANY_CD = DL4.COMPANY_CD
     AND CL4.COMPANY_CD = CL3.COMPANY_CD
	-- Upto this  18000 records
	 AND CL4.ORGZ_SYSTEM_CLS = CL3.ORGZ_SYSTEM_CLS
     AND A.STR_CD = K.STR_CD
	-- TO JOIN TABLE WPHDS_DM_STR_ATTR
     AND A.COMPANY_CD = G.COMPANY_CD
     --AND A.STR_CD = G.STR_CD
     AND G.CLNDR_DATE = H.TODAY-1
	-- THIS PART OF THE CONDITION ARE FOR STORE AREA HIERARCHY
     AND FF.COMPANY_CD=A.COMPANY_CD
	 ORDER BY A.STR_CD
	    
	 ;

[Updated on: Fri, 15 June 2007 08:32]

Report message to a moderator

Re: Select query fetching more than expected records... Please Urgent [message #245202 is a reply to message #245190] Fri, 15 June 2007 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel

Re: Select query fetching more than expected records... only answer if you have nothing else to do [message #245209 is a reply to message #245190] Fri, 15 June 2007 08:07 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
I refuse to read through your entire unformatted mess, however just reading the first few lines shows me one error already

(SELECT A.STR_CD
,MAX(A.BUSI_DATE) AS LAST_BUSI_DATE
FROM WPHDS_DS_DAYNUM_1 A
WHERE A.BUSI_DAY_NUM = 1
GROUP BY A.STR_CD
UNION
SELECT A.STR_CD
,A.OPEN_DATE AS LAST_BUSI_DATE
FROM WPHDM_OC_STR_HT A
,WPHDM_OA_DATE_CONTROL B
WHERE A.LATEST_FLG = 1
AND A.DENY_FLG = 0
AND B.COMPANY_CD = '000081'
AND B.SYSTEM_CLS = 'DW'
AND A.OPEN_DATE >= B.TODAY -1
AND A.OPEN_DATE <= B.TODAY +3
)K


You have two tables in this inline view and you are not joining them to each other. This will produce a cartisian join, which is normally a very bad thing.

[Updated on: Fri, 15 June 2007 08:08]

Report message to a moderator

Re: Select query fetching more than expected records... Please Urgent [message #245212 is a reply to message #245202] Fri, 15 June 2007 08:28 Go to previous messageGo to next message
mailtonagaraja
Messages: 9
Registered: June 2007
Junior Member
Hi .. I modified the SQL.. can you please give a solution...
Re: Select query fetching more than expected records... Please Urgent [message #245529 is a reply to message #245202] Mon, 18 June 2007 00:53 Go to previous message
mailtonagaraja
Messages: 9
Registered: June 2007
Junior Member
Hi Michel,
Can you please see the code which i sent .. and please tell me the problem... i am still struggling to do it...please...

Previous Topic: SQL : Connect BY PRIOR How to
Next Topic: Time condtion using Oracle
Goto Forum:
  


Current Time: Sun Dec 04 08:36:08 CST 2016

Total time taken to generate the page: 0.10002 seconds