Home » SQL & PL/SQL » SQL & PL/SQL » query process help
query process help [message #238089] Wed, 16 May 2007 12:09 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
The following query gives me the desired results for getting the case event number(in the err msg) events under 100.

select distinct substr(local_err_msg,47,10) case_sid, to_number(trim(substr(local_err_msg,82,2)),99) case_event
from err_log a
where local_err_msg like 'Case record containing case external number%'
and substr(local_err_msg,85,1) != ' '
and not exists (select null
from case_fact b
where substr(a.local_err_msg,47,10) = b.case_sid
and to_number(trim(substr(a.local_err_msg,82,2)),99)= b.case_evt_nbr)
order by substr(local_err_msg,47,10) desc;

err_msg; <100
Case record containing case external number = M991109346 and case event number = 3 had been rejected due to previous case event number = 2 not found in the database.

However, I can’t seem to get the case event number if it is over 100.
I tried,
to_number(trim(substr(local_err_msg,82,3)),999) case_event
I tried decode as well with no luck.

But if you look at the char count in the err msg, it picks up a char(h), and returns “invalid number”. I guess this happens when the number is < 10 and when I’m looking for (local_err_msg,82,3) case events over 100.
“9 -(82) ‘ ’-(83) (84)h ad” gives error invalid number

err_msg; >100
Case record containing case external number = M991109346 and case event number = 110 had been rejected due to previous case event number = 109 not found in the database.

Any suggestions on how to process case events over 100?


Thanks,
Mark S.
Re: query process help [message #238220 is a reply to message #238089] Thu, 17 May 2007 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could you actually put a paragraph at the top of your next problem explaining exactly what the problem you're getting is.
Something like
I'm trying to extract the first case number from a text string. I'm having problems doing this as the number is of a variable number of digits.


You need to look for the first space after the 82nd character, and get everything from chr 82 to there, like this:
with data_source as (select 'Case record containing case external number = M991109346 and case event number = 110 had been rejected due to previous case event number = 109 not found in the database' local_err_msg from dual
                     union all
                     select 'Case record containing case external number = M991109346 and case event number = 3 had been rejected due to previous case event number = 2 not found in the database.' local_err_msg from dual)
select to_number(substr(local_err_msg,82,instr(local_err_msg,' ',82)-82),'999') from data_source;


Re: query process help [message #238535 is a reply to message #238220] Fri, 18 May 2007 08:20 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
Yes I agree, sorry for the confusion. I've used instr function before, but never knew I could use (-82) to set it back to that position.

Thanks,
Mark S.
Re: query process help [message #238545 is a reply to message #238535] Fri, 18 May 2007 08:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm using Instr to return the absolute ofset of the first space in the string after the 82nd chr.
All the -82 is doing is converting that absolute offset to the offset from the 82nd character.
Previous Topic: what is short circuit evaluation
Next Topic: Holding tables
Goto Forum:
  


Current Time: Tue Dec 06 08:26:20 CST 2016

Total time taken to generate the page: 0.35427 seconds