OraFAQ Forum: Forms » see my code and error

Home » Developer & Programmer » Forms » see my code and error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

see my code and error [message #173356]

Mon, 22 May 2006 01:39

mfa786
Messages: 210
Registered: February 2006
Location: karachi

Senior Member

hi master
sir i put this code in when-button-press
but system give me error

this is my code

declare
curryear number;
begin
select yearid into curryear from yeartable where closingstatus='T';
if curryera >0 then
delete opbaltable where yearid=curryear+1;
insert into opbaltable (chofacc,dropbal,cropbal,yearid)
(select chart_of_account.chofacc,
case when
nvl(open_debit+sum(journal.debit),0) >nvl(open_credit+sum(journal.credit),0)
then
nvl(open_debit+sum(journal.debit),0) -nvl(open_credit+sum(journal.credit),0)
end drbal,
case when
nvl(open_debit+sum(journal.debit),0) <nvl(open_credit+sum(journal.credit),0)
then
nvl(open_credit+sum(journal.credit),0) - nvl(open_debit+sum(journal.debit),0)
end crbal,curryear
from chart_of_account,journal where chart_of_account.chofacc=journal.chofacc group by chart_of_account.chofacc,open_debit,open_credit having nvl(open_debit+sum(journal.debit),0)>0 or nvl(open_credit+sum(journal.credit),0)>0);
else if
commit;
end;

this is my error
Error 103 at line 55, column 1
Encountered the symbol “CASE” when expecting one of the following :
(- + mod null <an identitier>

please give me idea how i use this

thank you

aamir

Report message to a moderator

Re: see my code and error [message #173359 is a reply to message #173356]

Mon, 22 May 2006 01:52

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you change

"IF curryera > 0" into "if currYEAR > 0"

and

"else if" into "end if;"

this would compile; I don't know will the procedure do what it is intended to do.

DECLARE
   curryear   NUMBER;
BEGIN
   SELECT yearid
     INTO curryear
     FROM yeartable
    WHERE closingstatus = 'T';

   IF curryear > 0
   THEN
      DELETE      opbaltable
            WHERE yearid = curryear + 1;

      INSERT INTO opbaltable
                  (chofacc, dropbal, cropbal, yearid)
         (SELECT   chart_of_account.chofacc,
                   CASE
                      WHEN NVL (open_debit + SUM (journal.debit), 0) >
                             NVL (open_credit + SUM (journal.credit),
                                  0)
                         THEN   NVL (open_debit + SUM (journal.debit),
                                     0
                                    )
                              - NVL (open_credit + SUM (journal.credit), 0)
                   END drbal,
                   CASE
                      WHEN NVL (open_debit + SUM (journal.debit), 0) <
                             NVL (open_credit + SUM (journal.credit),
                                  0)
                         THEN   NVL (open_credit + SUM (journal.credit),
                                     0
                                    )
                              - NVL (open_debit + SUM (journal.debit), 0)
                   END crbal,
                   curryear
              FROM chart_of_account, journal
             WHERE chart_of_account.chofacc = journal.chofacc
          GROUP BY chart_of_account.chofacc, open_debit, open_credit
            HAVING NVL (open_debit + SUM (journal.debit), 0) > 0
                OR NVL (open_credit + SUM (journal.credit), 0) > 0);
   END IF;

   COMMIT;
END;

Report message to a moderator

give me error on case word [message #173365 is a reply to message #173359]

Mon, 22 May 2006 02:21

mfa786
Messages: 210
Registered: February 2006
Location: karachi

Senior Member

sir
i put this code

but give me error on case word

DECLARE
curryear NUMBER;
BEGIN
SELECT yearid
INTO curryear
FROM yeartable
WHERE closingstatus = 'T';

IF curryear > 0
THEN
DELETE opbaltable
WHERE yearid = curryear + 1;

INSERT INTO opbaltable
(chofacc, dropbal, cropbal, yearid)
(SELECT chart_of_account.chofacc,
?? error point <<<< CASE >>>>>
WHEN NVL (open_debit + SUM (journal.debit), 0) >
NVL (open_credit + SUM (journal.credit),
0)
THEN NVL (open_debit + SUM (journal.debit),
0
)
- NVL (open_credit + SUM (journal.credit), 0)
END drbal,
CASE
WHEN NVL (open_debit + SUM (journal.debit), 0) <
NVL (open_credit + SUM (journal.credit),
0)
THEN NVL (open_credit + SUM (journal.credit),
0
)
- NVL (open_debit + SUM (journal.debit), 0)
END crbal,
curryear
FROM chart_of_account, journal
WHERE chart_of_account.chofacc = journal.chofacc
GROUP BY chart_of_account.chofacc, open_debit, open_credit
HAVING NVL (open_debit + SUM (journal.debit), 0) > 0
OR NVL (open_credit + SUM (journal.credit), 0) > 0);
END IF;

COMMIT;
END;

Report message to a moderator

Re: give me error on case word [message #173368 is a reply to message #173365]

Mon, 22 May 2006 02:34

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Does your Oracle version support CASE? If not, you might substitute it with the DECODE.

Report message to a moderator

Previous Topic:	how get privous row value in grid
Next Topic:	a strange requirement in FORMS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:30:54 CDT 2024