Stored Procedure/Sub Report..???...Urgent please

[acr1]

All,
I am impressed with the replies I have read here recently in the last few weeks and obv plenty of Oracle gurus..

My question is as follows:....
I have a range of data that I need to report on as in the example as follows; Note that the SOR range is only part of the total SOR range (10000-99999)

Where the defect code = 1064 and SOR is in the range 18050 - 18999 (inclusive) then the fault code = 3133

Defect SOR Range Fault Code
1064 18050 - 18999 3133
1065 18050 - 18999 3131
1066 18050 - 18999 3200
1067 18050 - 18999 3132

Would I need to create a temporary table..that could be dropped/refreshed..??

I need this selection prior to running a report and I need to run a related query based on the data in the Fault Code field

Should I use a stored procedure or a subreport prior to a main report. I am using Oracle 8i and Seagate Info Vs7.0.
I have submitted before but have become unstuck...(wrong forum..) Any help would be greatly appreciated and/or assistance with the quickest result. this report needs to be run every month and their will be additions within the SOR range.

TIA

 

[acr1]

Anyone able to help...or am I on the wrong side of the world...
TIA

 

[sem]

Can you be more specific? Is your SOR Range field of varchar2 type and its data is really something like 18050 - 18999? Or you have a NUMBER SOR field and the range is some sort of condition?

 

[acr1]

Sem
Thanks for your response. The SOR field is varchar2(8) to allow for alphanumerics..(sorry) and the data is genuine numbers in the range from 18050 to 18999. Hope you can assist.
TIA
acr1

 

[Khayyam]

Hi.
I hope this will work

" SELECT sor from from table
Where
to_number(substr(Parameter_value,1,5))||
to_number(substr(parameter_value,7))
BETWEEN Parameter_value
AND Parameter_value
"

Try if some error come try to change it . I have done this before and it worked

 

[acr1]

Khayyam
Thanks for your help..the issue is that I need to report on this data (as at the start of my thread...). Do I need to create a temp table as the results of this output have to be reported on in a related query...
TIA
acr1

 

[cjmartin]

acr1,

Based on the information you have provided, I believe that either a stored proc/main report or a subreport/main report would work. I tend to favor Oracle stored procs over subreports in Crystal b/c of processing speed benefits and the simplicity of its exception handling features, among other reasons. But either can be scheduled to run monthly: If using stored proc/main report, then use dbms_jobs/Seagate Info; If using subreport/main reports, then use Seagate Info only.

Can you provide more detail on the "related query" you are needing to run monthly?

 

[sem]

I'm still not sure I understand you correctly, though try this:

select defect_code, min(to_number(sor))||'-'||max(to_number(sor)), fault_code from your_table group by defect_code, fault_code