Rectify the code [message #664844] |
Tue, 08 August 2017 08:39 |
|
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 #664852 is a reply to message #664851] |
Tue, 08 August 2017 09:57 |
|
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 #664856 is a reply to message #664852] |
Tue, 08 August 2017 12:50 |
joy_division
Messages: 4963 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 |
Bill B
Messages: 1971 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.
|
|
|