[solved] another outerjoin & SQL ANSI 92 problem

building semantic layer
1

Hi all !

It is all about an outer join problem & SQL ANSI_92 syntax. I looked for messages dealing with this problem, but none has been corresponding (actually, the ones I found were the oppposite of mine …).

My problem is that I have complex joins with outer joins melted (one would say “quite ugly” but that’s the ways it is and I have to deal with it to build my universe). Since outer joins are disabled on outerjoins, I have to write it by hand in the join syntax. As a result, when some querries are generated by BO, I have an error because both *= and ANSI syntax are melted.

I cannot modify the sybase prm file because it is likely to be used by another application in my company, but I guess such modifications can be done at universe level using the parameter tab.

I then tried adding:
OUTERJOINS_GENERATION=USUAL (instead of ANSI_92 in the prm file)
LEFT_OUTER=$* (instead of nothing)
RIGHT_OUTER=*$ (instead of nothing)
I did not touch ANSI92=No (in the tab) and EXT_JOIN=YES (in the file)

whether modifications are made directly in the file on in the parameter tab; the result is still the same. everything looks like the changes have no effect at all …

So here are my questions :
1/ is it correct that the universe parameter tab will override the prm file settings ?
2/ how can I enable the *= syntax ? Am I missing something ?
3/ is it normal that with a ANSI92=No in the universe paremeters and a OUTERJOINS_GENERATION=ANSI_92 in the prm file, i have SQL ANSI syntax generated ?
4/ subsidiary : is it better to have ANSI syntax and enabling complex outer joins (if possible) ? these are disabled, so if you answered yes, can you tell me how to enable outerjoins on complex joins ?

If it can help, i’m running sybase v12, BO 6.1 thick client

Many thanks for the help,

david

blinitz (BOB member since 2004-04-20)

2

I might had that everything was going well with BO 5.1 and that this only happened after the universe was migrated to BO 6.1 … (with a 6.1 referential)

blinitz (BOB member since 2004-04-20)

3

Change your OUTERJOINS_GENERATION parameter from USUAL to FULL_ODBC

Regards,
Mark

4

Hi mark8664 and thanks for the quick reply.

Unfortunately, this does not work neither.

david

blinitz (BOB member since 2004-04-20)

5

Try changing your LEFT and RIGHT parameters to just $ rather than $* and *$

6

the problem still remains.

but I’ve just noticed an other very interesting thing : the ANSI syntax is not used when I have standard joins but it is when I have outer joins

edit : the ANSI SQL Syntax is ONLY used when at least a join involved in the querry has its outer join checkbox checked.

blinitz (BOB member since 2004-04-20)

7

Can you cut and paste the whole fo this section of the PRM file here then?

cheers,
Mark

8

OK, here it is : sybase prm file :

I guess the positions of the $ and * in LEFT_OUTER and RIGHT_OUTER are good since I found them this way on the BO web site

In the parameters tab of my universe, I Have :

blinitz (BOB member since 2004-04-20)

9

I’d try setting ANSI to Yes, setting left and right outer to just $ and OUTERJOINS_GENERATION to FULL_ODBC. I can’t see anything else there that would affect your SQL as you have described it.

Regards,
Mark

10

OK, I got it … it works now.

Thanks for your help.

To solve the problem, I went to the connection parameters’ screen, and turned the SGBDR engine from Sybase Adaptive Server 12 to 11.

The fact is that I’am currently using Sybase 12.0 with openclient 11.1.
And I am about to migrate to Sybase 12.5 & OC12.5.

yet, I still do not understand why there is such a different behaviour; If someone can enlight me, that’s cool !

If anyone is interrested, I’ll update this when I am under Sybase and OC 12.5.

many thanks to mark8664

david

blinitz (BOB member since 2004-04-20)

11

A couple of other few elements, please do not hesitate and correct me if I am wrong :

the reason for all this might be that Sybase v11 was(is) not ANSI whereas Sybase v12 does comply with the ANSI standard. As a result, I think all this mess has nothing to do with openclient. It is just that using the v12 engine within BO enforces querries with outer joins to be writen using the ANSI 92syntax.

Why only such querries ? I cannot figure out exactly, but maybe for convenience : i personnally find the “not ansi” syntax far more easy to read and understand that the ansi one.

Finnally, how to manage complex outer joins with the Sybase v12 Adaptive Server Engine ?
Most of the time, checkboxes that setup the outerjoins on complex querries are disabled. Yet, you can still check the checkbox before building your complex join. BO will still disable them, but it will still take them into account on your schema (complex outer joins will be visible just as simple outer joins) and when writing the SQL code. (I sum it up there, but I found it on another thread.)

Hope this can help anyone in the same trouble as I have been.

Anyway, this works fine but not in a very sexy way …

Is this a bug ? or is there another way to do so ? :confused:
Though solved, any advice greatly appreciated

Thanks,

David

blinitz (BOB member since 2004-04-20)

12

I have facing this issue with ANSI SQL.

I have two tables. Company and Project.
There are companies which don’t have any project so I put an outer join b/w company and project.

If I put a where condition on Project.project number, I was still expecting all the companies to be returned by query but it returned only one row for that particular project number which is incorrect.

If I make changes to PRM file and put OUTER_JOIN_GENERATION = USUAL, it works fine.

But problem with USUAL is, If I join (regular join) another table Project_date to project table, the query won’t run. It gives errors saying project is inner member of an outer join which is not possible as it also participates in a regular join (with project_date table).

What is the way out here?

mkumar (BOB member since 2002-08-26)

13

Qeury generated with ANSI SQL is:

SELECT
  company.name,
  project.project_number,
  date.year
FROM
  ((company RIGHT OUTER JOIN project ON (project.company_id=company.company_id) )
   INNER JOIN date ON (date.date_id=project.project_start_date_id) )
WHERE  (  project.project_number  =  123 )

However the correct query should be

SELECT
  company.name,
  project.project_number,
  date.year
FROM
  ((company RIGHT OUTER JOIN project ON (project.company_id=company.company_id) AND project.project_number  =  123)
   INNER JOIN date ON (date.date_id=project.project_start_date_id)  )

With ANSI, all the conditions on the inner table should be moved to the FROM clause which does not happen when I create conditions in BO.

mkumar (BOB member since 2002-08-26)

14

If you change the outer joins into inner joins, does it do everything properly? I realize you want outer joins. I’m just wondering if the misbehavior is due to the outer joins.

KSG :us: (BOB member since 2002-07-17)

15

Yeah, Inner join works fine But I do want outer joins for my query.

Its syntax problem with ANSI. It seems ANSI syntax get executed line by line.
If I put a where condition outside FROM clause, the where clause gets executed first. OUTER JOIN statement in FROM gets executed next but is nullified because the where condition has already filtered out the data.

mkumar (BOB member since 2002-08-26)

16

Can you make the Inner join on the date into an Outer join, and use Usual instead of ANSI?

KSG :us: (BOB member since 2002-07-17)

17

I tried that. If I do an outer join on project to date
I gives me either all the projects or all the dates (depending on whether I do Left or Right outer join) which is again incorrect :frowning:

mkumar (BOB member since 2002-08-26)

18

Which RDBMS?

Do you want all companies, even if they don’t have a project 123?

Or do you usually want all companies, but if you request a specific project, you only want those companies that have a project 123?

KSG :us: (BOB member since 2002-07-17)

19

(Sybase 12.5)
The user can put a condition either on project or on company.

What I realized is, If I put an outer join b/w company to project, project being the inner member of outer join, I can’t put a WHERE condition on project table because irrespective of what condition I put on project table it will always select all companies.

So If a user wants to see information only for project no 123, he can’t do that unless he puts a WHERE condition on Company table also with to select only that company which has project no 123. Which means he needs to know the company to which the project no 123 belongs which is not possible for the users to know in most situations.

I have begining to realize that outer join won’t solve my purpose infact they are creating more problems that they solve.

mkumar (BOB member since 2002-08-26)

20

With Sybase and outer joins, I’ve done “isnull” to get around this.

This is really odd but I create TWO joins, one outer join between project and company (let’s say the project table is aliased to oj_project) and one inner join between project (aliased to ij_project) and company.

Then I define project as “isnull(oj_project.project,ij_project.project)”

When using project in a condition, it doesn’t include all the companies that don’t have projects.

When not using project in the conditions, it does include all the companies that don’t have projects.

I don’t know WHY but it works so I’m not complaining.

KSG :us: (BOB member since 2002-07-17)