Need help with where clause

• oradbguru

  Hall of Fame

  Points: 3126

  More actions

  June 20, 2012 at 5:41 pm

  #262610

  Hello, I have the following query in which I need help with the substring join:

  select

  x.labtest_key,

  x.ng_labtest_key,

  x.labtest_desc

  ng_labtest_desc

  from

  lab_tests_tbl x LEFT OUTER JOIN lab_tests_mstr_tbl m ON x.ng_labtest_key = m.ng_labtest_key

  LEFT OUTER JOIN up_ex_data_xref u ON substring(u.input_param, 2, len(u.input_param) - 2) = x.ng_labtest_key

  I throws the following when run:

  Msg 536, Level 16, State 5, Line 1

  Invalid length parameter passed to the SUBSTRING function.

  Is there a way around this?

  Thank you,

  David

• oradbguru

  Hall of Fame

  Points: 3126

  More actions

  June 20, 2012 at 6:41 pm

  #1503726

  I used the REPLACE function and it works as expected. Here is the solution I used:

  SELECT

  x.labtest_key,

  x.ng_labtest_key,

  x.labtest_desc

  m.ng_labtest_desc

  FROM

  lab_tests_xref_tbl x INNER JOIN lab_tests_mstr_tbl m ON x.ng_labtest_key = m.ng_labtest_key

  INNER JOIN up_ex_data_xref_tlb u ON REPLACE(u.input_param , '"' , '' ) = x.ng_labtest_key

  Thank you,

  David