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


> > Hi All,
> >
> > I am trying to create a view where a wage value is fixed based on a relation from the work allocation table 'Opr' field and the 'Opr' stored in a wage table which has a wage period, defined by a dfromdate and a dtodate field. The view gets the wage period using the between clause, as the code below shows. Just one thing. The dtodate for the wage table is entered automatically through a program, when a new record is added. For example, when adding a dfromdate in the latest record, the previous record will get a dtodate which will be one day prior to the last record dfrom date. That way, there is date continuity so that any date in the wage allocation has a fixed associated period. Now the important part. The last record will always have an empty dtodate field, because unless the next date is defined, it will be left empty. Now the final problem. In the query below, it skips the record having the empty dtodate. I have copied the code. Could anyone help, please?
> >
> > Thanks a lot!!
> >
> > Regards,
> >
> > Steve
> >
> >
SELECT Workallocation.ddate, Workallocation.copr,;
> >   Workallocation.nempno, Biodata.cname, Biodata.class, Biodata.gang,;
> >   Biodata.nempno, Workallocation.nindex, Oprwage.namt, Oprwage.dfromdate,;
> >   Oprwage.nserial;
> >  FROM ;
> >      C:\PANORAMA\WORKALLOCATION.DBF ;
> >     INNER JOIN GUINTY!BIODATA ;
> >    ON  Workallocation.nempno = Biodata.nempno ;
> >     INNER JOIN guinty!oprwage ;
> >    ON  Workallocation.copr = Oprwage.copr;
> >  WHERE  Workallocation.ddate BETWEEN Oprwage.dfromdate AND IIF (EMPTY(Oprwage.dtodate),DATE(),oprwage.dtodate);
> > ORDER BY Workallocation.ddate, Biodata.gang, Biodata.cname

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

Hi Tushar, Mustapha & Anders,

Thanks very much for your inputs. Sorry I had a problem with my connection, so hence the delay. Just testing your code. Will revert.

Regards,

Steve

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