Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to close the IF blocks. (oracle 11g)
Please help me to close the IF blocks. [message #596297] Fri, 20 September 2013 01:43 Go to next message
ramya_162
Messages: 80
Registered: August 2013
Location: Banglore
Member
HI EXperts,

Please help me on closing the IF blocks properly in the below procedure.

CREATE OR REPLACE PROCEDURE sums_proc
(
                Id INTEGER,
		mtrname VARCHAR2
                mtrtype INTEGER,   
)
AS 
state  VARCHAR2(10); 
accesslevel INTEGER; 
asts NUMBER;
mtrtypesql LONG;
sumsql LONG;
BEGIN  
SELECT st_name INTO state FROM states_info WHERE st_name  = 'KARNATAKA';
SELECT acc_level INTO accesslevel FROM level_table WHERE level_id  = 4; 
IF (accesslevel = 4) THEN  
BEGIN  
asts :=4;
END;  
ELSIF (accesslevel = 3)  THEN 
BEGIN  
IF (OBJECT_ID('CANN_TABLE') IS NOT NULL)  THEN
 BEGIN  
EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE';  
END; 
EXECUTE IMMEDIATE 'CREATE TABLE CANN_TABLE';  
END;
ELSE 
BEGIN   
IF OBJECT_ID('MANN_TABLE') IS NOT NULL  THEN
BEGIN  
EXECUTE IMMEDIATE 'DROP TABLE MANN_TABLE';  
END; 
EXECUTE IMMEDIATE 'CREATE TABLE MANN_TABLE';
END;  
IF (mtrname IS NULL OR mtrname = '')  THEN
mtrname := '';  
ELSE  
mtrname := 'DEFAULT'; 
IF (mtrtype > 2) THEN
mtrtypesql := ' AND (mtr = 1) ' ;  
ELSE   
mtrtypesql := ' AND (mtr = 2) ';  
END IF;
IF (mtrtype = 1) THEN  
BEGIN  
mtrtypesql := 'SELECT SUM(amount) as amount FROM sales_table WHERE sales_type=''SALES''||mtrtypesql';  
END;  
ELSIF (mtrtype = 2) 
mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN''||mtrtypesql';   
END;  
IF (mtrtype = 4  OR mtrtype = 3) THEN 
BEGIN   
mtrtypesql := 'SELECT SUM(amount) as amount FROM margin_table WHERE margin_type=''MARGIN''||mtrtypesql';
END;  
ELSIF (mtrtype = 10 OR mtrtype = 15) THEN 
BEGIN     
mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE revenue_type=''REVENUE''||mtrtypesql';
END;         
ELSE  
BEGIN  
mtrtypesql := 'SELECT SUM(amount) as amount FROM revenue_table WHERE all_type=''ALL''||mtrtypesql';
END; 
sumsql := ' SELECT SUM(amount) FROM salesum_table';
EXECUTE IMMEDIATE mtrtypesql || ' UNION all ' ||sumsql ;
IF OBJECT_ID('CANN_TABLE') IS NOT NULL THEN 
BEGIN  
EXECUTE IMMEDIATE 'DROP TABLE CANN_TABLE';  
END; 
END IF;  
IF OBJECT_ID('MANN_TABLE') IS NOT NULL  THEN
BEGIN  
EXECUTE IMMEDIATE  'DROP TABLE MANN_TABLE';  
END;
END;


Thanks.
icon8.gif  Re: Please help me to close the IF blocks. [message #596299 is a reply to message #596297] Fri, 20 September 2013 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
If you want help, please format your code.
If you don't know how to do it, learn it using SQL Formatter.
In addition, if you proper indent your code you may then find where is the error.


[Updated on: Fri, 20 September 2013 01:53]

Report message to a moderator

icon13.gif  Re: Please help me to close the IF blocks. [message #596300 is a reply to message #596297] Fri, 20 September 2013 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is at least a comma error there:
(
                Id INTEGER,
		mtrname VARCHAR2
                mtrtype INTEGER,   
)


[Updated on: Fri, 20 September 2013 01:56]

Report message to a moderator

Re: Please help me to close the IF blocks. [message #596302 is a reply to message #596300] Fri, 20 September 2013 02:06 Go to previous messageGo to next message
ramya_162
Messages: 80
Registered: August 2013
Location: Banglore
Member
I have put the comma,

I am not able to format code using SQL formatter.

It's giving syntax error in code..

Please help me.

Thanks.
icon13.gif  Re: Please help me to close the IF blocks. [message #596303 is a reply to message #596302] Fri, 20 September 2013 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And it gives you where is the error, which line and column.

You added the missing comma but did you remove the comma in excess?


[Updated on: Fri, 20 September 2013 02:18]

Report message to a moderator

Re: Please help me to close the IF blocks. [message #596317 is a reply to message #596297] Fri, 20 September 2013 05:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1081
Registered: May 2013
Location: Hi-tech city
Senior Member
Not only that the IF-ELSE construct is improper at a lot of lines, but, also the use of BEGIN-END seems to be improper.
Re: Please help me to close the IF blocks. [message #596325 is a reply to message #596317] Fri, 20 September 2013 07:54 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
LONG datatype has been deprecated since V7 & should ALWAYS be avoided!
Re: Please help me to close the IF blocks. [message #596326 is a reply to message #596325] Fri, 20 September 2013 08:03 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
He. In the "What else is horribly wrong" category one should also mention to not drop and create tables on the fly.

I somehow have a hunch that the whole procedure could basically just be scrapped and replaced with two views named MANN_TABLE and CANN_TABLE.
Re: Please help me to close the IF blocks. [message #596327 is a reply to message #596297] Fri, 20 September 2013 08:13 Go to previous messageGo to next message
joy_division
Messages: 4453
Registered: February 2005
Location: East Coast USA
Senior Member
And what can a CREATE TABLE statement with no columns do other than cause an error.
Re: Please help me to close the IF blocks. [message #596328 is a reply to message #596327] Fri, 20 September 2013 08:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1081
Registered: May 2013
Location: Hi-tech city
Senior Member
There are more errors than the number of lines of code. I think it is a work of too much copy&paste without proper understanding. Only OP can tell us the intention or requirement.
Re: Please help me to close the IF blocks. [message #596331 is a reply to message #596328] Fri, 20 September 2013 12:58 Go to previous message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
My bet is that he has chosen to ignore the good advice given to him in previous posts and has decided to try to directly conver MySQL (IIRC) code into Oracle code, rather than take a look at the requirements and code for them
Previous Topic: retrieve list of tables with same name from two schemas
Next Topic: How Rowids are assigned to rows by Oracle?
Goto Forum:
  


Current Time: Thu Apr 17 06:31:04 CDT 2014

Total time taken to generate the page: 0.13209 seconds