Home » RDBMS Server » Server Administration » simple select
simple select [message #370596] Tue, 04 January 2000 11:00 Go to next message
MAG
Messages: 3
Registered: December 1999
Junior Member
I have this query and It isn't work. Have you know any idea why ? I had have tests and It give me the error "is not a valid month" and I don't know in which line.

thank you

select b.segment1, b.segment2, b.segment7, b.segment4, b.segment5, b.segment6, b.segment3,
b.segment21, segment22, b.segment14,
TO_DATE(SUBSTR(SEGMENT10,1,3)||
DECODE(SUBSTR(SEGMENT10,4,3),
'ENE','JAN',
'ABR','APR',
'AGO','AUG',
'DIC','DEC',
SUBSTR(SEGMENT10,4,3))||
SUBSTR(SEGMENT10,7,5),'DD-MON-YYYY'),
TO_DATE(SUBSTR(SEGMENT11,1,3)||
DECODE(SUBSTR(SEGMENT11,4,3),
'ENE','JAN',
'ABR','APR',
'AGO','AUG',
'DIC','DEC',
SUBSTR(SEGMENT11,4,3))||
SUBSTR(SEGMENT11,7,5),'DD-MON-YYYY')
from per_person_analyses a, per_analysis_criteria b
where a.analysis_criteria_id = b.analysis_criteria_id
and rtrim(a.person_id) = rtrim(2581)
and b.id_flex_num=50374
Re: simple select [message #370598 is a reply to message #370596] Tue, 04 January 2000 11:37 Go to previous message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

if you want to identify the wrong data
create the function below in sqlplus

create or replace function isDate(segment_in in varchar2)
return varchar2
is
   v_date  date;
begin
  select to_date(substr(segment_in,1,3)||
         decode(substr(segment_in,4,3),
         'ENE','JAN',
         'ABR','APR',
         'AGO','AUG',
         'DIC','DEC',
         substr(segment_in,4,3))||
         substr(segment_in,7,5),'dd-mon-yyyy')
   into v_date
   from dual;
   
   return ('true');
exception
   when others then
      return ('false');
end;
/
show errors

and call it like

select segment10 from ....
where isdate(segment10) = 'false';

bye
Previous Topic: how do you use a CLOB datatype in PL/SQL function?
Next Topic: view error
Goto Forum:
  


Current Time: Sat Oct 31 21:04:09 CDT 2020