Talis Developer Network - Email Addresses - Comments

outer joins

Brian Crampton — Tue, 9 Sep 2008 15:19:28 +0100

The \Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\sybase\sybase.prm file on the PC running Desktop Intelligence also needs the lines with the following values

"LEFT_OUTER" $*
"RIGHT_OUTER" *$

The parameters may be blank.

It is unlikely that the universe load caused the problem itself but it may have brought the issue to light.

Brian Crampton
Developer, Talis

Outer Joins

markhughes — Tue, 9 Sep 2008 11:21:08 +0100

Brian,

I have recreated the reports from scratch and it seems the problem is only with DeskI, it works OK and as expected in WebI.

Cheers
Mark

http://www.wirral-libraries.net/

Outer joins

markhughes — Tue, 9 Sep 2008 11:03:25 +0100

Brian,

I have checked and I am using the correct Analysis detail object.

I have also checked my sybase.prm file and the correct values are there:
'EXT_JOIN_INVERT" = NO'
'OUTERJOINS_GENERATION = USUAL'

What's puzzling is that all was OK, with no problems, before I upgraded the universes.

I have just done the report in BO5i and that worked OK, showing all records, including those with blank Analysis codes, as expected. BUT when I then open this same report in BO Desktop Intelligence, then it only shows those records with Analysis codes.

I'll raise a job with the support team.

Cheers
Mark

http://www.wirral-libraries.net/

Borrower interest object

Brian Crampton — Tue, 9 Sep 2008 09:16:37 +0100

I have double-checked the 4.3 Circulation universe and the two outer joins are defined in the universes and are displaying in all the queries I create in Desktop Intelligence and Web Intelligence. The borrower analysis objects were not touched in this release.

There are two possibilities that could be the issue.

2) The available Sybase functions are defined in a file within the installation called sybase.prm. By default this file does not have the correct values for outer joins. As part of the Decisions Server installation this file is updated. For installations of Desktop Intelligence this file has to be changed locally as detailed in the instructions supplied during the install. The upgrade of the universes would not affect this file however.

As the universes are supported I would suggest that you raise a call through Support and then your setup can be investigated more fully than can be done in the Forum.

Brian Crampton
Developer, Talis

Brian, See SQL below for the

markhughes — Mon, 8 Sep 2008 17:00:00 +0100

Brian,

See SQL below for the SQL generated on the 4.2 universe and also on the 4.3 universe. The only difference is that on the 4.2 universe, the SQL included a '*' in the following lines of the WHERE clause:

AND ( dbo_ANALYSIS_BGL2.BORROWER_ID=*dbo_BORR1.BORROWER_ID )
AND ( dbo_ANALYSIS2.GROUPING_ID=*dbo_ANALYSIS_BGL2.GROUPING_ID )

I manually added these to the 4.3 universe SQL and it returned the correct results...

4.2 universe SQL
================

SELECT
dbo_BORR1.BARCODE,
substring(dbo_BORR1.SURNAME,1,25) + ', ' + substring(dbo_BORR1.FIRST_NAMES,1,25) + ', ' + substring(dbo_BORR1.STYLE,1,10),
substring(dbo_CONTACT_TYPE.NAME,1,20),
dbo_DEFAULT_CONTACT.NAME,
dbo_CONTACT1.CONTACT_NAME,
dbo_CONTACT1.START_DATE,
dbo_CONTACT1.END_DATE,
substring(dbo_CONTACT1.DISPLAY_VALUE,1,60),
substring(dbo_CONTACT1.NOTE,1,40),
dbo_BORR_HOME_SITE1.NAME,
substring(dbo_BORR_TYPE1.NAME,1,40),
dbo_ANALYSIS2.NAME
FROM
dbo.BORROWER dbo_BORR1,
dbo.TYPE_STATUS dbo_CONTACT_TYPE,
TRUE_FALSE dbo_DEFAULT_CONTACT,
CONTACT dbo_CONTACT1,
dbo.LOCATION dbo_BORR_HOME_SITE1,
dbo.TYPE_STATUS dbo_BORR_TYPE1,
dbo.GROUPING dbo_ANALYSIS2,
dbo.TYPE_STATUS dbo_BORR_STATUS1,
dbo.BORROWER_GROUPING_LINK dbo_ANALYSIS_BGL2
WHERE
( dbo_BORR_HOME_SITE1.LOCATION_ID=dbo_BORR1.HOME_SITE_ID )
AND ( dbo_BORR_HOME_SITE1.TYPE=0 )
AND ( dbo_BORR_TYPE1.SUB_TYPE=2 )
AND ( dbo_BORR_TYPE1.TYPE_STATUS=dbo_BORR1.TYPE_ID )
AND ( dbo_BORR_STATUS1.SUB_TYPE=37 )
AND ( dbo_BORR_STATUS1.TYPE_STATUS=dbo_BORR1.STATUS )
AND ( dbo_ANALYSIS2.SUB_TYPE=1 )
AND ( dbo_CONTACT1.TARGET_ID=dbo_BORR1.BORROWER_ID )
AND ( dbo_DEFAULT_CONTACT.CODE=dbo_CONTACT1.PREFERRED )
AND ( dbo_ANALYSIS_BGL2.SUB_TYPE=1 )
AND ( dbo_ANALYSIS_BGL2.BORROWER_ID=*dbo_BORR1.BORROWER_ID )
AND ( dbo_ANALYSIS2.GROUPING_ID=*dbo_ANALYSIS_BGL2.GROUPING_ID )
AND ( dbo_CONTACT_TYPE.TYPE_STATUS=dbo_CONTACT1.CONTACT_TYPE )
AND ( dbo_CONTACT_TYPE.SUB_TYPE=135 )
AND (
substring(dbo_BORR_STATUS1.NAME,1,15) = 'Active'
)

4.3 universe SQL
================

SELECT
dbo_BORR1.BARCODE,
substring(dbo_BORR1.SURNAME,1,25) + ', ' + substring(dbo_BORR1.FIRST_NAMES,1,25) + ', ' + substring(dbo_BORR1.STYLE,1,10),
substring(dbo_CONTACT_TYPE.NAME,1,20),
dbo_DEFAULT_CONTACT.NAME,
dbo_CONTACT1.CONTACT_NAME,
dbo_CONTACT1.START_DATE,
dbo_CONTACT1.END_DATE,
substring(dbo_CONTACT1.DISPLAY_VALUE,1,60),
substring(dbo_CONTACT1.NOTE,1,40),
dbo_BORR_HOME_SITE1.NAME,
substring(dbo_BORR_TYPE1.NAME,1,40),
dbo_ANALYSIS2.NAME
FROM
dbo.BORROWER dbo_BORR1,
dbo.TYPE_STATUS dbo_CONTACT_TYPE,
TRUE_FALSE dbo_DEFAULT_CONTACT,
CONTACT dbo_CONTACT1,
dbo.LOCATION dbo_BORR_HOME_SITE1,
dbo.TYPE_STATUS dbo_BORR_TYPE1,
dbo.GROUPING dbo_ANALYSIS2,
dbo.TYPE_STATUS dbo_BORR_STATUS1,
dbo.BORROWER_GROUPING_LINK dbo_ANALYSIS_BGL2
WHERE
( dbo_BORR_HOME_SITE1.LOCATION_ID=dbo_BORR1.HOME_SITE_ID )
AND ( dbo_BORR_HOME_SITE1.TYPE=0 )
AND ( dbo_BORR_TYPE1.SUB_TYPE=2 )
AND ( dbo_BORR_TYPE1.TYPE_STATUS=dbo_BORR1.TYPE_ID )
AND ( dbo_BORR_STATUS1.SUB_TYPE=37 )
AND ( dbo_BORR_STATUS1.TYPE_STATUS=dbo_BORR1.STATUS )
AND ( dbo_ANALYSIS2.SUB_TYPE=1 )
AND ( dbo_CONTACT1.TARGET_ID=dbo_BORR1.BORROWER_ID )
AND ( dbo_DEFAULT_CONTACT.CODE=dbo_CONTACT1.PREFERRED )
AND ( dbo_ANALYSIS_BGL2.SUB_TYPE=1 )
AND ( dbo_ANALYSIS_BGL2.BORROWER_ID=dbo_BORR1.BORROWER_ID )
AND ( dbo_ANALYSIS2.GROUPING_ID=dbo_ANALYSIS_BGL2.GROUPING_ID )
AND ( dbo_CONTACT_TYPE.TYPE_STATUS=dbo_CONTACT1.CONTACT_TYPE )
AND ( dbo_CONTACT_TYPE.SUB_TYPE=135 )
AND (
substring(dbo_BORR_STATUS1.NAME,1,15) = 'Active'
)

Cheers
Mark

Emaill addresses

Brian Crampton — Mon, 8 Sep 2008 14:10:49 +0100

I have created the query in both versions of the universes and the SQL generated is exactly the same.

Can you confirm whether you are using Web Intelligence or Desktop Intelligence.

Could you also send a copy of the SQL generated by the query please.

Thanks

Brian Crampton
Developer, Talis

Email Addresses

markhughes — Mon, 8 Sep 2008 12:47:42 +0100

I have just upgraded the universes to 4.3 and have come across a problem which I cannot see away around...

I had a simple report which showed all users with email addresses and also the contents of the borrower analysis code, so I could see whether they had opted for email contact. And this worked great... I could see all email addresses and analysis codes so I could easily spot which analysis codes were blank.

My report consisted of: Barcode, Borrower Name, Contact Type, Default Contact Y/N, Contact name, Contact address, Home site, Borrower Type and Borrower Analysis.