odbc outer joins in infomix
Dominic Salvia
joins in it using the {oj {oj table LEFT OUTER JOIN... syntax. The query I
have runs under MS SQL 2K but fails with an "[Informix][Informix ODBC
Driver][Informix]A syntax error has occurred." message under informix. The
query looks a little like this:
SELECT Table1.col1, Table2.col1, Table3.col1
FROM {oj {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1}
LEFT OUTER JOIN Table3 ON Table2.col1 = Table3.col1}
anyone have any ideas?
Thanks,
Dominic Salvia
David Bowman
I think the syntax you are using is not correct for Informix. You should
do something like this:
SELECT Table1.col1, Table2.col1, Table3.col1
FROM Table1, OUTER Table2, OUTER Table3 ON
WHERE Table1.col1 = Table2.col1
AND Table2.col1 = Table3.col1
Hope it works...
Take a look at this and excuse me for my poor english.... :)
Extract from Informix Documentation
=========================
Simple Outer Join on Two Tables
----------------------------------
Query 3-22 uses the same select list, tables, and comparison condition as
the
preceding example, but this time it creates a simple outer join.
Query 3-22
SELECT c.customer_num, c.lname, c.company,
c.phone, u.call_dtime, u.call_descr
FROM customer c, OUTER cust_calls u
WHERE c.customer_num = u.customer_num
The addition of the keyword OUTER before the cust_calls table makes it the
subservient table. An outer join causes the query to return information on
all
customers, whether or not they have made calls to customer service. All rows
from the dominant customer table are retrieved, and null values are assigned
to columns of the subservient cust_calls table,
Outer Join for a Simple Join to a Third Table
---------------------------------------------
Query 3-23 shows an outer join that is the result of a simple join to a
third
table. This second type of outer join is known as a nested simple join.
Query 3-23
SELECT c.customer_num, c.lname, o.order_num,
i.stock_num, i.manu_code, i.quantity
FROM customer c, OUTER (orders o, items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname
Query 3-23 first performs a simple join on the orders and items tables,
retrieving information on all orders for items with a manu_code of KAR or
SHM. It then performs an outer join to combine this information with data
from the dominant customer table.
Outer Join of Two Tables to a Third Table
-------------------------------------------
Query 3-24 shows an outer join that is the result of an outer join of each
of two
tables to a third table. In this third type of outer join, join
relationships are
possible only between the dominant table and the subservient tables.
Query 3-24
SELECT c.customer_num, lname, o.order_num,
order_date, call_dtime
FROM customer c, OUTER orders o, OUTER cust_calls x
WHERE c.customer_num = o.customer_num
AND c.customer_num = x.customer_num
ORDER BY lname
INTO TEMP service
Query 3-24 individually joins the subservient tables orders and cust_calls
to
the dominant customer table; it does not join the two subservient tables. An
INTO TEMP clause selects the results into a temporary table for further
manipulation or queries.
Joins That Combine Outer Joins
---------------------------------
To achieve multiple levels of nesting, you can create a join that employs
any
combination of the three types of outer joins. Query 3-26 creates a join
that is
the result of a combination of a simple outer join on two tables and a
second
outer join.
Query 3-26
SELECT c.customer_num, lname, o.order_num,
stock_num, manu_code, quantity
FROM customer c, OUTER (orders o, OUTER items i)
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
AND manu_code IN ('KAR', 'SHM')
ORDER BY lname
Query 3-26 first performs an outer join on the orders and items tables,
retrieving information on all orders for items with a manu_code of KAR or
SHM. It then performs a second outer join that combines this information
with data from the dominant customer table. Query 3-26 preserves order
numbers that the previous example eliminated, returning rows for orders
that do not contain items with either manufacturer code.
Dominic Salvia <d.sa...@ntlworld.com> escribió en el mensaje de noticias
9gcplj$b0k$1...@reader-00.news.insnet.cw.net...
Andrew Hamm
<9gcplj$b0k$1...@reader-00.news.insnet.cw.net>...
approximately 7.21 or 7.23. It will be in the release notes of the
installation directory.
--
When I grow up, I want to be a Principal or a caterpillar!
- Ralph Wiggam
.. ... .--. .. - --- -. --- .-. .- -.-. .-.. .
Joe
do them in ODBC and the ODBC driver is supposed to create a query that is
compatible with the database engine being used, so you don't have to write
the query one way for Informix and another way for Oracle and another way
for MS SQL.
For example, if you execute this through ODBC:
SELECT Table1.col1, Table2.col1
FROM {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1}
When the MS SQL Server driver gets this, it translates it to the SQL Server
syntax:
SELECT Table1.col1, Table2.col1
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1
When the Informix driver gets it, it translates it to:
SELECT Table1.col1, Table2.col1
FROM Table1 OUTER Table2 WHERE Table1.col1 = Table2.col1
When the Oracle driver gets it, it translates it to:
SELECT Table1.col1, Table2.col1
FROM Table1, Table2 (+) WHERE Table1.col1 = Table2.col1
(at least I think that's right for Oracle - my Oracle is a little rusty).
"David Bowman" <tri...@yahoo.com> wrote in message
news:newscache$t6zyeg$by3$1...@news.si.soluziona.com...
Joe
(native) format for Informix.
"Andrew Hamm" <NOaha...@sanderson.net.au> wrote in message
news:9gd464$8c7ic$1...@ID-79573.news.dfncis.de...
Manuel Daponte
SELECT c.customer_num, lname, o.order_num,
order_date, call_dtime
FROM customer c, OUTER orders o, OUTER cust_calls x
WHERE c.customer_num = o.customer_num
AND c.customer_num = x.customer_num
ORDER BY lname
"Dominic Salvia" <d.sa...@ntlworld.com> wrote in message
news:9gcplj$b0k$1...@reader-00.news.insnet.cw.net...
Andrew Hamm
>Shouldn't matter. The ODBC driver should be translating it into the
correct
>(native) format for Informix.
>
OUTER JOIN is new to informix... Perhaps the ODBC driver he has is new, and
is attempting to use that, but the engine doesn't know about it because he's
on an older engine? Just wild speculation in the area of version
compatibility...
Andrew Hamm
>From the Informix SQL Tutorial manual:
>
>SELECT c.customer_num, lname, o.order_num,
>order_date, call_dtime
>FROM customer c, OUTER orders o, OUTER cust_calls x
>WHERE c.customer_num = o.customer_num
>AND c.customer_num = x.customer_num
>ORDER BY lname
>
OUTER'. You won't be disappointed ;^)
Dominic Salvia
to do is alias the tables and reuse them in multiple {oj... 's for example:
SELECT t1.col1, t2a.col1, t3.col1
FROM {oj Table1 t1 LEFT OUTER JOIN Table2 t2a ON t1.col1 = t2a.col1},
{oj Table2 t2b LEFT OUTER JOIN Table3 t3 ON t2b.col1 = t3.col1}
This is because (I'm informed) of the way informix deals with the results of
outer joins and that it only gets the results of the join and doesn't create
a temporary table which other joins can be performed on (I think...).
Thanks for taking the time to look at this for me and I hope this solution
can help others trying to do the same as me.
Dominic Salvia
"Dominic Salvia" <d.sa...@ntlworld.com> wrote in message
news:<9gcplj$b0k$1...@reader-00.news.insnet.cw.net>...
David Bowman
Anybody know where can I found some info on ODBC syntax?
Thanks,
Joe <j...@noneofyourbiz.com> escribió en el mensaje de noticias
mdzW6.209$fd.1...@newshog.newsread.com...
Andrew Hamm
>Thanks for the help guys but I've now got the way round it. what you have
>to do is alias the tables and reuse them in multiple {oj... 's for example:
>
>SELECT t1.col1, t2a.col1, t3.col1
>FROM {oj Table1 t1 LEFT OUTER JOIN Table2 t2a ON t1.col1 = t2a.col1},
>{oj Table2 t2b LEFT OUTER JOIN Table3 t3 ON t2b.col1 = t3.col1}
>
>This is because (I'm informed) of the way informix deals with the results
of
>outer joins and that it only gets the results of the join and doesn't
create
>a temporary table which other joins can be performed on (I think...).
>
point to where it's written down? I would expect Informix to handle your
query when written natively. And, if it can't do the query directly, it will
resort to the use of an internal temp table to pre-stage some of the data.
Your statement really surprises me although I won't dispute that you now
have a sucessful ODBC query;-)
Exactly what does the {oj .... } syntax mean in ODBC?
Dominic Salvia
think what I wrote before was more or less the what he was saying, but the
guy is a little crazy and sometimes makes not much sense. As for the {oj...}
syntax it's the outer join escape syntax for under odbc. I'm not much of an
odbc geek and when discussing how to make our ms sql multiplatform someone
along the line came up with this fantastic {oj etc.} stuff to use. you use
it and no matter what database you connect to you end up with a successful
outer join (apparently) and it seems to work quite well :) works with ms
sql, informix and oracle (that I've tested) and can also be used in jdbc
queries.
Dominic Salvia
Andrew Hamm
>I got the info from another guy in the office who was wandering past. I
>think what I wrote before was more or less the what he was saying, but the
>guy is a little crazy and sometimes makes not much sense.
>
>As for the {oj...}
>[SNIP]
>
Interesting - i'll try to keep that in the braincells for some indefinite
point in the future ;-)
--
I'd always thought music was too formal, and I
thought "well, I'll get into this and fix it"
- Don Van Vliet