trouble in creating view....

Database
Enthusiast

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

Tags (2)
1 REPLY
Junior Contributor

Re: trouble in creating view....

Hi Yuvana,

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