Home » SQL & PL/SQL » SQL & PL/SQL » error from compiling
error from compiling [message #319482] Sun, 11 May 2008 20:08 Go to next message
Taiwo
Messages: 7
Registered: September 2000
Location: Nigeria
Junior Member

I am trying to pull recharges for 60days from the two tables because march consists of 31 days and april 30days but it is giving error while compiling this is query below. The trunc aspect is giving error(encountered where when expected declare, begin, exception)

-- table MSISDN_RECHARGES_DC contains rechages for 60 days

INSERT INTO MSISDN_RECHARGES_DC
SELECT *
FROM MSISDN_RECHARGES_DC_APR
UNION
SELECT *
FROM MSISDN_RECHARGES_DC_MAR;

WHERE TRUNC(RECHARGE_DATE) > TO_DATE('20080301','yyyymmdd');

COMMIT;
Re: error from compiling [message #319483 is a reply to message #319482] Sun, 11 May 2008 20:12 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Things would be better if/when extraneous semicolon is removed.
Re: error from compiling [message #319485 is a reply to message #319483] Sun, 11 May 2008 20:23 Go to previous messageGo to next message
Taiwo
Messages: 7
Registered: September 2000
Location: Nigeria
Junior Member

Thanks but the query is actually in a PL/SQL block i copied that part out, if i uncomment the part below i don't get error but once i comment it i get error while compiling.

-- WHERE TRUNC(RECHARGE_DATE) > TO_DATE('20080301','yyyymmdd');
Re: error from compiling [message #319486 is a reply to message #319482] Sun, 11 May 2008 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Things would be better if/when extraneous semicolon is removed.
Re: error from compiling [message #319500 is a reply to message #319486] Mon, 12 May 2008 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
60days from the two tables because march consists of 31 days and april 30days

Is it 60 days, or is it 2 months? What I meant to say is that you could, perhaps, research use of the ADD_MONTHS function and use something like
WHERE TRUNC(recharge_date) > ADD_MONTHS(SYSDATE, -2)
(once you fix that extra semi-colon).
Re: error from compiling [message #319517 is a reply to message #319482] Mon, 12 May 2008 01:21 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
WHERE TRUNC(RECHARGE_DATE) > TO_DATE('20080301','yyyymmdd');
-- is the same as
WHERE RECHARGE_DATE >= TO_DATE('20080302','yyyymmdd');

I would rather avoid using TRUNC or any function on DATE column, as it cannot use index on that DATE column.
You can fix it by indexing TRUNC(RECHARGE_DATE), but I do not like this approach.
You can achieve the same by the adjusting (eg. by TRUNC) of the compared value.
Previous Topic: improve the performance of procedure
Next Topic: list dates from <some date> to sysdate
Goto Forum:
  


Current Time: Sat Dec 03 16:02:32 CST 2016

Total time taken to generate the page: 0.09346 seconds