Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Sanjay Sharma
  Where is Sanjay Sharma?
 Rishra
 India
 Sanjay Sharma
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Check to prevent duplicate at Database
Thread ID: 331505 Message ID: 331546 # Views: 51 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 31, 2011 4:28:26 AM         
   


> >
> >
> > chart of accounts | gl account group | number of G/L account (lower area limit) | number of G/L account (upper area limit) | long text :: Caption
> > ktopl | ktoks | vornr | bisnr | text30 :; field name
> > -----------------------------------------------------------------------------------------------------------------------------------------------------
> > CAPE | ADCU | 0014390000 | 0014399999 | Advance from customers
> > CAPE | ADIC | 0025010000 | 0025019999 | Advance Inter Company
> > CAPE | ADIN | 0025000000 | 0025009999 | Advance Inter Unit
> > CAPE | ADMS | 0025200000 | 0025299999 | Advance for Material / Servic
> >
> > index on ktopl with candidate key.
> > index on ktopl+ktoks with candidate key.
> >
> > I would like to prevent any number range which is falling between any number range already exists for every ktopl.
> >
> >
> > Individual gl account will be opened between the number mentioned in another table, such as:
> >
> > chart of accounts | gl account group | number of G/L account | long text
> > ktopl | ktoks | vornr | text30
> > ---------------------------------------------------------------------------
> > CAPE | ADIC | 0025010012 | (Name of a company)
> > CAPE | ADIN | 0025000336 | (Name of a unit)
> >
> > : Valid Record : since the vornr number is between 0025010000 and 0025019999.
> >
> > CAPE | ADIC | 0014390059 | (Name of a company)
> >
> > : Invalid Record, since the vornr number is not between 0025010000 and 0025019999 for every ktopl and ktoks.
> >
> > Account posting will be made based on the last table.
> >
> > Regards,
>
> The Candidate index on the column 'ktopl' is not working. There are four duplicate values 'CAPE'.
> To find if a vornr is ok:
>
>
*****
>  TEXT TO lcText NOSHOW PRETEXT 8
>  CAPE | ADCU | 0014390000 | 0014399999 | Advance from customers|
>  CAPE | ADIC | 0025010000 | 0025019999 | Advance Inter Company|
>  CAPE | ADIN | 0025000000 | 0025009999 | Advance Inter Unit|
>  CAPE | ADMS | 0025200000 | 0025299999 | Advance for Material/Service|
> ENDTEXT 
> rows=ALINES(aa, lcText, 1, '|')
> CREATE CURSOR GL (ktopl C(5), ktoks C(4) UNIQUE, vornr C(10), bisnr C(10), text30 C(30)) 
>  DIMENSION aa(4,m.rows/4)
>  INSERT INTO GL FROM ARRAY aa
> BROWSE LAST 
> 
> CREATE CURSOR Accounts (ktopl C(4), ktoks C(4), vornr C(10), company C(30))
> INSERT INTO Accounts VALUES ('CAPE', 'ADIC', '0025010012','(Name of a company)')
> INSERT INTO Accounts VALUES ('CAPE', 'ADIN', '0025000336','(Name of a unit)')
> INSERT INTO Accounts VALUES ('CAPE', 'ADIC', '0014390059','(Name of a company)')
> BROWSE LAST
> *If you make ktoks a Candidate column you won't be able to insert a second 'ADIC'
> 
> 
> SELECT T1.* FROM Accounts AS T1 ;
> LEFT JOIN GL AS T2 ;
> ON T1.ktopl=T2.ktopl AND T1.ktoks=T2.ktoks ;
>  AND T1.vornr BETWEEN T2.vornr AND T2.bisnr ;
> WHERE T2.vornr IS NULL ;
> INTO CURSOR Invalids
> BROWSE LAST
> ****

> This query will also return rows where ktopl or ktoks don't match the GL.
> Note: BETWEEN is inclusive.
>
> As there seems to be a many-to-one relationsship between 'ktopl' and 'ktoks' they should perhaps be split into two tables with a reference to ktops in Accounts as a foreign key.
> -Anders

I am sorry. It seems that I could not decribe my requirement clearly.

I would like to maintain a table record validation (or another) that a number range could be used once against ktopl and should not conflict with other record.

Regards,

Swarup Modak

ENTIRE THREAD

Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 3:00:15 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 3:10:23 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 6:53:47 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 9:06:53 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/31/2011 4:28:26 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/31/2011 1:23:16 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 12/31/2011 7:24:41 PM
RE: Check to prevent duplicate at Database Posted by tushar @ 1/1/2012 5:45:49 AM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/4/2012 2:51:18 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/4/2012 3:32:17 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/8/2012 8:16:37 AM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/8/2012 4:32:19 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/13/2012 8:55:58 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 1/13/2012 1:04:26 PM