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: 345214 # Views: 29 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Sunday, May 20, 2012 5:49:34 PM         
   


> >
> > Change INNER JOIN to LEFT JOIN and change WHERE to AND, or add an EVL()
> > You can also perhaps give the dtodate a default value of DATE(), or some date fifty years later, which is overwritten by a calculated value when a new period is added.
> > WHERE clauses have priority ove ON clauses; if the WHERE filter eliminates a row it is never added to the rest of the query processing.
> >
> >
> > CREATE CURSOR X1 (id i UNIQUE, ddate D) 
> > INSERT INTO X1 VALUES (1,DATE()-5)
> > INSERT INTO x1 VALUES (2,DATE()-20)
> > 
> > CREATE CURSOR X2 (x2id i UNIQUE, x1id i, fromdate D, todate D) 
> >  INSERT INTO X2 VALUES (1,1,DATE()-6,DATE()+30)
> >  INSERT INTO X2 VALUES (2,1,DATE()-6,{//})
> >  INSERT INTO X2 VALUES (3,2, DATE()-21,{//})
> >  INSERT INTO X2 VALUES (4,1, DATE()-4, DATE()+5)
> >  
> >  SELECT x1.id, X2.x1id, X1.ddate, X2.fromdate, X2.todate,EVL(X2.todate,DATE()) FROM x1  JOIN x2 ;
> >  ON X1.ddate BETWEEN X2.fromdate AND EVL(X2.todate,DATE()) ;
> >  AND X1.id = X2.x1id

> >
> >

> > Id X2id Ddate Fromdate Todate Exp_6
> > 1 1 2012/05/15 2012/05/14 2012/06/19 2012/06/19
> > 1 2 2012/05/15 2012/05/14 / / 2012/05/20
> > 2 3 2012/04/30 2012/04/29 / / 2012/05/20
> >

> >
> >
> > -Anders
>
> 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

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