cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a store procedure

Chris Frangandonis
Regular Advisor

Creating a store procedure

Hi All,

From my previous posting is was explained to me that I should create a function-based index in order to speed up my query either by using substr or right, but it cannot be done on sybase-IQ ver 12.5.
I questioned Sybase and they recommended to create a store procedure and redirect the info into a temp_table, then using the select statement to extract the info required from temp_table.

forums1.itrc.hp.com/service/forums/que
stionanswer.do?threadId=73355

My select for procedure

select STAT_CODE ,CHRG_UNIT,C_DUR,STATUS,DATA_TP
from CE.CDR_Chris
where right(FE_F,8) >= '$3' and right(FE_F,8) <= '$4' and PART_IND !='M' and PART_IND !='Q' and PART_IND !='P' and PART_IND !='E' and PART_IND !='H' and PART_IND !='D' and PART_IND !='Y'

FE_F = only the last 8 char is required.

And my select for temp_table

select STAC=STAT_CODE , RECORDS=count(*), UNITS=sum(CHRG_UNIT*.54),DURATION=sum(C_DUR)/10,
ERROR=sum(if STATUS = 'R' or STATUS = 'C' then 1 else 0 endif),REJ_ERRORS=sum(if STATUS = 'R' then 1 else 0 endif),
REJ_ERRORS_U=sum(if STATUS='R' and CHRG_UNIT > 0 and CHRG_UNIT != -1 then 1 else 0 endif), DATA_T=DATA_TP
from CE.CDR_Chris
where right(FE_F,8) >= '$3' and right(FE_F,8) <= '$4' and $ST and $DT
and PART_IND !='M' and PART_IND !='Q' and PART_IND !='P' and PART_IND !='E' and PART_IND !='H' and PART_IND !='D' and PART_IND !='Y'
and $TSN
group by STAT_CODE,DATA_TP
order by STAT_CODE


1)How to create a store procedure?
2)Will the store procedure do the trick.

Many Thanks
Chris