Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

OBIEE logical column has same SQL but returns different results

Posted on 2015-02-24
5
311 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

839 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