Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Compiles with warnings if I include an IF statement (Oracle 9.2.0.4)
Procedure Compiles with warnings if I include an IF statement [message #420987] Thu, 03 September 2009 10:07 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

I have a Soted procedure which compiles without problem until I include an IF statement around an 'CURSOR IS <SQL goes here>' statement.

the first bit of the stored procedure, which includes the new IF statement is as follows:
create or replace
procedure p_glpost (i_entity IN varchar2, i_transType IN varchar2,  i_startDate IN VARCHAR2, i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_incInternal in VARCHAR2, i_chargeable in VARCHAR2, i_trialPost in VARCHAR2, i_postReport in

  v_overallMatchCount NUMBER DEFAULT 0;
  v_misMatch          NUMBER DEFAULT 0;
  v_matchCount        NUMBER DEFAULT 0;
  v_matchID           NUMBER DEFAULT 0;
  --declare run-time parameters
  v_fromDate VARCHAR2(10):= substr(i_startDate,  9, 2)||'/'||substr(i_startDate,  6,2)||'/'||substr(i_startDate,  1, 4);
  v_toDate VARCHAR2(10):=substr(i_endDate,  9, 2)||'/'||substr(i_endDate,  6,2)||'/'||substr(i_endDate,  1, 4);
  v_accountPeriod VARCHAR2(10):=substr(i_accountPeriod,  9, 2)||'/'||substr(i_accountPeriod,  6,2)||'/'||substr(i_accountPeriod,  1, 4);
  v_entity   VARCHAR2(10):= i_entity;
  v_transType VARCHAR2(1):= i_transType;
  
   if v_transType = 'L' or  v_transType = 'X' or  v_transType = 'E' then

  CURSOR c_wip
  IS
    SELECT TO_CHAR(wip.transno)                        AS TRANSNO,
    CASE WHEN wip.transtype = 'L' then 'LABOUR'
    WHEN wip.transtype = 'X' then 'EXPENSE'
    WHEN wip.transtype = 'E' then 'EQUIPMENT'
    WHEN wip.transtype = 'M' then 'MATERIAL'
    WHEN wip.transtype = 'A' then 'ADJUSTMENT'
    WHEN wip.transtype = 'AU' then 'WRITEUP'
    WHEN wip.transtype = 'AD' then 'WRITEDOWN'
    end as TRANSTYPE,


      wip.entity,
      wip.charge_code,
      wip.company_code,
      wip.project_code,
      wip.projectclass,
      wip.resource_code,
      wipv.currency_code,
      wipv.amount,
      cst.dsti_revenue_class,
      wip.wipclass,
      wip.clientclass,
      wip.departcode
    FROM niku.ppa_wip wip,
      niku.ppa_wip_values wipv,
      niku.pac_mnt_projects pac,
      niku.odf_ca_project cst
    WHERE wip.entity       = v_entity
    AND cst.id             = pac.id
    AND wip.transtype      = v_transType
    AND wip.transno        = wipv.transno
    AND wipv.currency_type = 'HOME'
    AND pac.project_code = wip.project_code
    AND (wip.transdate BETWEEN to_date(v_fromDate, 'dd/mm/yyyy') AND to_date(v_toDate, 'dd/mm/yyyy'))
    AND NOT EXISTS
      (SELECT gl.transno
      FROM niku.dsti_gl_control gl
      WHERE gl.transno = wip.transno);

  r_wip c_wip%ROWTYPE;  
else  ......




The IF statement is qualifying an input parameter.
Basically I want to examine the parameters and run one of two different SQL scripts depending on the value.

The errors I seem to be getting back from the compiler are:

Error(32,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following:     begin function package pragma procedure subtype type use    <an identifier> <a double-quoted delimited-identifier> form    current cursor The symbol "begin" was substituted for "IF" to continue. 

Error(34,10): PLS-00103: Encountered the symbol "C_WIP" when expecting one of the following:     := . ( @ % ; 

Error(75,3): PLS-00103: Encountered the symbol "END" when expecting one of the following:     begin function package pragma procedure subtype type use    <an identifier> <a double-quoted delimited-identifier> form    current cursor 


can anybody advise please?
thanks,
Matt

Re: Procedure Compiles with warnings if I include an IF statement [message #420988 is a reply to message #420987] Thu, 03 September 2009 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't put IF statements in the declare section.

You can conditionaly open/fetch/close cursors - which is what you should be doing.
You can't conditionaly declare them.

And next time you post code can you please format it so it doesn't scroll off the side of the screen.
Re: Procedure Compiles with warnings if I include an IF statement [message #420991 is a reply to message #420987] Thu, 03 September 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to do that?
What's the problem if you declare the cursor and don't use it?

Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Procedure Compiles with warnings if I include an IF statement [message #420995 is a reply to message #420991] Thu, 03 September 2009 10:43 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Apologies for the formatting issues.

I want to do what I am trying to do, because I need the procedure to look at a different set of tables depending on one input parameter.

I'm assuming that declaring cursors actually performs the database query, so I was hoping to maintain performance by only letting it execute one cursor declaration instead of two.

The SP code after the example I pasted opens up the cursor and loops throught the results (and in turn looping through anothert set of results stored to another cursor)

I guess I will just have to declare all 3 cursors and in the BEGIN section, use an IF statement to use one of the 2 main cursors (in my original question)


Thanks for your help Razz

Regards,
Matt





Re: Procedure Compiles with warnings if I include an IF statement [message #420996 is a reply to message #420995] Thu, 03 September 2009 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm assuming that declaring cursors actually performs the database query,
You assume incorrectly.
Re: Procedure Compiles with warnings if I include an IF statement [message #421021 is a reply to message #420987] Thu, 03 September 2009 12:38 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Unless my eyes deceive me in the mess of a code posting, I don't see any close parenthesis for the input parameters nor th datatype for the last parameter (in addition to all the other things previously mentioned).

[Updated on: Thu, 03 September 2009 12:39]

Report message to a moderator

Re: Procedure Compiles with warnings if I include an IF statement [message #421077 is a reply to message #421021] Fri, 04 September 2009 02:37 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

no need to be rude.
The close parenthesis are there in my SP - that must have been a copy paste error.

the only bit that stopped the SP working was when I tried to use an IF in the declaration section.
I am new to SP and PL/SQL so was unaware that I could not use IF in that place.

If the Cursor declaration does not run the query, then it must execute when the cursor is OPENED.
Please can someone confirm.

Thanks for your help,
Matt

Re: Procedure Compiles with warnings if I include an IF statement [message #421079 is a reply to message #421077] Fri, 04 September 2009 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
matpj wrote on Fri, 04 September 2009 09:37
no need to be rude.
The close parenthesis are there in my SP - that must have been a copy paste error.

the only bit that stopped the SP working was when I tried to use an IF in the declaration section.
I am new to SP and PL/SQL so was unaware that I could not use IF in that place.


If you post code here and ask why it is not working, don't protest if people tell you why it is not working. joy_division showed you a flaw in your code. The fact that you posted something different than your code cannot be blamed on him.
Re: Procedure Compiles with warnings if I include an IF statement [message #421086 is a reply to message #421079] Fri, 04 September 2009 03:17 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

ok fair enough, but I did state that it compiles fine until I add an IF statement.

anyway, i'm not going to get into a pointless argument.
I saught help.
I found the reason why it was not working (IF statement used in the wrong place) and I am very grateful for everyone's time in helping me with this.

Matt
Re: Procedure Compiles with warnings if I include an IF statement [message #421195 is a reply to message #421086] Sat, 05 September 2009 08:00 Go to previous message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
If the Cursor declaration does not run the query, then it must execute when the cursor is OPENED.
Please can someone confirm.



Yes you are in right direction now......
Previous Topic: condition required in trigger
Next Topic: comparing single value with many values at one go
Goto Forum:
  


Current Time: Sat Dec 10 08:42:04 CST 2016

Total time taken to generate the page: 0.06115 seconds