Solved

OBIEE logical column has same SQL but returns different results

Posted on 2015-02-24
5
280 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 43
Using querystring in a hyperlink 3 35
query in Oracle forms Builder 2 26
Insert with SET how to handle join 6 26
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 …
How to increase the row limit in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now