compile warning

[nelco]

I am getting warnings when I am compiling my package: Warning(70,17): PLW-07204: conversion away from column type may result in sub-optimal query plan.
The errors are on these lines:

sysdate >= (oh.latest_ship_date+70)
and sysdate <= (oh.latest_ship_date+120)


How can I fix these?.

Thanks in advance, Nelco

 

[carp]

My guess is the optimizer is trying to tell you that you are telling it to ignore an index.
Try changing the math to the sysdate side:


sysdate - 70 >= oh.latest_ship_date
and sysdate - 120 <= oh.latest_ship_date

 

[carp]

Even if there is no index on your column, the above will improve performance. Only two date calculations (sysdate - 70/120) are required, whereas your original code would need to make two calculations on each row.

I think it would also be more readable as
oh.latest_ship_date BETWEEN (sysdate - 120) AND (sysdate - 70)
which would correspond more closely with the requirement
"shipped between 70 and 120 days ago".

 

[nelco]

Thanks for answering my query. I was out of office for the past 2 days.
I tried with the above suggested query but I stll get the same warning when I compile the body.

I am using sql developer 11.1.1.54.40.

My package works fine but I was just wondering, if I can avoid warnings.

thanks, Nelco

 

[Thargy]

Can you confirm that the dates are being stored in date fields and not some other data type?

Regards

T

 

[nelco]

Data type is Date for latest_ship_date.

 

[carp]

And if you comment out that condition, does the warning go away?
If so, then another approach might be to declare two date variables and set them to sysdate - 70 and sysdate - 120. Then use these variables in the query.

 

[nelco]

Warnings go away, if I comment out that condition.

I will try to create two date variable and see if that helps.

Thanks, Nelco

 

[nelco]

By declaring two variable as s_date(sysdate-70) and e_date (sysdate-120) and then checking
s_date >= oh.latest_ship_date
and e_date <= oh.latest_ship_date

When I compiled above codes, It compiled and did not get any warnings.

I appreciate your help and this approach will be more efficient as I do not get any warnings.