Home » Discuss » Forums » Library Systems » Talis Library System » Talis Decisions

Email Addresses

Submitted by markhughes on Mon, 2008-09-08 12:47.

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.

The only condition was: Active/Deleted equal to 'Active'

However, since upgrading the universes to 4.3, this no longer works, and only shows those records where an analysis code exists. So I can't now see which borrowers have email addresses but DO NOT have an analysis code.

Any ideas?

Mark

‹ checking which borrowers have email addresses how to exclude borrowers with a particular standard message from a report ›
»

Emaill addresses

Submitted by Brian Crampton on Mon, 2008-09-08 14:10.

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

»

Brian, See SQL below for the

Submitted by markhughes on Mon, 2008-09-08 17:00.

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

»

Borrower interest object

Submitted by Brian Crampton on Tue, 2008-09-09 09:16.

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.

1) You should be using the Borrowers | Borrower details | Barcode | Borrower analysis detail object and not the Borrowers | Borrower conditions | Analysis dimension object. The Analysis object as a condition does not use outer joins.

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

»

Outer joins

Submitted by markhughes on Tue, 2008-09-09 11:03.

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/

»

Outer Joins

Submitted by markhughes on Tue, 2008-09-09 11:21.

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

Submitted by Brian Crampton on Tue, 2008-09-09 15:19.

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

»