unique data from two tables

[astech]

Hi all,

1. what is the default of empty datetime format ?

2. eg.
I have 2 tables
TableA( id c(6)) and TableB( id c(6)).
Unique id format is like 'A99999'

How to make an unique id from combining of that two tables, so there is not an duplicate id from both tables ?

Thanks

 

[ramani]

Hi Astech

1. what is the default of empty datetime format ?
{}

2. I am not clear as to your question..

Try this for getting only unique records in the combined set..

SELECT * FROM tableA ;
UNION SELECT * FROM TableB ;
INTO CURSOR temp
BROW

The union will select only the unique records in the combined cursor. Make sure you are not mentioning UNION ALL.. since ALL will make the combined to fetch all records.

If you are talking about uniqe field to be genrated from the tablea and tableb then TableA.id+TableB.id is the answer.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[astech]

Thnks Mr. Ramani, this is my clearly of my question.

I have 2 tables named TrxAir and TrxSea.
In the Transaction I have 2 Forms like :
Form "Air Transaction" using Table TrxAir and "Sea Transaction" using TrxSea.

this is my listing.


*** Info ********************************************************************
*** Sea Transaction Form
*** ID is like J99999 , 'J' Means 'January' adding counter '99999'
***
*****************************************************************************

*** Creating temporary Data

Select cnoid From TrxAir Into Cursor crsAir Readwrite && (with 5000 data(s))
Select cnoid From TrxSea Into Cursor crsSea Readwrite && (with 5000 data(s))

Select crsSea
Append From Dbf('crsAir')


*** Combining temporary Data counter to max Number
Select crsSea
Calculate Max(Right(crsSea.cnoid,5)) To nxmax

cxMax= cxMOnth+Right('00000'+Alltrim(Transform(Val(nxmax)+1,'99999')),5)

*** Closing Temporary Data
USE IN crsSea
USE IN CrsAir


Select TrxSea
Append Blank
Replace cNoID With cxMax
=TABLEUPDATE(.T.)

**************************************************************************
* This is also same with "Air Transaction" Form.
**************************************************************************

My Questions are :

1. with all 10.000 datas there is some times has duplicate datas.
So why that's happend ?, because i already counting and adding max +1 as new record ?.

2. is the way to combining data :

SELECT * FROM TrxAir UNION SELECT * FROM TrxSea INTO CURSOR temp

is faster than .

Select cnoid From TrxAir Into Cursor crsAir Readwrite && (with 5000 data(s))
Select cnoid From TrxSea Into Cursor crsSea Readwrite && (with 5000 data(s))
Select crsSea
Append From Dbf('crsAir')

If faster i will use that.

Thanks for your answer Mr. Ramani.


* Sorry i'm just dummy and sorry form my bad language, couse i'm not englishman

 

[ramani]

HI

1. The reason you get duplicate numbers generated is likely that when one user, obtains the nextId and then writes the record, at the same time, another user might try and do the same.. thus both ending with same number.

2. The better way to do the same job is..
*************************************************
*** Creating temporary Data

IF !FILE("idTable")
** First time create idTable and
** insert the next record
CREATE TABLE idTable (IdNo I)
INSERT INTO idTable (IdNo) VALUES (1)
** In your case..
** please insert the maxNum+1 in the table first time
ENDIF
IF !USED('idTable')
USE idTable IN 0 ALIAS idTable
LOCATE IN idTable
ENDIF

IF RLOCK('idTable')
myNextId = idTable.IdNo
REPLACE idTable.IdNo WITH myNextId+1
UNLOCK IN idTable
ENDIF

Select TrxSea && or TrxAir
Append Blank
Replace cNoID With ;
cxMOnth+Right('00000'+Alltrim(STR(myNextId)),5)
=TABLEUPDATE(.T.)



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[astech]

Thanks Mr. Ramani for your solution.
May be my mistake, is in not using lock.

how about this way below ?
My reason is we adding new data when idTable is in Lock.
where is the best way like the first.

IF RLOCK('idTable')
myNextId = idTable.IdNo
REPLACE idTable.IdNo WITH myNextId+1

***************************
Select TrxSea && or TrxAir
Append Blank
Replace cNoID With ;
cxMOnth+Right('00000'+Alltrim(STR(myNextId)),5)
=TABLEUPDATE(.T.)
***************************

UNLOCK IN idTable
ENDIF

 

[ramani]

Your code is fine except that it locks a little more time than necessary since extra execution code is there before the unlock. This delay to next user for accessing idTable can be avoided if the UNLOCK command precedes the other append etc.. codes. Once the value is obtained, that value will not be available for next user.

Again, I just copied whatever code you used earlier. However, I would not do APPEND BLANK and then REPLACE.. The reason is (1) SPEED and (2)that in a table with primary key.. if 2 people append together, the value could get duplicated for primary key. To avoid this, I suggest..

***************************
IF RLOCK('idTable')
myNextId = idTable.IdNo
REPLACE idTable.IdNo WITH myNextId+1
UNLOCK IN idTable
ENDIF
Select TrxSea && or TrxAir
INSERT INTO TrxSea (cNoId) VALUES ;
(cxMOnth+Right('00000'+Alltrim(STR(myNextId)),5))
=TABLEUPDATE(.T.)
***************************



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[astech]

Thanks for all your kindess to help me.
Sorry, i'm just dummy.