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: Sanjay Sharma
  Where is Sanjay Sharma?
 Rishra
 India
 Sanjay Sharma
 Tags
Subject: RE: Check to prevent duplicate at Database
Thread ID: 331505 Message ID: 331532 # Views: 56 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 30, 2011 9:06:53 PM         
   


> >
> > Every, every!, absolutely each and every, table in a relational system must have a primary key, a unique key, in one or a combination of more columns.
> > What's the PK of your table?
> > Such a column is safeguarded by the system through a Primary Key index or a Candidate (Unique) index. See the commands CREATE TABLE and ALTER TABLE in the documentation. The MODIFY STRUCTURE window is a visible tool and can create those indexes too.
> > I'm sorry but I don't understand upper and lower limits in this scenario.
> > How do you maintain correctness of 'long text?
> >
> > -Anders
>
>
> 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

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