OUTER JOIN

building semantic layer
1

Relationship from company to project is One to Many
Company -< Project
i.e. one company can have many projects. There are companies which don’t have any projects at all.
I want to include all those companies in my report which don’t have any projects. So I created an outer join.
When I create a report, BO generates following SQL.


SELECT
     COMPANY.COMPANY_NAME
     COMPANY.COMPANY_NUMBER,
     PROJECT.PROJECT_NAME
FROM  (COMPANY LEFT OUTER JOIN PROJECT ON (PROJECT.COMPANY_ID=COMPANY.COMPANY_ID) )
WHERE
   COMPANY.COMPANY_NUMBER  =  '10000'
   AND PROJECT.STATUS = 'NEW'

This query does not return me any data.

I wrote an equivalent SQL in DBArtisan

SELECT 
    COMPANY.COMPANY_NAME, 
    COMPANY.COMPANY_NUMBER, 
    PROJECT.PROJECT_NAME
FROM PROJECT, COMPANY
WHERE 
   COMPANY.COMPANY_ID *= PROJECT.COMPANY_ID
   AND COMPANY.COMPANY_NUMBER = '10000'
   AND PROJECT.STATUS = 'NEW'

This query gives me one row.
Note the change in outer join syntax.

  1. Why the two Qeuries produce different results?
  2. How can I change my settings in BO so that OUTER JOIN syntax should be as in second query.

We are on BO 6.0.1 and Sybase 12.5

Thanks,

mkumar (BOB member since 2002-08-26)

2

Please, try a Search on BOB, suggested keywords: Sybase outer join.

Or start with this thread.

Andreas :de: (BOB member since 2002-06-20)