All Forums Database
yuvana 7 posts Joined 09/14
20 May 2015
trouble in creating view....

Hi 
I am trying to implement a view on tow tables 
but the problem is I am not getting the result set after creating the view.
I have a SOURCE tables 
First source table T1
Second Source table T2.
 
my Requirement is  I am joining T1 with T2 on date, t2nbr,t3nbr,t4nbr,t5nbr.
I want all the mathing records from the T1 table+ T2 table's last column
 
If T1 with T2 matching on date, t2nbr, t3nbr, t4nbr matched but t5 nbr not matched then I want all those t5 column not matched records As 
all Matching records from the T1 table , but in the place of T1's t5nbr column, I want T2's t5column and the last five columns in T1 should come as 1.
 
 
So I created as 
 

CREATE  VIEW AS VIEW3
(
SELECT
T1.TN_DT                      
,T1.SNBR                 
,T1.RNBR                     
,T1.TNBR                    
,T1.CNBR       
,T1.CHNBR        
,T1.S_NBR                    
,T1.UNBR                    
,T1.E_TCD            
,T1.S_QTY                   
,T1.S_AMT             
,T1.C_AMT            
,T1.E_AMT         
,T2.D_WANTED_AMT           
 FROM DB1. TABLE T1  T1
 JOIN
 DB2.TABLET2  T2
ON T1.TN_DT  = T2.T_DT
AND T1.SNBR = T2.SNBR
AND T1.RNBR = T2.RNBR
AND T1.TNBR = T2.TNBR
AND T1.CNBR =T2.CNBR
AND T1.S_NBR = T2.S_NBR
 
 UNION
 
SELECT
T1.TN_DT                   
,T1.SNBR                 
,T1.RNBR                    
,T1.TNBR                    
,T1.CNBR      
,T1.CHNBR                      
,T2.S_NBR                    
,T2.UNBR                    
,T2.E_TCD                  
,0AS S_QTY                
,0 AS S_AMT            
,0 AS C_AMT           
,0 AS E_AMT            
,T2.D_WANTED_AMT         
 FROM  DB1.TABLET1  T1
 JOIN
DB2.TABLET2  T2
 ON T1.TN_DT  = T2.T_DT
AND T1.SNBR = T2.SNBR
AND T1.RNBR = T2.RNBR
AND T1.TNBR = T2.TNBR
AND T1.CNBR =T2.NBR
WHERE  T1.S_NBR  <>  T2.S_NBR)

But I have only 8 Matching records in T1 table and T2
I have 1 single  record with one date 2/1/2015  12:20:01 with all condition match .
I have 3 records with one date3/1/2015  12:20:02 with all condition match
I have 1 single record with  one date 5/1/2015  12:40:01  all condition match  except T1.S_NBR  <>  T2.S_NBR
I have 3 records with one date 4/1/2015  12:40:02 all condition match except T1.S_NBR  <>  T2.S_NBR.
 
in Result set It suppose to come total 8 rows, but why I am getting 11 rows,
 
here the first join condition all matched specifice date '3' records again populating with second joining condition with along with the non matched records, but the non matched single record is not populating in the result set...
 
Please help me out in creating this veiw  correctly...
 
Thank you 
Yuvana

dnoeth 4628 posts Joined 11/04
20 May 2015

Hi Yuvana,
UNION does an implicit DISTINCT, try replacing it with UNION ALL (which will be more efficient, too)

Dieter

You must sign in to leave a comment.