Limits to Case Statement

[maverick00]

An object is created using a case statement. It has multiple When statements. The Universe aborts when the object is parsed. The database is Oracle 8.

Oddly, if some of the When statements are removed, it will parse, and continue to parse, as the removed When statements are added back in.

If the Universe is then uploaded and downloaded again the Universe will again abort when the object is parsed.

Is there a limit to the Case statement? Why does it allow me to parse without aborting some of the time?

 

[dbomrrsm]

I think Oracle only supports the case statement from 9i

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[maverick00]

The case statement works fine. We have a 10 year old software tool that allows us to run SQL and the case statement works fine.

Right now, my workaround is to not parse the code in BO. It still publishes the Universe to the Repository and works fine in the report.

 

[blom0344]

'Case' was introduced to ORACLE in version 8, before that time you were forced to use ORACLE's 'decode' instead.
I imagine version 9 still supports 'decode'? What happens if you create the object by using 'decode' instead' of 'case'?

Parsing is not mandatory, it is just an additional check..


Ties Blom
Information analyst

 

[maverick00]

Decode behaves the same way and has a limit of around 125 arguments.

Of course, one way to do this is to create a table, which will be done. The rush on this assignment prevented that at first.

So far my answer has been not to parse the object.

 

[BrianTyler]

You havn't mentioned which version of B.O. you are on, or how many 'when' statements there are.

There is always a chance that the SQL statement length exceeeds the permitted (in BO, ODBC or Oracle itself), or the Case statement itself has limits on text size or number of entries

 

[maverick00]

here is the code:
CASE
WHEN (ae_l4_vh.COMPT_OBJ_CD >= '4801' AND ae_l4_vh.COMPT_OBJ_CD <= '4817')
OR (ae_l4_vh.COMPT_OBJ_CD >= '4825' AND ae_l4_vh.COMPT_OBJ_CD <= '4857')
OR (ae_l4_vh.COMPT_OBJ_CD >= '4920' AND ae_l4_vh.COMPT_OBJ_CD <= '4955') THEN 'IN STATE TRAVEL EXPENSES'
WHEN (ae_l4_vh.COMPT_OBJ_CD >= '4860' AND ae_l4_vh.COMPT_OBJ_CD <= '4872')
OR (ae_l4_vh.COMPT_OBJ_CD >= '4880' AND ae_l4_vh.COMPT_OBJ_CD <= '4915')
OR (ae_l4_vh.COMPT_OBJ_CD >= '4960' AND ae_l4_vh.COMPT_OBJ_CD <= '4995') THEN 'OUT OF STATE TRAVEL EXPENSES'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6112','6115','6120','6125','6130','6169','6170','6171')
THEN 'PURCHASED SERVICES'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6135','6136','6137','6160')
THEN 'DUES, FEES, SUBSCRIPT, CONFERENCES'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6140','6145','6146') THEN 'MAILING SERVICES'
WHEN ae_l4_vh.COMPT_OBJ_CD = '6155' THEN 'OTHER FEES AND COMPENSATION'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6230','6235','6245','6246') THEN 'SUPPLIES'
WHEN ae_l4_vh.COMPT_OBJ_CD = '6240' THEN 'REPLACEMENT PARTS'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6425','6426','6445','6510','6530')
THEN 'RENTAL/LEASE OF CAPITAL ASSETS'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6500','6520') THEN 'CAPITALIZED PURCHASE OF ASSETS'
WHEN ae_l4_vh.COMPT_OBJ_CD IN ('6505','6525') THEN 'NON CAPITALIZED PURCHASE OF ASSETS'
WHEN ae_l4_vh.COMPT_OBJ_CD = '6560' THEN 'MOVABLE PARTITIONS'
ELSE 'OTHER'
END
We are using version 5.1.7.

 

[skrandel]

I don't see any reasons that this doesn't work.

Steve Krandel
VERITAS Software

 

[dbomrrsm]

Me neither - it looks fine to me.

What error do you get ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[maverick00]

The software aborts when the object is parsed. This is the message.


Unhandled Exception
Code: c0000005
Description: EXCEPTION_ACCESS_VIOLATION

Call stack:
------------------------------------------------------------------
Module at 0x60400000: C:\oracle\ora81\bin\ORACLIENT8.DLL v 0.0.0.0 [English (United States)]
address: 0x60429283
address: 0x60488f03
address: 0x604837a1
address: 0x60468fc4
Module at 0x10000000: C:\oracle\ora81\bin\OCIW32.dll v 1.0.0.5 [English (United States)]
address: 0x10001a80
Module at 0x34540000: C:\Program Files\Business Objects\Data Access 5.0\SQBOCI50.DLL
address: 0x34547c01
address: 0x34548499
address: 0x345548c0
Module at 0x31b30000: C:\Program Files\Business Objects\BusinessObjects 5.0\cnxsrv50.dll
address: 0x31b4d8ea
Module at 0x345f0000: C:\Program Files\Business Objects\BusinessObjects 5.0\tools50.dll
address: 0x347534fe
------------------------------------------------------------------

 

[dbomrrsm]

dont think this is anything to do with the query - seems n=more like a call failure to oracle ?

http://forum.java.sun.com/thread.jspa?threadID=434753&messageID=1950032

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[maverick00]

Definitely not an Oracle wiz. What needs to be done?

 

[dbomrrsm]

I would show the query to the oracle DBA and show him the error message you are getting - they may be able to more accurately trace the cause.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]