3

I am trying to run the below query 

SELECT      mr.AsofDate as date,
            mr.FA,
            mr.TPNL as tpnl,
            mr.MPNL as mpnl,
            mrf.tpnl as mrfTpnl,
            mrf.cpnl as mrfCpnl
FROM        vw_daily mr
FULL        OUTER JOIN mrfeeddaily  mrf
ON          mr.FA = mrf.book and mr.AsofDate = mrf.AsOfDate
WHERE       mr.AsofDate = '20141121'

But i end up getting only rows from the first View vw_daily and the columns from mrfeeddaily are NULL, doesn't Full join return all non matching rows as well ? what am i missing.

There is no common data between the view and the table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TJ_
  • 255
  • 3
  • 12

2 Answers2

3

Move the filter to ON condition

ON tells what are the rows to used for Joining but where clause filters the result of join.

Only matched rows in vw_daily table is going to have value in asofdate so the filtering the rows in Where clause is going to do implicit conversion from FULL OUTER JOIN to INNER JOIN

SELECT mr.asofdate AS date, 
       mr.fa, 
       mr.tpnl     AS tpnl, 
       mr.mpnl     AS mpnl, 
       mrf.tpnl    AS mrfTpnl, 
       mrf.cpnl    AS mrfCpnl 
FROM   vw_daily mr 
       FULL OUTER JOIN mrfeeddaily mrf 
                    ON mr.fa = mrf.book 
                       AND mr.asofdate = mrf.asofdate 
                       AND mr.asofdate = '20141121'
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Hi, on doing that I get all the rows in the vw_daily table (130 Million) for all the historic dates and all the rows from mrfeeddaily as opposed to what i want 3900 rows from vw_daily table for AsOfDate 20141121 and 95 rows from mrfeeddaily table for AsOfDate 20141121 so total of 3995 in my result set, please let me know if I am not asking the correct question and i will change it – TJ_ Aug 24 '15 at 15:43
0

Filter the condition in your join.

SELECT mr.AsofDate AS date, mr.FA, mr.TPNL AS tpnl, mr.MPNL AS mpnl,  mrf.tpnl AS mrfTpnl, mrf.cpnl AS mrfCpnl
FROM vw_daily mr
FULL OUTER JOIN mrfeeddaily  mrf ON mr.FA = mrf.book 
AND mr.AsofDate = mrf.AsOfDate 
AND mr.AsofDate = '20141121'
Matt
  • 14,906
  • 27
  • 99
  • 149
  • Hi @matt, on doing that I get all the rows in the vw_daily table (130 Million) for all the historic dates and all the rows from mrfeeddaily as opposed to what i want 3900 rows from vw_daily table for AsOfDate 20141121 and 95 rows from mrfeeddaily table for AsOfDate 20141121 so total of 3995 in my result set, please let me know if I am not asking the correct question and i will change it – TJ_ Aug 24 '15 at 16:05