Home » SQL & PL/SQL » SQL & PL/SQL » Rectify the code
Rectify the code [message #664844] Tue, 08 August 2017 08:39 Go to next message
salam_shah
Messages: 4
Registered: August 2017
Junior Member
Hello dears

please corrrect my code accordingly as it gives me same result although if condition is also involved. code is as under;

DECLARE
BAL number;
s number:=9;
BEGIN

IF :FROM_DATE > '01-JUL-17'
THEN
SELECT SUM(nvl(OPENING,0)) INTO BAL FROM BANK_T
WHERE BANK_CODE NOT IN ('A','B');
ELSE
SELECT SUM(nvl(OPENING, 0))+s INTO bal FROM BANK_T;
END IF;
DBMS_OUTPUT.PUT_LINE(BAL);
END;
Re: Rectify the code [message #664846 is a reply to message #664844] Tue, 08 August 2017 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

'01-JUL-17' is a string not a DATE.
You should ALWAYS specify 4 digit Year.

results depend upon input value which you neglected to actually show us.
Re: Rectify the code [message #664848 is a reply to message #664846] Tue, 08 August 2017 08:57 Go to previous messageGo to next message
salam_shah
Messages: 4
Registered: August 2017
Junior Member
Dear BlacSwan thans for your reply my data source as under;

bank_code dat opening
B024 01-JUL-13 33840.48
B076 01-JUL-13 41158.1
B077 01-JUL-13 22102.45
B124 01-DEC-13 30000
A 01-JUL-17 500000
B 01-JUL-17 100000
Re: Rectify the code [message #664849 is a reply to message #664848] Tue, 08 August 2017 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Rectify the code [message #664850 is a reply to message #664849] Tue, 08 August 2017 09:07 Go to previous messageGo to next message
salam_shah
Messages: 4
Registered: August 2017
Junior Member
Hello dears

please corrrect my code accordingly as it gives me same result although if condition is also involved. code is as under;
 DECLARE
 BAL number;
 s number:=9;
 BEGIN

 IF :FROM_DATE > '01-JUL-17'
 THEN
 SELECT SUM(nvl(OPENING,0)) INTO BAL FROM BANK_T
 WHERE BANK_CODE NOT IN ('A','B');
 ELSE
 SELECT SUM(nvl(OPENING, 0))+s INTO bal FROM BANK_T;
 END IF;
 DBMS_OUTPUT.PUT_LINE(BAL);
 END; 
Re: Rectify the code [message #664851 is a reply to message #664850] Tue, 08 August 2017 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
SQL> @compare_strings
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   BEGIN
  3   IF '02-JUL-16'  > '01-JUL-17'
  4   THEN
  5   DBMS_OUTPUT.PUT_LINE('Left is greater than right');
  6   ELSE
  7   DBMS_OUTPUT.PUT_LINE('Right is greater than left ');
  8   END IF;
  9   IF '01-JUL-17'  > '31-JUL-16'
 10   THEN
 11   DBMS_OUTPUT.PUT_LINE('Left is greater than right');
 12   ELSE
 13   DBMS_OUTPUT.PUT_LINE('Right is greater than left ');
 14   END IF;
 15   END;
 16  /
Left is greater than right
Right is greater than left

PL/SQL procedure successfully completed.

SQL> 


explain why post results are expected & CORRECT.
Re: Rectify the code [message #664852 is a reply to message #664851] Tue, 08 August 2017 09:57 Go to previous messageGo to next message
salam_shah
Messages: 4
Registered: August 2017
Junior Member
please create table below and insert values then try above code again and see the result. thanks
CREATE TABLE BANK_t 
   (	"BANK_CODE" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"DAT" DATE NOT NULL ENABLE, 
	"OPENING" NUMBER);
  
  insert into bank_t 
  values 
('B024',	'01-JUL-13',	33840) , 
('B076',	'01-JUL-13',	41158.1),
('B077',	'01-JUL-13'	22102.45),
('B124',	'01-DEC-13'	30000),
('A',	'01-JUL-17',	500000),
('B',	'01-JUL-17',	100000)
Re: Rectify the code [message #664853 is a reply to message #664852] Tue, 08 August 2017 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
Oracle performs as designed & properly

explain why the results above are expected & CORRECT.
Re: Rectify the code [message #664856 is a reply to message #664852] Tue, 08 August 2017 12:50 Go to previous messageGo to next message
joy_division
Messages: 4777
Registered: February 2005
Location: East Coast USA
Senior Member
Your INSERT is invalid Oracle syntax.
Plus, look what happens when you rely on implicit conversion. You have been told that '01-JUL-13' is a string, not a date.

SQL> CREATE TABLE BANK_t
   (    "BANK_CODE" VARCHAR2(30 BYTE) NOT NULL ENABLE,
        "DAT" DATE NOT NULL ENABLE,
        "OPENING" NUMBER);  2    3    4

Table created.

SQL> insert into bank_t values ('B024',   '01-JUL-13',    33840);

1 row created.

SQL> select to_char(dat,'mm/dd/yyyy') from bank_t;

TO_CHAR(DAT,'MM/DD/YYYY')
---------------------------------------------------------------------------
07/13/2001
Re: Rectify the code [message #664945 is a reply to message #664856] Mon, 14 August 2017 06:49 Go to previous message
Bill B
Messages: 1687
Registered: December 2004
Senior Member
in production code ALWAYS store a date in a date column and never rely on implicit conversion of your date string. ALWAYS use to_date and a date format to convert.

For example

DECLARE
 BAL number;
 s number:=9;
 BEGIN

 IF :FROM_DATE > to_date('01-JUL-2017','DD-MON-YYYY')
 THEN
 SELECT SUM(nvl(OPENING,0)) INTO BAL FROM BANK_T
 WHERE BANK_CODE NOT IN ('A','B');
 ELSE
 SELECT SUM(nvl(OPENING, 0))+s INTO bal FROM BANK_T;
 END IF;
 DBMS_OUTPUT.PUT_LINE(BAL);
 END; 

This is assuming that your variable :FROM_DATE was declared as a date variable. If it is a string then you also need to put to_date with a format mask around it also.
Previous Topic: LPX-00230: invalid character 0 (U+0000) found in a Name or Nmtoken
Next Topic: Should I Clean Up My Collections?
Goto Forum:
  


Current Time: Mon Sep 25 21:09:12 CDT 2017

Total time taken to generate the page: 0.01967 seconds