OBIEE logical column has same SQL but returns different results

I have a SQL query with a case statement that is returning correct results when run in Oracle SQL Developer. I have created several logical columns in OBIEE, one for each case in the original query. However, the results returned by each OBIEE logical column are radically different from the results of the original SQL query, even though the SQL code is virtually identical.

For example, one of the OBIEE logical columns that is returning incorrect results contains the following SQL:

SUM( CASE when
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%A%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%B%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%C%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%E%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%G%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%H%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%J%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%R%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%S%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%T%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%V%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%X%') or
("Enrolment - College"."F College Enrolment"."Postal Code"  like '%Y%')
THEN 1 ELSE 0 END )

Open in new window



The case statement in the original SQL query, which is returning correct results, is as follows:

CASE
WHEN (postal_zip_code_permanent like '%K%') or (postal_zip_code_permanent like '%L%') or (postal_zip_code_permanent like '%M%') or (postal_zip_code_permanent like '%N%') or (postal_zip_code_permanent like '%P%') THEN 'Ontario'
WHEN (postal_zip_code_permanent like '%A%') or (postal_zip_code_permanent like '%B%') or (postal_zip_code_permanent like '%C%') or (postal_zip_code_permanent like '%E%') or (postal_zip_code_permanent like '%G%') or (postal_zip_code_permanent like '%H%') or (postal_zip_code_permanent like '%J%') or (postal_zip_code_permanent like '%R%') or (postal_zip_code_permanent like '%S%') or (postal_zip_code_permanent like '%T%') or (postal_zip_code_permanent like '%V%') or (postal_zip_code_permanent like '%X%') or (postal_zip_code_permanent like '%Y%') THEN 'Canada, outside Ontario'
WHEN (substr(postal_zip_code_permanent,1,1) IN ('1','2','3','4','5','6','7','8','9','0') or (postal_zip_code_permanent like '%D%') or (postal_zip_code_permanent like '%F%') or (postal_zip_code_permanent like '%I%') or (postal_zip_code_permanent like '%O%') or (postal_zip_code_permanent like '%Q%') or (postal_zip_code_permanent like '%U%') or (postal_zip_code_permanent like '%W%') or (postal_zip_code_permanent like '%Z%') ) THEN 'Other'
WHEN (postal_zip_code_permanent like '%+%') or (postal_zip_code_permanent like '%.%') or (postal_zip_code_permanent like '%?%') or (postal_zip_code_permanent like '%,%') or postal_zip_code_permanent IN ('+', '.', '?', ',') THEN 'Invalid'
WHEN postal_zip_code_permanent is null THEN 'Blank'
ELSE postal_zip_code_permanent
END

Open in new window

mariitaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mariitaConnect With a Mentor Author Commented:
I see now what the problem was. In the original SQL query, each condition is exclusive, so each record will only be categorized into one of the WHEN scenarios. But in OBIEE, each logical column is standalone, so some records were categorized into more than logical column, even though each logical column was supposed to be exclusive.
0
 
PaulCommented:
I can't help with the OBIEE case expression, sorry.

But the SQL case expression looks complex.

To test a string is only numeric
| POSTAL_ZIP_CODE_PERMANENT | STATUS_OF |
|---------------------------|-----------|
|                   1234567 |     valid |
|                  1234nnt8 |   INVALID |
|                 oqihafoih |   INVALID |
|                  1m2n3b4v |   INVALID |

Open in new window


tested by:
select
    POSTAL_ZIP_CODE_PERMANENT
  , case when REGEXP_REPLACE(POSTAL_ZIP_CODE_PERMANENT,'[^0-9]+', '') = POSTAL_ZIP_CODE_PERMANENT
         then 'valid'
         else 'INVALID'
     end as status_of
from table1
;

Open in new window


Don't know if you can use such an approach with OBIEE.
0
 
Haris DjulicCommented:
Hi,

can you describe more detail what you mean by radically different?

and can you post some output  samples from both on reduced sample for example where postal_zip_code_permanent like '%A%'?
0
 
johnsoneSenior Oracle DBACommented:
I'm not sure what you are trying to do.  However, the case statements in the 2 portions of code that you posted are different.

The first one only accounts for what you are listing as Canada, outside Ontario in the second one.

Does the results from the first query match the results from the second query that are marked Canada, outside Ontario?  If so, then it would appear to me to be working correctly.
0
 
mariitaAuthor Commented:
It was the only answer that took into account that OBIEE does not allow for multiple case statements within a logical column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.