Home » SQL & PL/SQL » SQL & PL/SQL » Query Failing with Invalid Number
Query Failing with Invalid Number [message #224341] Wed, 14 March 2007 00:14 Go to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
Query:
----------------------------------------------------
Select FemCondDimCmpDtlEO.COND_DIM_CMP_OBJ_DEF_ID,
FemCondDimCmpDtlEO.CRITERIA_SEQUENCE,
FemCondDimCmpDtlEO.HIERARCHY_OBJ_ID,
FemCondDimComponentEO.DIM_ID DimensionId,
xdim.dimension_varchar_label DimensionVacharLabel,
xdim.member_data_type_code MemberDataTypeCode,
null as node_display_code, null as calendar_dc,
null as dimension_group_dc,
to_number(null) as gl_period_num,
to_date(null) as cal_period_end_date,
FemCondDimCmpDtlEO.NODE,
FemCondDimCmpDtlEO.RELATION_CODE,
null as root_node_display_code,
FemCondDimCmpDtlEO.ROOT_NODE_ID,
FemCondDimCmpDtlEO.RELATIONSHIP_ID,
FemCondDimCmpDtlEO.DIM_ATTR_VARCHAR_LABEL,
attr.attribute_data_type_code,
attr.attribute_dimension_id,
attr_xdim.dimension_varchar_label as attr_dim_varchar_label,
attr_xdim.member_data_type_code as attr_dim_mem_data_type_code,
null as dim_attr_value_display_code,
null as dim_attr_value_date,
null as dim_attr_value_number, FEM_MIR_PKG.Get_Dim_Member_Display_Code('CALENDAR',calp.calendar_id)
as dim_attr_calendar_dc, FEM_MIR_PKG.Get_Dim_Group_Display_Code(xdim.dimension_varchar_label,calp.dimen
sion_group_id) as dim_attr_dimension_group_dc,
FEM_MIR_PKG.Get_Gl_Period_Num(calp.cal_period_id) as
dim_attr_gl_period_num,
FEM_MIR_PKG.Get_Cal_Period_End_Date(calp.cal_period_id) as
dim_attr_cal_period_end_date,
FemCondDimCmpDtlEO.DIM_ATTR_VALUE,
FemCondDimCmpDtlEO.OBJECT_VERSION_NUMBER
From FEM_COND_DIM_CMP_DTL FemCondDimCmpDtlEO,
fem_cond_dim_components FemCondDimComponentEO,
fem_xdim_dimensions_vl xdim,
fem_dim_attributes_b attr,
fem_xdim_dimensions_vl attr_xdim,
fem_cal_periods_b calp
WHERE FemCondDimComponentEO.cond_dim_cmp_obj_def_id =
FemCondDimCmpDtlEO.cond_dim_cmp_obj_def_id
and xdim.dimension_id = FemCondDimComponentEO.dim_id
and FemCondDimCmpDtlEO.dim_attr_varchar_label is not null
and attr.attribute_varchar_label = FemCondDimCmpDtlEO.dim_attr_varchar_label
and attr.dimension_id = FemCondDimComponentEO.dim_id
and attr_xdim.dimension_id = attr.attribute_dimension_id
and attr.attribute_dimension_id = 1

and calp.cal_period_id = FemCondDimCmpDtlEO.DIM_ATTR_VALUE
and FemCondDimCmpDtlEO.COND_DIM_CMP_OBJ_DEF_ID = 11450
--------------------------------------------------------------

The query is faling at 'and calp.cal_period_id = FemCondDimCmpDtlEO.DIM_ATTR_VALUE '

Note:
1)The datatype of DIM_ATTR_VALUE is 'VARCHAR' while calp.cal_period_id is Number.
2)DIM_ATTR_VALUE stores both number as well as non-number data.
3)The blue coloured line will filter to return only number values of this column.

The same query is working fine on our instances while erroring out on customer instance.I have attched the explain plans for both the instances.

Query logically looks to be fine but explain plan is different on our instance and customer instance. Pl. points us where we are missing.

Thanks in advance,
Puneet
  • Attachment: Explain.txt
    (Size: 3.31KB, Downloaded 214 times)
Re: Query Failing with Invalid Number [message #224342 is a reply to message #224341] Wed, 14 March 2007 00:27 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
how about...
and TO_CHAR(calp.cal_period_id) = TO_CHAR(FemCondDimCmpDtlEO.DIM_ATTR_VALUE)

Re: Query Failing with Invalid Number [message #224355 is a reply to message #224342] Wed, 14 March 2007 01:25 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
and TO_CHAR(calp.cal_period_id) = TO_CHAR(FemCondDimCmpDtlEO.DIM_ATTR_VALUE)

It will loose the index on calp.cal_period_id.Moreover i am not able to understand why this is happening as the rows having non numeric characters in (FemCondDimCmpDtlEO.DIM_ATTR_VALUE) are already filtered out with a join just before this.
Re: Query Failing with Invalid Number [message #224382 is a reply to message #224355] Wed, 14 March 2007 02:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How do you control the order of filters?
This is what you get if you store numbers in varchar2 columns...
Re: Query Failing with Invalid Number [message #224614 is a reply to message #224382] Wed, 14 March 2007 14:29 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
With some applications, you can't change the fact that numbers are stored in varchar2 columns. It a shame, awful, etc., but maybe we have to deal with it sometimes...

You might want to try to have the "filtering numbers clauses" in an inline view. It could be that the predicates are pushed inside that inline view, than it won't help, but it's worth a try.

If I understand your code correctly, this would be something like:
FROM   fem_cond_dim_cmp_dtl femconddimcmpdtleo
      ,fem_cond_dim_components femconddimcomponenteo
      ,fem_xdim_dimensions_vl xdim
      ,(SELECT *
        FROM   fem_dim_attributes_b
        WHERE  attribute_dimension_id = 1) attr
      ,(SELECT *
        FROM   fem_xdim_dimensions_vl
        WHERE  dimension_id = 1) attr_xdim
      ,fem_cal_periods_b calp
Re: Query Failing with Invalid Number [message #224695 is a reply to message #224614] Thu, 15 March 2007 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
skooman wrote on Wed, 14 March 2007 20:29
With some applications, you can't change the fact that numbers are stored in varchar2 columns. It a shame, awful, etc., but maybe we have to deal with it sometimes...

I know, you are right Sabine. I know of an application where we used to have this problem too; I rewrote it with 'a collegue' Wink
Re: Query Failing with Invalid Number [message #224832 is a reply to message #224341] Thu, 15 March 2007 11:39 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tried executing your sql in your customer environment excluding the problematic condition (comparing the number_value with character value) ? I had the same problem in the past and it is one of those things... Believe me oracle never lie ( I am not being sarcastic). We went spent nearly two days thinking that we have definitely filtered the values but unfortunately we haven't. In the end we came with this solution.

trim(
translate(
trim(<character_column>),
'0123456789. ',' ..'
)
)
is null

HTH
Previous Topic: Having Problem in using rownum, please help, Thanks
Next Topic: How to display rows in stored procedure?
Goto Forum:
  


Current Time: Wed Dec 07 20:18:38 CST 2016

Total time taken to generate the page: 0.12234 seconds