Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Michael Landrup
  Where is Michael Landrup?
 
 Vietnam
 Michael Landrup
 Tags
Subject: RE: how to synthetic data?
Thread ID: 345281 Message ID: 345436 # Views: 40 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, May 23, 2012 11:29:30 AM         
   


> >
> > Would you give data that generates that result.
> >
> >

> > Cetin Basoz
> >
> > .Net has got better.Think about moving - check my blog:
> > My Blog
> > Blog (mirror) - sounds to be down
> >

> > Support Wikipedia
>
>
>
> create cursor cn1 (code I, value C(1))
> insert into cn1 values (1, 'A')
> insert into cn1 values (2, 'B')
> insert into cn1 values (3, 'C')
> insert into cn1 values (4, 'D')
> insert into cn1 values (5, 'E')
>
> create cursor cn2 (code I, value C(1))
> insert into cn2 values (1, 'A')
> insert into cn2 values (2, 'F')
> insert into cn2 values (3, 'C')
> insert into cn2 values (4, 'D')
> insert into cn2 values (5, 'E')
>
> create cursor cn3 (code I, value C(1))
> insert into cn3 values (1, 'A')
> insert into cn3 values (2, 'F')
> insert into cn3 values (3, 'C')
> insert into cn3 values (4, 'D')
> insert into cn3 values (5, 'E')
>
> Select Nvl(Nvl(cn1.Code,cn2.Code),cn3.Code) As 'code', ;
> concat( cn1.Value, cn2.Value, cn3.Value ) As 'value' ;
> from cn1 ;
> full Join cn2 On cn1.Code = cn2.Code or cn2.Code = cn3.Code ;
> full Join cn3 On cn2.Code = cn3.Code or cn1.Code = cn3.Code
>
> Procedure Concat(tcVal1, tcVal2, tcVal3)
> tcVal1 = Nvl(m.tcVal1,' ')
> tcVal2 = Nvl(m.tcVal2,' ')
> tcVal3 = Nvl(m.tcVal3,' ')
>
> Return Cast(Chrtran(Alltrim(Trim(m.tcVal1) + ;
> Iif( m.tcVal2 = m.tcVal1 or m.tcVal2 = ' ', '', ' '+Trim(m.tcVal2) ) + ;
> Iif( m.tcVal3 = m.tcVal2 Or m.tcVal3 = m.tcVal1 or m.tcVal3 = ' ', '', ' '+Trim(m.tcVal3) )),' ','/') As c(5))
> Endproc
>
>
>
> and i said more: the number of my table more than 3 table (interval 60 table match with 60 branch). Please help me!

Select Nvl(Nvl(cn1.Code,cn2.Code),cn3.Code) As 'code', ;
concat( cn1.Value, cn2.Value, cn3.Value ) As 'value' ;
from cn1 ;
full Join cn2 On cn1.Code = cn2.Code ;
full Join cn3 On cn1.Code = cn3.Code or (cn1.Code is null and cn2.Code = cn3.Code)

Procedure Concat(tcVal1, tcVal2, tcVal3)
tcVal1 = Nvl(m.tcVal1,' ')
tcVal2 = Nvl(m.tcVal2,' ')
tcVal3 = Nvl(m.tcVal3,' ')

Return Cast(Chrtran(Alltrim(Trim(m.tcVal1) + ;
Iif( m.tcVal2 = m.tcVal1 or m.tcVal2 = ' ', '', ' '+Trim(m.tcVal2) ) + ;
Iif( m.tcVal3 = m.tcVal2 Or m.tcVal3 = m.tcVal1 or m.tcVal3 = ' ', '', ' '+Trim(m.tcVal3) )),' ','/') As c(5))
Endproc


With 60 tables since VFP doesn't have something like .Net's Linq for custom aggregation probably I would go for xbase:

Select * From cn1 ;
	union ;
	Select * From cn2 ;
	union ;
	Select * From cn3 ;
	order By 1,2 ;
	into Cursor crsAll ;
	nofilter
Create Cursor crsResult (Code I, Value C(120))
Local oRecord
Select crsAll
Scan
	Scatter Name oRecord
	Skip
	Scan While Code = m.oRecord.Code
		m.oRecord.Value = m.oRecord.Value + '/' + Value
	Endscan
	Insert Into crsResult From Name m.oRecord
	Skip -1
Endscan
Select crsResult
Browse




Cetin Basoz

.Net has got better.Think about moving - check my blog:
My Blog
Blog (mirror) - sounds to be down


Support Wikipedia

ENTIRE THREAD

how to synthetic data? Posted by Michael Landrup @ 5/22/2012 6:24:11 AM
RE: how to synthetic data? Posted by Anders Altberg @ 5/22/2012 12:46:23 PM
RE: how to synthetic data? Posted by Cetin Basoz @ 5/22/2012 1:45:29 PM
RE: how to synthetic data? Posted by Michael Landrup @ 5/22/2012 4:11:34 PM
RE: how to synthetic data? Posted by Cetin Basoz @ 5/22/2012 7:04:29 PM
RE: how to synthetic data? Posted by Michael Landrup @ 5/23/2012 6:30:58 AM
RE: how to synthetic data? Posted by Cetin Basoz @ 5/23/2012 11:29:30 AM
RE: how to synthetic data? Posted by onytoo @ 5/22/2012 6:20:15 PM
RE: how to synthetic data? Posted by Michael Landrup @ 5/23/2012 4:21:42 AM
RE: how to synthetic data? Posted by Anders Altberg @ 5/23/2012 8:25:22 PM
RE: how to synthetic data? Posted by Michael Landrup @ 5/24/2012 5:21:22 AM