Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Steven Rebello
  Where is Steven Rebello?
 Bangalore
 India
 Steven Rebello
 Tags
Subject: RE: View with empty field
Thread ID: 345186 Message ID: 345219 # Views: 32 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Sunday, May 20, 2012 8:39:02 PM         
   


> >
> > As you see I didn't use LEFT JOIN. That's what's forcing NULL.
> > In my sample data it makes no difference if I use INNER JOIN or LEFT JOIN.
> >
> > Could you post some of your data showing copr, ddate, dfromdate, and dtodate.
> > With _VFP.DataToClip it's eas to create sopme ample data and paste it into Foxite.
> >
> > -Anders
>
>
OK, got it:
> Oprwage table
>      Opr  Dfromdate   DtoDate  Namt        Nserial    
>      WDM 12/03/2012 30/04/2012 30.00          1
>      WDM 01/05/2012 31/05/2012 45.00          2
>      WDM 01/06/2012  /  /      70.00          3
>      SPR 01/04/2012 11/12/2012                4
>      SPR 12/12/2012 11/03/2012                9
> 
> WorkAllocation table:
> Ddate         Nempno    Copr      Nindex     
> 12/03/2012       125    WDM                 1
> 05/05/2012       333    WDM                 2
> 01/06/2012       319    WDM                 4
> 
> 
> 

>
> Thanks!

The result of your query withg your date is
Copr_a	Copr_b	Ddate     	Dfromdate 	Dtodate   	Exp_6     	Namt   	
WDM WDM 12/03/2012 12/03/2012 30/04/2012 30/04/2012 30.00
WDM WDM 05/05/2012 01/05/2012 31/05/2012 31/05/2012 45.00
WDM .NULL. 01/06/2012 .NULL. .NULL. .NULL. .NULL.

The reason the last one is all Null is that it doesn't fulfill the JOIN condition
workallocation.copr=oprwage.copr
the left side table's data included anyway because of LEFT JOIN. With an INNER JOIN it's not included at all.
That's perfectly normal and correct - there is no corresponding row with a copr 'SPR'. Why fight it?
All you wanted was a way of handling an empty dTodate and you've got it.
-Anders

ENTIRE THREAD

View with empty field Posted by Steven Rebello @ 5/19/2012 8:03:44 PM
RE: View with empty field Posted by tushar @ 5/20/2012 5:53:52 AM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:37:14 PM
RE: View with empty field Posted by Mustapha Bihmouten @ 5/20/2012 9:06:30 AM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 1:12:30 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:36:37 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 1:15:49 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 3:34:30 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:35:03 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 5:49:34 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:58:49 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 6:42:55 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 8:39:02 PM
RE: View with empty field Posted by Steven Rebello @ 5/24/2012 5:24:13 AM
RE: View with empty field Posted by Steven Rebello @ 5/24/2012 5:50:34 AM