BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

ORA-00933: SQL command not properly ended :-933


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
DanJ
Senior Member
Senior Member



Joined: 20 Nov 2006

Posts: 62



PostPosted: Mon Mar 12, 2007 5:26 am 
Post subject: ORA-00933: SQL command not properly ended :-933

Hello,

Our data mart isnt really up to the job to report our MI as we need, so we have a lot of logic in our universe.

Essentially, I need to create a Business/ Department hierarchy, which is a combination of Sales Channels and Products. This means we have a lot of CASE statements around this dimension.

When I add these new dimensions to a report I get the following error.
"ORA-00933: SQL command not properly ended :-933"

THE SQL has 8,500 characters. Is there a limit to the no. of characters a data provider can hold?


Also, the SQL does contain some &. e.g. CASE WHEN SALES CHANNEL = 'Group' AND product = 'Life insurance' then 'GROUP P&A'.

GROUP P&A is the name of the department, i need to create.

The database is Oracle, v 9 i think.

Could it be the &, or is the SQL too large?

Thanks for your help
Dan
Back to top
Omkar Paranjpe
Forum Enthusiast
Forum Enthusiast



Joined: 13 Feb 2006
ASUG Icon
Posts: 1898
Location: Minneapolis, MN


flag
PostPosted: Mon Mar 12, 2007 7:26 am 
Post subject: Re: ORA-00933: SQL command not properly ended :-933

Hi,
Have you used "Else ENd" at the end of the SQL?
it will be better if you send the complete SQL

_________________
Regards,
Omkar Paranjpe.
Back to top
DanJ
Senior Member
Senior Member



Joined: 20 Nov 2006

Posts: 62



PostPosted: Mon Mar 12, 2007 7:42 am 
Post subject: Re: ORA-00933: SQL command not properly ended :-933

The actual select statement for one of my dimensions is
CASE WHEN @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') AND IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Direct' THEN 'Direct Protection' else CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Direct' THEN 'Direct Investment' else CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Group' AND @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') THEN 'Group P&A' ELSE CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Group' THEN 'Group Pensions' ELSE CASE WHEN @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') AND IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = ('IFA Specialist') THEN 'IFA Specialist - P&A' ELSE CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = ('IFA Specialist') THEN 'IFA Specialist - WM' ELSE IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 END END END END END END END

So, i think I am ending it correctly. It certainly parses OK. It is just when i go to use them in a report. I get error message ORA-00933: SQL command not properly ended.


If it parses, do i assume SQL is OK??
Back to top
Dennis W. Disney
Forum Associate
Forum Associate



Joined: 17 Sep 2003

speaker.gif
Posts: 504
Location: North Dallas, TX


flag
PostPosted: Mon Mar 12, 2007 12:39 pm 
Post subject: Re: ORA-00933: SQL command not properly ended :-933

DanJ wrote:
The actual select statement for one of my dimensions is
CASE WHEN @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') AND IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Direct' THEN 'Direct Protection' else CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Direct' THEN 'Direct Investment' else CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Group' AND @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') THEN 'Group P&A' ELSE CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = 'Group' THEN 'Group Pensions' ELSE CASE WHEN @Select(Product Group\MIR Grouped Products) IN ('Protection', 'Individual Annuities') AND IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = ('IFA Specialist') THEN 'IFA Specialist - P&A' ELSE CASE WHEN IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 = ('IFA Specialist') THEN 'IFA Specialist - WM' ELSE IAK1MAST.CONREPMAST_SALES_CHANNEL.SLES_CHNL_LEVEL2 END END END END END END END

So, i think I am ending it correctly. It certainly parses OK. It is just when i go to use them in a report. I get error message ORA-00933: SQL command not properly ended.


If it parses, do i assume SQL is OK??

I don't see why you have multiple levels for the CASE statment. Take out all of the "else CASE" and then you have only one "END" at the end.

_________________
BO4EBS - We provide Oracle EBS universes customized to your instance for a fixed price
Back to top
Anita Craig
Forum Groupie
Forum Groupie



Joined: 17 Jun 2002

Posts: 8541
Location: Palo Alto, California, U.S.A.


flag
PostPosted: Mon Mar 12, 2007 8:01 pm 
Post subject: Re: ORA-00933: SQL command not properly ended :-933

Dennis W. Disney wrote:
I don't see why you have multiple levels for the CASE statment. Take out all of the "else CASE" and then you have only one "END" at the end.

That is:

Code:
Case When  a Then a1
     When  b Then b1
....
     When  z Then z1
End

_________________
Anita Craig Image link
Institutional Research & Decision Support
Stanford University Image link
____________________
Search is Your Friendô
Back to top
DanJ
Senior Member
Senior Member



Joined: 20 Nov 2006

Posts: 62



PostPosted: Tue Mar 13, 2007 5:47 am 
Post subject: Re: ORA-00933: SQL command not properly ended :-933

Ok, thanks for that.. it seems to have worked.

I understand that the original case statement could have been more efficient.. and that i had a plethora of ELSE CASE WHEN. I have removed these and it now appears to run.

I dont know what fixed it though. I would have thought my SQL was just inefficient, not wrong. Each Dim parsed. It was when i used them with other dimensions i got the error message "ORA-00933: SQL command not properly ended :-933"

Thanks again.. either way it now runs. Thanks as always.
DJ
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0533 seconds using 17 queries. (SQL 0.0100 Parse 0.0344 Other 0.0089)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo