SQLTeam.com | Weblogs | Forums

Why is it given behaving like Cartesian join?

sql2012

#1

Hi team ,
Here i am using inner join but it is giving result of cartesian join,i am not getting why?
In my table no primary ,no foriegn key relation.But matching data available in all table .
I want fetch only matching data which is match in all three table .Below is my query please see this one and tell me where i am going wrong.

SELECT i.sid,g.sid,q.sid FROM

IRIS_STG i inner join GL_1015_STG g
on i.RETR_REF_NO=g.RETR_REF_NO inner join QCB_STG q on
q.RETR_REF_NO=i.RETR_REF_NO where
q.TRANS_AMOUNT=i.AMT_TRAN and i.AMT_TRAN=
g.TRA_AMT and i.PAN=q.CARD_NUMBER and
i.SYS_TRACE_AUDIT_NO=g.stan
and q.BIN IN(434141,484823,428246,472839)
AND q.CHANNEL='ATM' AND q.ISS_BANK_CODE=3
AND q.ACQ_BANK_CODE!=3
AND q.TRAN_TYPE IN(10,98,99)
AND (q.RECON_ID IS NULL OR q.RECON_STATUS='AU')

	and  g.AUTHORIZING_CHANNEL IN('9999')
	 AND g.PROC_CODE=01 AND g.ACQUIRING_CHANNEL_ID=28 
	 AND g.TELL_ID=9953 AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')

	 and 

	 i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 AND i.RESP_CODE IN('000','036','037') AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')

thanks & regards,
shivam


#2

JOIN conditions not "tight" enough somewhere?

Presumably you expect these four COUNTs to be the same?

SELECT	COUNT(*) AS i
FROM	IRIS_STG i
where	    
	and i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 
	AND i.RESP_CODE IN('000','036','037') 
	AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')
SELECT	COUNT(*) AS g
FROM	IRIS_STG i
	inner join GL_1015_STG g
		 on g.RETR_REF_NO  = i.RETR_REF_NO
		and g.TRA_AMT      = i.AMT_TRAN
		and g.stan         = i.SYS_TRACE_AUDIT_NO
		and g.AUTHORIZING_CHANNEL IN('9999')
		AND g.PROC_CODE=01
		AND g.ACQUIRING_CHANNEL_ID=28 
		AND g.TELL_ID=9953 
		AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')
where	    
	and i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 
	AND i.RESP_CODE IN('000','036','037') 
	AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')
SELECT	COUNT(*) AS q
FROM	IRIS_STG i
	inner join QCB_STG q
		 on q.RETR_REF_NO  = i.RETR_REF_NO 
		AND q.TRANS_AMOUNT = i.AMT_TRAN
		and q.CARD_NUMBER  = i.PAN
		and q.BIN IN(434141,484823,428246,472839) 		
		AND q.CHANNEL='ATM' 
		AND q.ISS_BANK_CODE=3		
		AND q.ACQ_BANK_CODE!=3		
		AND q.TRAN_TYPE IN(10,98,99) 		
		AND (q.RECON_ID IS NULL OR q.RECON_STATUS='AU')
where	    
	and i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 
	AND i.RESP_CODE IN('000','036','037') 
	AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')
SELECT	COUNT(*) AS Combined
FROM	IRIS_STG i
	inner join GL_1015_STG g
		 on g.RETR_REF_NO  = i.RETR_REF_NO
		and g.TRA_AMT      = i.AMT_TRAN
		and g.stan         = i.SYS_TRACE_AUDIT_NO
		and g.AUTHORIZING_CHANNEL IN('9999')
		AND g.PROC_CODE=01
		AND g.ACQUIRING_CHANNEL_ID=28 
		AND g.TELL_ID=9953 
		AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')
	inner join QCB_STG q
		 on q.RETR_REF_NO  = i.RETR_REF_NO 
		AND q.TRANS_AMOUNT = i.AMT_TRAN
		and q.CARD_NUMBER  = i.PAN
		and q.BIN IN(434141,484823,428246,472839) 		
		AND q.CHANNEL='ATM' 
		AND q.ISS_BANK_CODE=3		
		AND q.ACQ_BANK_CODE!=3		
		AND q.TRAN_TYPE IN(10,98,99) 		
		AND (q.RECON_ID IS NULL OR q.RECON_STATUS='AU')
where	    
	and i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 
	AND i.RESP_CODE IN('000','036','037') 
	AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')

#3

Hi Kristen,

thanks,
It has solve.But i have another problem ,which i am discussng below.

Here i am getting performance issue using cursor in above query.I want to update row by row record with genearting dynamic id for each row .Can you help me how can i overcome this performance issue or any alternate for this which provide me row by row updation .I am giving procedure which i am using.

ALTER procedure [dbo].[updateNapsRecon]

as
DECLARE @irisRet_Ref_No varchar(50),
@glRet_Ref_No varchar(50),
@irisRecon_id varchar(50),
@glRecon_id varchar(50),
@iris_sid varchar(50),
@gl_sid varchar(50),
@iris_amt varchar(50),
@gl_amt varchar(50),
@genrateRecon_id varchar(50),
@genrateSid varchar(50),
@qcbRet_Ref_No varchar(50),
@qcb_sid varchar(50),
@qcb_amt varchar(50),
@qcb_recon_id varchar(50),
@irisversion int,
@glversion int,
@qcbversion int,
@ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint,
@count int=1

-- Here i create cursor name is c3 according to require condition
DECLARE c4 CURSOR OPTIMISTIC FOR SELECT i.sid,i.RETR_REF_NO ,g.sid,g.RETR_REF_NO ,q.sid,q.RETR_REF_NO
, i.version,g.version,q.version FROM IRIS_stg i inner join Gl_1015_stg g
on i.RETR_REF_NO=g.RETR_REF_NO inner join QCB_STG q on
q.RETR_REF_NO=i.RETR_REF_NO where
q.TRANS_AMOUNT=i.AMT_TRAN and
i.AMT_TRAN=
g.TRA_AMT and i.PAN=q.CARD_NUMBER
and
i.SYS_TRACE_AUDIT_NO=g.stan
and i.DATE_LOC_TRAN=g.TRA_DATE

	and  g.AUTHORIZING_CHANNEL IN('9999')
	 AND g.PROC_CODE=01 AND g.ACQUIRING_CHANNEL_ID=28 
	 AND g.TELL_ID=9953 AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')

	 and 

	 i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 AND i.RESP_CODE IN('000','036','037') AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')

-- open the cursor
OPEN c4;

-- fetch one row column value and store in to @colName,@colType
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@gl_sid,@glRet_Ref_No,
@qcb_sid,@qcbRet_Ref_No,
@irisversion,@glversion,@qcbversion;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--here i am inserting record in diffrent table on basis of sid
insert into IRIS_AUDIT select * from IRIS_stg where sid=@iris_sid;
insert into GL_1015_AUDIT select * from GL_1015_STG where sid=@gl_sid;
insert into QCB_AUDIT select * from QCB_STG where sid=@qcb_sid;

--here i am genearting recon id dynamically basis of retr_ref_no and sid
set @genrateRecon_id=@irisRet_Ref_No+@iris_sid;
set @irisversion=@irisversion+1;
set @glversion=@glversion+1;
set @qcbversion=@qcbversion+1;
--updating row by row record basis on sid
update IRIS_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@irisversion where sid=@iris_sid
update GL_1015_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@glversion where sid=@gl_sid
update QCB_STG set RECON_ID=@genrateRecon_id ,RECON_STATUS='AM' ,VERSION=@qcbversion where sid=@qcb_sid
print @count
set @count=@count+1

-- fetch one row column value and store in to @colName,@colType inside loop

FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@gl_sid,@glRet_Ref_No,
@qcb_sid,@qcbRet_Ref_No,
@irisversion,@glversion,@qcbversion;

END
--close while loo body

-- close the cursor c3
CLOSE c4;

--DEALLOCATE cursor c3 that mean every execution time it will treate as new one
DEALLOCATE c4;

In above procedure I am creating cursor and fetching one row and creating reconid for that row and update that row`s recon id null to new generated recon id.In this process i have millions of record and this process take more time .I want to reduce time can u help me.

thanks & regards,
shivam