Help.!!!...Select....from....into 
Author Message
 Help.!!!...Select....from....into

Hi there,

I'm vfp 6.0 novice.
How can I consolidate fields from 2 or more tables into
one single new table without having those empty fields ??
Thank you in advance for your kind help!!

wing



Tue, 30 Aug 2005 06:07:24 GMT  
 Help.!!!...Select....from....into
Hi Wing,

I'm not sure what kind of consolidation you want.

Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
....
- or -

Field1                             Field2
Table1.Field1.Record1    Table1.Field2.Record1
...
Table1.Field1.RecordN    Table1.Field2.RecordN
Table2.Field1.Record1    Table2.Field2.Record1
......
--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> I'm vfp 6.0 novice.
> How can I consolidate fields from 2 or more tables into
> one single new table without having those empty fields ??
> Thank you in advance for your kind help!!



Tue, 30 Aug 2005 06:14:39 GMT  
 Help.!!!...Select....from....into

Hello Cindy,

I want a table which consolidates those non-empty fields
from N-Tables each of which has N-Fields(some of the
fields may or may not be empty) but having just one record
like  
Table1.Field1.Record1
...Table1.FieldN.Record1
.
Table2.Field1.Record1
...Table2.FieldN.Record1
.
.
.
TableN.Field1.Record1
...TableN.FieldN.Record1

I would like to know the SQL needed for the above
consoldation table which I can manipulate later.

Thank you Cindy,
Wing

Quote:
>-----Original Message-----
>Hi Wing,

>I'm not sure what kind of consolidation you want.

>Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
>.....
>- or -

>Field1                             Field2
>Table1.Field1.Record1    Table1.Field2.Record1
>....
>Table1.Field1.RecordN    Table1.Field2.RecordN
>Table2.Field1.Record1    Table2.Field2.Record1
>.......
>--
>Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP


http://cindywinegarden.adsl.duke.edu

- Show quoted text -

Quote:
>http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



>> I'm vfp 6.0 novice.
>> How can I consolidate fields from 2 or more tables into
>> one single new table without having those empty
fields ??
>> Thank you in advance for your kind help!!

>.



Tue, 30 Aug 2005 14:18:13 GMT  
 Help.!!!...Select....from....into
What do you mean non empty? NOT NULL ? is that what you talking about?
like source table
a b c null e null g h

and the resulting one looking like:
a b c e g h
?



Tue, 30 Aug 2005 19:57:02 GMT  
 Help.!!!...Select....from....into

Hi Iliya,

Yes,non empty means Not NULL.
Your presentation is exactly what I mean.

Thanks,
ing

Quote:
>-----Original Message-----
>What do you mean non empty? NOT NULL ? is that what you
talking about?
>like source table
>a b c null e null g h

>and the resulting one looking like:
>a b c e g h
>?

>.



Wed, 31 Aug 2005 02:20:23 GMT  
 Help.!!!...Select....from....into
SET NULLDISPLAY TO ""
-Anders


Quote:

> Hi Iliya,

> Yes,non empty means Not NULL.
> Your presentation is exactly what I mean.

> Thanks,
> ing

> >-----Original Message-----
> >What do you mean non empty? NOT NULL ? is that what you
> talking about?
> >like source table
> >a b c null e null g h

> >and the resulting one looking like:
> >a b c e g h
> >?

> >.



Wed, 31 Aug 2005 04:12:10 GMT  
 Help.!!!...Select....from....into

Hi again Iliya,

To be exact:
Like source table:
A.Fielda,b,null,d ; B.Fielde,null,g,h ; C.Fieldnull,j,null

then the resulting table:
A.Fielda,b,d ; B.Fielde,g,h ; C.Fieldj

Thanks again,
wing

Quote:
>-----Original Message-----
>What do you mean non empty? NOT NULL ? is that what you
talking about?
>like source table
>a b c null e null g h

>and the resulting one looking like:
>a b c e g h
>?

>.



Wed, 31 Aug 2005 04:25:01 GMT  
 Help.!!!...Select....from....into

Hello Anders,

I tried

SET NULLDISPLAY TO ""
Select * from tableA,tableB,tableC;
into tableD

But the resulting tableD still includes the null fields.

Wing

Quote:
>-----Original Message-----
>SET NULLDISPLAY TO ""
>-Anders



>> Hi Iliya,

>> Yes,non empty means Not NULL.
>> Your presentation is exactly what I mean.

>> Thanks,
>> ing

>> >-----Original Message-----
>> >What do you mean non empty? NOT NULL ? is that what you
>> talking about?
>> >like source table
>> >a b c null e null g h

>> >and the resulting one looking like:
>> >a b c e g h
>> >?

>> >.

>.



Wed, 31 Aug 2005 05:38:05 GMT  
 Help.!!!...Select....from....into
Wing,

How do you know how many columns to have in your result? You must remove a
whole column at a time in a VFP table.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> to be exact:
> Like source table:
> A.Fielda,b,null,d ; B.Fielde,null,g,h ; C.Fieldnull,j,null

> then the resulting table:
> A.Fielda,b,d ; B.Fielde,g,h ; C.Fieldj
> >What do you mean non empty? NOT NULL ? is that what you
> talking about?
> >like source table
> >a b c null e null g h

> >and the resulting one looking like:
> >a b c e g h
> >?



Wed, 31 Aug 2005 10:45:45 GMT  
 Help.!!!...Select....from....into
Wing,

You are making a "horizontal" table rather than a "vertical" one. I really
don't think you can do this with SQL. The best I can think of is to step
through the FIELDS() array and test to see if the value is null and then
append or not to an array where you collect the values.

Can you describe what you are going to do with the data? Maybe there's
another way to accomplish your goal.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> I want a table which consolidates those non-empty fields
> from N-Tables each of which has N-Fields(some of the
> fields may or may not be empty) but having just one record
> like
> Table1.Field1.Record1
> ...Table1.FieldN.Record1
> .
> Table2.Field1.Record1
> ...Table2.FieldN.Record1
> .
> .
> .
> TableN.Field1.Record1
> ...TableN.FieldN.Record1

> I would like to know the SQL needed for the above
> consoldation table which I can manipulate later.



Wed, 31 Aug 2005 10:49:32 GMT  
 Help.!!!...Select....from....into
SET NULLDISPLAY TO "" doesn't remove the NULLs, it changes the way NULL is
DISPLAYed. You can SET NULLDIPLAY TO "Unknown" or 'Missing' or "No data"
You can use the NVL() function in the query itslef to replace a NULL with
anything else of the correct data type for the column.

-Anders


Quote:

> Hello Anders,

> I tried

> SET NULLDISPLAY TO ""
> Select * from tableA,tableB,tableC;
> into tableD

> But the resulting tableD still includes the null fields.

> Wing

> >-----Original Message-----
> >SET NULLDISPLAY TO ""
> >-Anders



> >> Hi Iliya,

> >> Yes,non empty means Not NULL.
> >> Your presentation is exactly what I mean.

> >> Thanks,
> >> ing

> >> >-----Original Message-----
> >> >What do you mean non empty? NOT NULL ? is that what you
> >> talking about?
> >> >like source table
> >> >a b c null e null g h

> >> >and the resulting one looking like:
> >> >a b c e g h
> >> >?

> >> >.

> >.



Wed, 31 Aug 2005 17:51:04 GMT  
 Help.!!!...Select....from....into
Cindy,

Your are right that I am making a "horizontal" table.
The resulting table(containing only filled fields)will
then be sent to either a query report or graph.
By the way,the legend in the graph shows only up to 4,
cannot see beyond that number of fields!!! any tatic??

Could you give me a procedure command for your suggestion
to step through the null testing of FIELD()...??

Many thanks,
wing

Quote:
>-----Original Message-----
>Wing,

>You are making a "horizontal" table rather than

a "vertical" one. I really
Quote:
>don't think you can do this with SQL. The best I can

think of is to step
Quote:
>through the FIELDS() array and test to see if the value
is null and then
>append or not to an array where you collect the values.

>Can you describe what you are going to do with the data?
Maybe there's
>another way to accomplish your goal.

>--
>Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP


http://cindywinegarden.adsl.duke.edu
Quote:
>http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



>> I want a table which consolidates those non-empty fields
>> from N-Tables each of which has N-Fields(some of the
>> fields may or may not be empty) but having just one
record
>> like
>> Table1.Field1.Record1
>> ...Table1.FieldN.Record1
>> .
>> Table2.Field1.Record1
>> ...Table2.FieldN.Record1
>> .
>> .
>> .
>> TableN.Field1.Record1
>> ...TableN.FieldN.Record1

>> I would like to know the SQL needed for the above
>> consoldation table which I can manipulate later.

>.



Thu, 01 Sep 2005 03:51:08 GMT  
 Help.!!!...Select....from....into
Cindy,
I have only one (no more than one) record for each
horizontal table. Any null field will be removed together
with that one record and therefore the resulting table
will have accountable columns at the end hopefully.

Quote:
>-----Original Message-----
>Wing,

>How do you know how many columns to have in your result?
You must remove a
>whole column at a time in a VFP table.

>--
>Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP


http://cindywinegarden.adsl.duke.edu
Quote:
>http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



>> to be exact:
>> Like source table:
>> A.Fielda,b,null,d ; B.Fielde,null,g,h ;
C.Fieldnull,j,null

>> then the resulting table:
>> A.Fielda,b,d ; B.Fielde,g,h ; C.Fieldj

>> >What do you mean non empty? NOT NULL ? is that what you
>> talking about?
>> >like source table
>> >a b c null e null g h

>> >and the resulting one looking like:
>> >a b c e g h
>> >?

>.



Thu, 01 Sep 2005 04:03:30 GMT  
 Help.!!!...Select....from....into
Hi Wing
If you remove fields with nulls and put all the other fields into a single
record, how will you know which fields the values belong to? What is an
accountable column? Are these numeric fields?. Do you know that NULL values
are automatically excluded in statistical computations?
-Anders


Quote:
> Cindy,
> I have only one (no more than one) record for each
> horizontal table. Any null field will be removed together
> with that one record and therefore the resulting table
> will have accountable columns at the end hopefully.

> >-----Original Message-----
> >Wing,

> >How do you know how many columns to have in your result?
> You must remove a
> >whole column at a time in a VFP table.

> >--
> >Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://cindywinegarden.adsl.duke.edu
> >http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> >> to be exact:
> >> Like source table:
> >> A.Fielda,b,null,d ; B.Fielde,null,g,h ;
> C.Fieldnull,j,null

> >> then the resulting table:
> >> A.Fielda,b,d ; B.Fielde,g,h ; C.Fieldj

> >> >What do you mean non empty? NOT NULL ? is that what you
> >> talking about?
> >> >like source table
> >> >a b c null e null g h

> >> >and the resulting one looking like:
> >> >a b c e g h
> >> >?

> >.



Fri, 02 Sep 2005 03:21:02 GMT  
 
 [ 14 post ] 

 Relevant Pages 

1. +++GET YOUR FREE PENTIUM CELERON-300+++____________________________________________________________________________________________w5eoiu5ourerktjw erhtkjw885 woiwr oiwre

2. =?NS_4551-1?Q?Running_multiple_=28or_at_least_two=29_instances?= =?NS_4551-1?Q?_of_FoxPro_2=2E6w_screen?=

3. Help Help Help PLEASE!!!!!!!!!!!!!!!!!!!!!!!!!!

4. US-Nationwide-ORACLE-Ingres-Ingres-PowerBuilder-Progress-SYBASE-SQL-Developer 20

5. needed Immediately Plant Manager:!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

6. help me help me hhhhhhhheeeeeeeelllllllpppppppp meeeeeeeee

7. HELP-VisFox6-Query-Line-Too-Long

8. Pls help me !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

9. INSUFFICIENT MEMORY HELP ?????????????????????????????????

10. Cancelling Queries --- HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

11. Please help me !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

 
Powered by phpBB® Forum Software