Oledbdataadapter, doing a left outer join give an error

How can I accomplish a left outer join with the Oledbdatadapter, I get an error when the statement is run !
ParanoidOneAsked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
By George, you are correct:

Things Your Mamma Never Told You
http://www.hop.man.ac.uk/staff/mpitcher/foxpro/cdxf3.html

<Quote>
Oh no, it's the dreaded Left Outer Join! This is an SQL construct which FoxPro doesn't currently support, so you have to simulate it using a union of two selects: one for all the records with matches in table B, and one for all the records without. Here's an example:

      SELECT a.keyfield,;
            a.datafield1,;
            b.datafield2,;
            b.datafield3;
      FROM a,b;
      WHERE a.keyfield = b.keyfield;
      UNION;

      SELECT a.keyfield,;
            a.datafield1,;
            000.00,;
            000.00;
      FROM a;
      WHERE a.keyfield NOT IN;

      (SELECT b.keyfield;
            FROM b)

A Couple of points:
The numeric and string constants in the second select (000.00 in this example) have to exactly match the length of the fields in the second database, because FoxPro requires both sides of a union to exactly the same structure.

set relation to and set skip to may be faster and easier if it's only a simple query.
</Quote>

Bob
0
 
Carl TawnSystems and Integration DeveloperCommented:
What error ? Whats the code ?
0
 
ParanoidOneAuthor Commented:
I coughs up an error as soon as you leave the Query Designer, basically saying the statement will not work. I tried the same statement using the sqldataadapter and it does not work. It is something about Foxpro and the ODBC and OLEDB not allowing a left outer join.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ParanoidOneAuthor Commented:
I am sorry, type there. the SQLDataAdapter DOES allow the statement.
0
 
Bob LearnedCommented:
What type of database are you accessing?

Bob
0
 
ParanoidOneAuthor Commented:
It is a set of DBF files, a directory containing about 10 of them. They are either Informix or Foxpro but normally the Foxpro setup seems to work fine, something like the OLEDbDataAdapter
0
 
Bob LearnedCommented:
Are you using the Visual FoxPro OleDb provider?

Bob
0
 
ParanoidOneAuthor Commented:
Yeah I have used both.
0
 
Bob LearnedCommented:
What does the SQL statement look like?

Bob
0
 
ParanoidOneAuthor Commented:
Something like this, but the field names being different of course. This simple statement will run on the SQL adapter but not with the Oledb. The error is "Command contains unrecognized phrase/keyword.

SELECT     table1.id, table1.type, table2.descript
FROM         { oj table1 LEFT OUTER JOIN
                      table2 ON table1.type = table2.type }
0
 
Bob LearnedCommented:
Did you try it like this:

SELECT     table1.id, table1.type, table2.descript
FROM         table1 LEFT OUTER JOIN
                      table2 ON table1.type = table2.type

Bob
0
 
ParanoidOneAuthor Commented:
THe designer will pop the other characters back into the statement and still pop out an error.
0
 
Bob LearnedCommented:
Does it run in the designer with those characters?

Bob
0
 
ParanoidOneAuthor Commented:
No.
0
 
Bob LearnedCommented:
I'm sorry, I meant to say, "Does it run in the designer without those characters?"

Bob
0
 
ParanoidOneAuthor Commented:
Nope, I tried that too.
0
 
ParanoidOneAuthor Commented:
BTW I think it will require a UNION to work. From some reading Foxpro does not allow a LEFT OUTER JOIN
0
 
ParanoidOneAuthor Commented:
That is the website I read it on. Unfortunately his example does not seem to work for me, well it does work just not as intended for my purposes. I am still trying some stuff though.
0
 
Bob LearnedCommented:
What kind of problems are you having with the UNION statement?

Bob
0
 
ParanoidOneAuthor Commented:
It appears to only give the information that is in both tables, instead of all of the information on the left. The end result I wanted was a left outer join.
0
 
Bob LearnedCommented:
That's where this comes into play:

SELECT a.keyfield,;
          a.datafield1,;
          000.00,;
          000.00;
     FROM a;
     WHERE a.keyfield NOT IN;
     (SELECT b.keyfield;
          FROM b)

Give me everything that is not in table b.

Bob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.