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


> >
> > Sorry, Anders, with the code (simplified to match yours below), I still get null values for the fields linked to the empty date..
> >
> >
SELECT  Workallocation.copr,oprwage.copr,Workallocation.ddate, ;
> >    Oprwage.dfromdate, oprwage.dtodate, EVL(Oprwage.dtodate,DATE()), Oprwage.namt;
> >  FROM C:\PANORAMA\WORKALLOCATION.DBF ;
> >    LEFT JOIN guinty!oprwage ;
> >    on Workallocation.ddate BETWEEN Oprwage.dfromdate AND EVL(Oprwage.dtodate,DATE()) ;
> > and Workallocation.copr = Oprwage.copr
> > 

> >
> > Regards,
> >
> > Steve
>
> 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!

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