Solved

OBIEE logical column has same SQL but returns different results

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

932 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

18 Experts available now in Live!

Get 1:1 Help Now