Outer Join Problem

[slinc]

I have two tables joined on SLA_ID. One called 'CALLS' which contains all call information, and one called 'SLA_LOOKUP' which resolves the SLA_ID into a SLA_NAME. I join the two tables on SLA_ID, and create an outer join as some calls do not have an SLA.

The problem is when I create a query where I use SLA_NAME in the where clause (eg. where sla_name = '20 Days'), not only do I get the results where SLA_NAME = '20 Days', but also return calls with no SLA.

Many Thanks

 

[dbomrrsm]

select a.*,b.SLA_NAME
from CALLS a left outer join SLA_LOOKUP b
on a.SLA_ID = b.SLA_ID
WHERE b.SLA_NAME = '20 Days'

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[blom0344]

not only do I get the results where SLA_NAME = '20 Days', but also return calls with no SLA.

Adding predicates on tables that have another join type than a normal inner join leads to unexpected behavior.
An 'ON' check is different than the 'WHERE' check as it never removes rows from the set, but either categorizes them as 'matching' or 'non-matching'. If there is a match data is joined, if not matched data is passed through.

So the return calls with no SLA are the red ones



Ties Blom
Information analyst

 

[slinc]

Thanks for the feedback. I can easily get around this problem with free-hand sql, but I'm creating a universe for the report writers to use with a dimension called 'SLA_NAME'. If I leave the outer join on, if they use this dimension in the where clause they get calls with no SLAs. However, if I take the join off, if they use SLA_NAME in the select clause to return all calls the ones with no SLA are missing.

Is there any way of getting around this at universe level?

 

[blom0344]

hMM,

Tricky one. Universe design is almost all about the limits of SQL itself. A very common strategy within reporter is to use a global filter to surpress the unwanted portion. Very much in the way that you would surpress data from a document with linked dataproviders.
Are you entitled to store both fact and lookup data within a database view and use that within the universe? (other idea)

Just a thought, I am experiencing a major memory-leak

Ties Blom
Information analyst

 

[dbomrrsm]

what backend are you using - I just recreated the scenario in BO and SQL Server and it gives the desired results without freehand SQL.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[slinc]

I'm using SQL Server

 

[dbomrrsm]

In my universe I have the following set up

CALLS - SLA_LOOKUP
SLA_ID SLA_ID
SLA_NAME

with a many to one relationship CALLS M:1 SLA_LOOKUP on SLA_ID

with the outerjoin checked on the CALLS side.

with all three fields in a Class

in BO reporter if I drag the SLA_ID and SLA_NAME into Result Objects and put a condition that SLA_NAME = '20 Days' it returns only one row.

There are three rows in calls with sla_id of 1,2,3 and two in SLA_LOOKUP 1, '10 days' and 2, '20 Days'

My Query only returns row 2, '20 Days'

and BO creates the SQL

SELECT
  TEST.dbo.CALLS.SLA_ID,
  TEST.dbo.SLA_LOOKUP.SLA_NAME
FROM
{ oj TEST.dbo.CALLS LEFT OUTER JOIN TEST.dbo.SLA_LOOKUP ON ( TEST.dbo.CALLS.SLA_ID=TEST.dbo.SLA_LOOKUP.SLA_ID )
  }
WHERE
  ( 
  TEST.dbo.SLA_LOOKUP.SLA_NAME  =  '20 Days'
  )

If your set up is the same repost as I think I might know what the answer is to your problem - I think it might be a file you need that tells BO to use the full outer join syntax - if this is the case I can send you the file.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[slinc]

The SQL created by BO is :

SELECT
  CALLS.CALLID,
  SLA_LOOKUP.NAME
FROM
  CALLS,
  SLA_LOOKUP
WHERE
  CALLS.SLA_ID*=SLA_LOOKUP.SLA_ID
  AND SLA_LOOKUP.NAME  =  '20 Day'

 

[dbomrrsm]

there is a SQLSVEN.PRM file located in
C:\Program Files\Business Objects\Data Access 5.0\ODBC

that tells BO to set

OUTERJOINS_GENERATION=FULL_ODBC

your obviously doesnt contain this so it is using the *= syntax which is causing the problem.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

set up isnt my big thing so you would need to ask that of someone with more knowledge - I think the answer is yes unfortunately - test in on one first - to make sure it works though but I seem to remember having to do this on all machines although it was a long time ago so I may be mistaken - you may be able to run some sort of batch file to copy the file to all relevant workstations into the correct directory - but again not my area of expertise.

Good luck and please let me know if it sorts the problem.

emailing file now.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

Here is the text of the file - save it as the file name I said above in a text editor and save it in the directory stated above.

[RDBMS]
(GENERAL)
STG= STSRVen
SORT_BY_NO=NO
GROUPBYCOL=NO
EXT_JOIN=YES
CONCAT=+
UNION=UNION
INTERSECT=
MINUS=
OWNER=Y
QUALIFIER=Y
COMMA=+' '+
REFRESH_COLUMNS_TYPE=Q
CHECK_OWNER_STATE=N
CHECK_QUALIFIER_STATE=N
KEY_INFO_SUPPORTED=Y
OUTERJOINS_GENERATION=FULL_ODBC
BACK_QUOTE_SUPPORTED=Y
TABLE_DELIMITER="

[DATE]
(SQL)
YEAR={fn year($D)}
QUARTER=datepart(qq,$D)
MONTH={fn month($D)}

[OPERATORS]
(NOT_NULL)
ARITY=0
SQL=IS NOT NULL

(NULL)
ARITY=0
SQL=IS NULL

(SUP)
ARITY=1
SQL=>=

(INF)
ARITY=1
SQL=<=

(EQUAL)
ARITY=1
SQL==

(DIFF)
ARITY=1
SQL=!=

(STRICT_SUP)
ARITY=1
SQL=>

(STRICT_INF)
ARITY=1
SQL=<

(IN_LIST)
ARITY=1
SQL=IN

(NOT_IN_LIST)
ARITY=1
SQL=NOT IN

(MATCH)
ARITY=1
SQL=LIKE

(NOT_MATCH)
ARITY=1
SQL=NOT LIKE

(BETWEEN)
ARITY=2
SQL=BETWEEN  AND

(NOT_BETWEEN)
ARITY=2
SQL=NOT BETWEEN  AND

[FUNCTIONS]
(0)
NAME= Substring
TRAD= String: $Initial position: $Number of characters:
HELP= Extracts a sequence of characters from a character string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=substring($A,$n,$n)

(1)
NAME= Uppercase
TRAD= String:
HELP= Returns a character string in upper case
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=upper($A)

(2)
NAME= Lowercase
TRAD= String:
HELP= Returns a character string in lower case
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=lower($A)

(3)
NAME= Rightpart 
TRAD= String: $Nbr of char. to extract:
HELP= Returns a specified number of characters from the right of a character string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=right($A,$n)

(4)
NAME= LeftRemove
TRAD= String:
HELP= Removes blank characters from the left of a character string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=ltrim($A)

(5)
NAME= Absolute 
TRAD= Numeric expression:
HELP= Returns the absolute value of a given expression
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=abs($N)

(6)
NAME= Round
TRAD= Number to round: $Precision:
HELP= Returns a number rounded to n decimal places
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=round($N,$N)

(7)
NAME= Sqrt
TRAD= Square root of:
HELP= Returns the square root of a number
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=sqrt($N)

(8)
NAME= Minimum
TRAD= Minimum of:
HELP= Selects the minimum value
TYPE=N
IN_MACRO=Y
GROUP=Y
SQL=min($*)

(9)
NAME= Maximum
TRAD= Maximum of:
HELP= Selects the maximum value
TYPE=N
IN_MACRO=Y
GROUP=Y
SQL=max($*)

(10)
NAME= Average
TRAD= Average of:
HELP= Returns the average value
TYPE=N
IN_MACRO=Y
GROUP=Y
SQL=avg($N)

(11)
NAME= Sum
TRAD= Sum of:
HELP= Returns the sum of the values
TYPE=N
IN_MACRO=Y
GROUP=Y
SQL=sum($N)

(12)
NAME= Count
TRAD= Count of:
HELP= Returns the number of values
TYPE=N
IN_MACRO=Y
GROUP=Y
SQL=count($*)

(13)
NAME= Ceil
TRAD= Numeric expression:
HELP= Returns the value of a number rounded up to the nearest integer
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=ceiling($N)

(14)
NAME= Floor
TRAD= Numeric expression:
HELP= Returns the value of a number rounded down to the nearest integer
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=floor($N)

(15)
NAME= DateExtract
TRAD= Model: $Date Expression:
HELP= Extracts part of a date value
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=Datename($A,$D)


(16)
NAME= Arc cosine
TRAD= Value:
HELP= Returns the arc cosine of a numeric expression
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=acos($N)

(17)
NAME= Arc sine
TRAD= Value:
HELP= Returns the arc sine of a numeric expression
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=asin($N)


(18)
NAME= Arc Tangent
TRAD= Value:
HELP= Returns the arc tangent of a numeric expression
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=atan($N)


(19)
NAME= Angle Tangent 2
TRAD= Angle 1:$Angle 2:
HELP= Returns the angle in radians whose tangent is angle1/angle2
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=atan2($N,$N)


(20)
NAME= Cosine
TRAD= Angle:
HELP= Returns the cosine of a number
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=cos($N)

(21)
NAME= Sine
TRAD= Angle:
HELP= Returns the sine of a number
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=sin($N)


(22)
NAME= Tangent
TRAD= Angle:
HELP= Returns the tangent of a number
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=tan($N)

(23)
NAME= Degrees
TRAD= Value:
HELP= Converts radians to degrees
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=degrees($N)

(24)
NAME= Exp
TRAD= Value:
HELP= Returns the exponential value of the specified value
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=exp($N)


(25)
NAME= Log
TRAD= Value:
HELP= Returns the natural logarithm of the specified value
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=log($N)

(26)
NAME= Log10
TRAD= Value:
HELP= Returns the base 10 logarithm of the specified value
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=log10($N)

(27)
NAME= Power
TRAD= Value: $Power:
HELP= Returns a value raised to the nth power
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=power($N,$N)


(28)
NAME= Radians
TRAD= Value:
HELP= Converts degrees into radians
TYPE=N
IN_MACRO=N
GROUP=N
SQL=radians($N)



(29)
NAME= Sign
TRAD= Value:
HELP= Returns a number that represents the sign of the specified number
TYPE=N
IN_MACRO=N
GROUP=N
SQL=sign($N)

(30)
NAME= Random
TRAD= Value:
HELP= Returns a random number between 0 and 1
TYPE=N
IN_MACRO=N
GROUP=N
SQL=rand($N)

(31)
NAME= Pi
TRAD= ???
HELP= Returns the constant value of 3.1415926535897932
TYPE=N
IN_MACRO=N
GROUP=N
SQL=pi()


(32)
NAME= ASCII code 
TRAD= Character:
HELP= Returns the ASCII code of the character
TYPE=A
IN_MACRO=N
GROUP=N
SQL=ascii($C)


(33)
NAME= Character
TRAD= Value of the character:
HELP= Returns the character that corresponds to the ASCII code
TYPE=A
IN_MACRO=N
GROUP=N
SQL=char($N)

(34)
NAME= Charindex
TRAD= Expression: $Object:
HELP= Returns the position of an expression in a character string
TYPE=N
IN_MACRO=N
GROUP=N
SQL=charindex($A,$A)


(35)
NAME= Difference
TRAD= Object 1: $Object 2:
HELP= Returns the difference between the SOUNDEX value of two objects
TYPE=N
IN_MACRO=N
GROUP=N
SQL=difference($A,$A)


(36)
NAME= Patindex
TRAD= Pattern: $Object:
HELP= Returns the position of a character in a character string
TYPE=N
IN_MACRO=N
GROUP=N
SQL=patindex($A,$A)

(37)
NAME= Replicate
TRAD= Expression: $Number of times:
HELP= Repeats a character expression n number of times
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=replicate($A,$N)

(38)
NAME= Reverse
TRAD= Expression:
HELP= Reverses a string of characters
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=reverse($A)


(39)
NAME= Rtrim
TRAD= Object:
HELP= Removes blank characters from the right of a character string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=rtrim($A)

(40)
NAME= Soundex
TRAD= String:
HELP= Returns a character string representing phonetically the word(s) in the string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=soundex($A)

(41)
NAME= Space
TRAD= Number of times:
HELP= Returns a string with the number of blank characters
TYPE=A
IN_MACRO=N
GROUP=N
SQL=space($N)

(42)
NAME= Str
TRAD= ???
HELP= Returns a value set to a specific length
TYPE=A
IN_MACRO=N
GROUP=N
SQL=str()

(43)
NAME= Stuff
TRAD= String1: $Initial position: $Number of characters: String2:
HELP= Replaces a sequence of characters with other characters
TYPE=A
IN_MACRO=N
GROUP=N
SQL=stuff($A,$N,$N,$A)

(44)
NAME= Conversion
TRAD= ???
HELP= Converts datatypes
TYPE=A
IN_MACRO=N
GROUP=N
SQL=Convert()


(45)
NAME= Cotangent
TRAD= Value:
HELP= Returns the cotangent of an angle in radians
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn cot($N)}

(46)
NAME= Mod
TRAD= Value: $Divided by
HELP= Returns the remainder of a division
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn mod($N,$N)}

(47)
NAME= Concat
TRAD= String1: $String2:
HELP= Concatenates two character strings
TYPE=A
IN_MACRO=Y
GROUP=N
SQL={fn concat($A,$A)}

(48)
NAME= Left
TRAD= String:$Length:
HELP= Returns a specified number of characters from the left of a character string
TYPE=A
IN_MACRO=Y
GROUP=N
SQL={fn left($A,$N)}

(49)
NAME= Length
TRAD= Object:
HELP= Returns the length of a character string
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn length($A)}

(50)
NAME= Current date
TRAD= current date
HELP= Returns the current date
TYPE=D
IN_MACRO=Y
GROUP=N
SQL=convert (SMALLDATETIME, {fn CURDATE()})


(51)
NAME= Day of week
TRAD= Date:
HELP= Returns the day of the week (1 equals Sunday)
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn dayofweek($D)}
     
(52)
NAME= Day of month
TRAD= Date:
HELP= Returns the day of the month (1-31)
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn dayofmonth($D)}
     
(53)
NAME= Day of year
TRAD= Date:
HELP= Returns the day of the year (1-366)
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn dayofyear($D)}
     
(54)
NAME= Number of the week
TRAD= Date:
HELP= Returns the number of the week (1-52)
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn week($D)}
     
(55)
NAME= Number of the month
TRAD= Date:
HELP= Returns the number of the month (1-12)
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn month($D)}
     
(56)
NAME= Year
TRAD= Date:
HELP= Returns the year
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn year($D)}
     
(57)
NAME= Quarter
TRAD= Date:
HELP= Returns the quarter
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=datepart(qq,$D)

(58)
NAME= Locate
TRAD= Search: $Object:
HELP= Returns the position of a character in a character string
TYPE=N
IN_MACRO=Y
GROUP=N
SQL={fn locate($A,$A)}

(58a)
NAME= IsNull
TRAD= Expression:
HELP= Returns TRUE if the expression is NULL
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=isnull($*)

(59)
NAME= Character prompt
TRAD= Question:
HELP= Prompts the user to enter a specific value 
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=@prompt($A,'A',,,)

(60)
NAME= Numeric prompt
TRAD= Question:
HELP= Prompts the user to enter a specific value 
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=@prompt($A,'N',,,)

(61)
NAME= Date prompt
TRAD= Question:
HELP= Prompts the user to enter a specific value 
TYPE=D
IN_MACRO=Y
GROUP=N
SQL=@prompt($A,'D',,,)

(62)
NAME= Case
TRAD= Value: $Expression: $Result1: $Result2:
HELP= Returns a value that depends on which of the specified set of conditions is met
TYPE=A
IN_MACRO=N
GROUP=N
SQL=CASE $A WHEN $A THEN $A ELSE $A END

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[slinc]

You're a star! Changed OUTERJOIN_GENERATION from USUAL to FULL, and removed LEFT_OUTER and RIGHT_OUTER parameters and it works! Thanks

 

[dbomrrsm]

Great glad it worked - just remembered - whilst your email address was on the site I noticed you work for AnglianWater - I work for UU (a supposed rival water co) so was contemplating not posting the help !! ;-)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]