How do a join with 2 dbf files?

[ccb-it2013]

Hi there,

For the life of me I can't join 2 dbf files so that I can get the necessary data I need from the second database.

In my script I have this:

SELECT * FROM ADO CONMANPO_H

The name of the second dbf I need to get data from is called say CONMANPO_D . They field they have in common is called PONO.

I tried this:

LEFT JOIN CONMANPO_H on  PONO as CONMANPO_D

However I get a script error when I try running the script.

What am I doing wrong?? I am sure it is something really simple.

Thanks for your help!

[ccb-it2013]

Hi Sasidhar,

For some reason what you suggested does not work:

The database files are Visual FoxPro 9.x.

Is there something I am not understanding?

Thanks!

[vishsaggi]

Are you still having issues joining these tables? Can you paste your actual script where you getting this error?

[ccb-it2013]

Hi Vishwarath,

This is the script I have:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=G:\AIMS\QVDATA\CMP4\;Collating Sequence=general;];

SELECT * FROM ADOCONMANPO_h

SELECT * FROM ADOCONMANPO_h ADOPOLINS

LEFT JOIN

ADOCONMANPO_H ADOPOLINS

ON ADOCONMANPO_H.PONO=ADOPOLINS.PONO

This is error I get:

This table 1:

This is the table I need join to:

Ticket in table 1 contains the same reference in table 2 under field PONO

What I need is to be able access the fields in table 2 for the report.

What am I doing wrong? I understand to join two tables, you need a common reference or key.

Please advise.

Thanks!

[vishsaggi]

NO this is not how you do a join. Try below and you have to mention what fields you want to pull from both the tables. If you have field which are similar in both the tables you need to specify from which table you want that common field to be displayed.

And are you trying to do a self join here? If yes you have to mention the fields appropriately. Can you provide what fields you want to display in your final report. Your script says you are joining the same table.

[ccb-it2013]

Hi Vishwarath,

Thank you for your patience and guidance. Much appreciated.

From the first table I need:

Ticket, Style, Vendor, Mfcode, duedate

From the second table :

Account, Color, desc, linetot (quantity of color), Shipno

[vishsaggi]

Ok. one last thing what is the table name for First table and the table name for Second table?

[ccb-it2013]

The first table name is ADOCONMANPO_h

The second table name is ADOPOLINS

Many thanks!

[vishsaggi]

Try this! If I did not enter the table names try to change accordingly.

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=G:\AIMS\QVDATA\CMP4\;Collating Sequence=general;];
SQL
SELECT adoh.Ticket,
adoh.Style,
adoh.Vendor,
adoh.Mfcode,
adoh.duedate,
adoplins.Account,
adoplins.Color,
adoplins.desc,
adoplins.linetot
adoplins.shipno
FROM ADOCONMANPO_h adoh
LEFT JOIN
ADOPOLINS adoplins
ON adoh.Ticket = adoplins.PONO;

[ccb-it2013]

HI VIshwarath,

The names are good, but I am still getting the same error...

[sasiparupudi1]

May be there is no field called pono?

In your previous replies you said code ran well with the join

ON adoh.TICKET = adod.TICKET  ?

May be change

ON adoh.Ticket = adoplins.PONO to above stmt