Home » RDBMS Server » Server Administration » simple select
| simple select [message #370596] |
Tue, 04 January 2000 11:00  |
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  |
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
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 08:58:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
