Hi,
Has anyone managed to successfully set up a universe for an SQLAnywhere 5.0
database?
The generic ODBC support provided by BO doesn’t allow for outer joins. I
have tried modifying the generic ODBC parameter file
(\BusinessObjects\odbc\odbc10en.prm) supplied with BO with limited success:
I can now represent outer joins in the universe but invalid SQL is generated
when I report on it. I’ve included more details below.
Thanks in advance!
Greg Nockolds
Changes made to the odbc10en.prm file:
First change: EXT_JOIN=NO to EXT_JOIN=YES
This allowed me to define outer joins in the universe but had no effect on
the generated SQL.
Second change: OUTERJOINS_GENERATION=NO to OUTERJOINS_GENERATION=ODBC
Outer join syntax did get generated, but it is not valid for SQLAnywhere.
Example:
SELECT
DCT_Division.description,
DCT_Team.description
FROM
ECM_SC.sc_code_tree DCT,
ECM_SC.sc_code_tree DCT_Division,
ECM_SC.sc_code_tree DCT_Team,
{ oj DCT_Team RIGHT OUTER JOIN DCT_Division ON
DCT_Team.parent_code=DCT_Division.sc_code_no }
WHERE
( DCT.sc_code_no=‘8062’ )
AND ( DCT.sc_code_no=DCT_Division.parent_code )
which results in the following error:
[Sybase][ODBC Driver]Table or view not found: table ‘DCT_Division’ not
found-141
contents of my odbc10en.prm file:
[RDBMS]
(GENERAL)
SORT_BY_NO=YES
GROUPBYCOL=NO
EXT_JOIN=YES <=== First changed this one
CONCAT=+
STG= stodbcen
UNION=
INTERSECT=
MINUS=
OWNER=Y
QUALIFIER=Y
COMMA=+’ '+
NO_DISTINCT=Y
REFRESH_COLUMNS_TYPE=T
CHECK_OWNER_STATE=Y
CHECK_QUALIFIER_STATE=Y
KEY_INFO_SUPPORTED=N
OUTERJOINS_GENERATION=ODBC <==== then changed this one
Different databases handle outer join in different way. Oracle, Sybase
handles outer joins in where clauses.
ODBC, Db2 handle it in From clause (as you have specified in your mail)
Now the question is How does it happen in SQLAnywhere.
You may have to look at the parameter OUTERJOINS_GENERATION=ODBC
Try to find if you can specify any other value (You might have
Hi,
Has anyone managed to successfully set up a universe for an SQLAnywhere
5.0
database?
The generic ODBC support provided by BO doesn’t allow for outer joins. I
have tried modifying the generic ODBC parameter file
(\BusinessObjects\odbc\odbc10en.prm) supplied with BO with limited
success:
I can now represent outer joins in the universe but invalid SQL is
generated
when I report on it. I’ve included more details below.
Thanks in advance!
Greg Nockolds
Changes made to the odbc10en.prm file:
First change: EXT_JOIN=NO to EXT_JOIN=YES
This allowed me to define outer joins in the universe but had no effect on
the generated SQL.
Second change: OUTERJOINS_GENERATION=NO to OUTERJOINS_GENERATION=ODBC
Outer join syntax did get generated, but it is not valid for SQLAnywhere.
Example:
SELECT
DCT_Division.description,
DCT_Team.description
FROM
ECM_SC.sc_code_tree DCT,
ECM_SC.sc_code_tree DCT_Division,
ECM_SC.sc_code_tree DCT_Team,
{ oj DCT_Team RIGHT OUTER JOIN DCT_Division ON
DCT_Team.parent_code=DCT_Division.sc_code_no }
WHERE
( DCT.sc_code_no=‘8062’ )
AND ( DCT.sc_code_no=DCT_Division.parent_code )
which results in the following error:
[Sybase][ODBC Driver]Table or view not found: table ‘DCT_Division’ not
found-141
contents of my odbc10en.prm file:
[RDBMS]
(GENERAL)
SORT_BY_NO=YES
GROUPBYCOL=NO
EXT_JOIN=YES <=== First changed this one
CONCAT=+
STG= stodbcen
UNION=
INTERSECT=
MINUS=
OWNER=Y
QUALIFIER=Y
COMMA=+’ '+
NO_DISTINCT=Y
REFRESH_COLUMNS_TYPE=T
CHECK_OWNER_STATE=Y
CHECK_QUALIFIER_STATE=Y
KEY_INFO_SUPPORTED=N
OUTERJOINS_GENERATION=ODBC <==== then changed this one