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!

[vishsaggi]

Can you try your SQL script in your database environment and see what error you are getting? If you getting error in your SQL environment change the script to run and if that works just copy the same script in Qlikview script editor and check.

[ccb-it2013]

HI Sasidhar,

This table contains the field "PONO":

This table contains the field "TICKET":

for some reason I still get an error. The names are good...

[ccb-it2013]

They are not SQL databases, they are VIsual FoxPro 9 databases...you see that the following line in my script:

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

[ccb-it2013]

I theory the script should still work if I remove the SQL line, but even if I do that the script errors out.

I thought it just a syntax error somewhere in my script or I am not using the correct command to get the data I need...

[sasiparupudi1]

Just do this

Connect your db

Table1:

Sql select * from table1;

Table2:

Sql select * from table2;

Do you see 2 tables in qv? Do they contain the columns you see in your db?

[ccb-it2013]

Hi Sasidhar,

When I try:

Sql select * from adoconmanpo_h

All I get the hourglass from QV....

If I do the command:

Select * from adoconmanpo_h

QV likes that:

[ccb-it2013]

If I tried with the other dbf, and that select command works as well.

If I try having both select commands in the script, i get an error.

I had expected QV to not like that....

[vishsaggi]

Try this once :

ADOCONMANPO_H:
LOAD *;
SELECT
adoh.Ticket,
adoh.Style,
adoh.Vendor,
adoh.Mfcode,
adoh.duedate
FROM ADOCONMANPO_h adoh;


LEFT JOIN(ADOCONMANPO_H)
ADOPLINS:
LOAD *;
SELECT
adoplins.Account,
adoplins.Color,
adoplins.desc,
adoplins.linetot
adoplins.shipno,
adoplins.PONO AS Ticket
FROM ADOPOLINS adoplins;

[vishsaggi]

Did you terminate your first Select statement with ; semicolon?

[ccb-it2013]

Yes sir, I did.

Ran your script above and the hourglass is still turning..