SQL Command using 2 database locations or do I need a custom data source?

[campagnolo1]

Hello everyone,

I'm trying to figure out how to write a SQL statement in the Add Command box that will let me query tables from 2 different data sources. I have been chasing my tail for the better part of the day with no real success. I'm not even sure if it can be done in CR or if I need a custom Data Source that includes both locations (not even sure if THAT can be done).
SO as an example:
Tables for Datasource 1 are in C:\Datasources\Datasource1Tables for Datasource 2 are in C:\Datasources\Datasource2
Select statement maybe something like this:

SELECT table1.field
FROM table1
IN "C:\Datasources\Datasource1" "Datasource1;"
WHERE table1.field Like "A*"

That already gives me an error, so I can't even continue with a presumed UNION or UNION ALL.

I have tried a few more syntaxes, but each one has given me an "Unrecognized Command" error.
Can anybody help me out here and give me a hint how I can accomplish my task? Unfortunately there is no way to combine the 2 data locations as our accounting/manufacturing software dictates the setup.

Any help is greatly appreciated!

Chris

 

[Turkbear]

I have usually found that creating a view in one datasource that incorporates the other datasource is easier to use with Crystal, so if you can do that in your databaae type give it a try.

With that method only one datasource needs to be accessed by your SQL in Crystal.
If not, is a SQL command absolutely the only way to get the results that you need ?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

Commands only work with databases

What is your data source?

As Turkbear says if you want to link data from two databases you must link the databases and not try to do via Crystal

Assuming you provide a link from datasource one your query could be

SELECT table1.field
FROM table1
WHERE table1.field Like "A*"
union all
SELECT T2.field
FROM [Server2].[scheme2].table2 T2
WHERE T2.field Like "A*"

If your datasources are excel spread sheets then you will need to use Subreports to merge data.

Ian

 

[campagnolo1]

Thank you for the quick responses!

The data sources are both VFP free table directories. Each of the two directories basically holds about 20 or so tables.

@Turkbear:

How would I create a "view"? I will try to find some examples and hopefully figure it out, but I'd appreciate it if you could elaborate some more.

Cheers,

Chris

 

[campagnolo1]

Well,

after not much luck with the Views (I kept getting the message that there were no tables in the directory) I messed around some more with the syntax for the query, and I had some success:

SELECT Slheader.docid, Slheader.billto, Slheader.coname, Sllines.item,
  Sllines.descrip, Bmfgcfg.aitem
FROM 
F:\TIW\KOKAC\slheader.dbf
LEFT OUTER JOIN F:\TIW\KOKAC\sllines.dbf 
ON  Slheader.docid = Sllines.docid 
LEFT OUTER JOIN F:\TIW\KOKWS\bmfgcfg.dbf
ON  Sllines.item = Bmfgcfg.aitem;

That actually works, but it is slow as can be. It may just be the size of the tables (it produces almost 750k records) so I will try some more. If anyone has any more suggestions, feel free to chime in!

Cheers,

Chris