Solved

OBIEE logical column has same SQL but returns different results

Posted on 2015-02-24
5
300 Views
Last Modified: 2015-03-03
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

0
Comment
Question by:mariita
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40630349
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40630483
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40630517
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
 

Accepted Solution

by:
mariita earned 0 total points
ID: 40630545
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
 

Author Closing Comment

by:mariita
ID: 40641315
It was the only answer that took into account that OBIEE does not allow for multiple case statements within a logical column.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question