ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Selection error involving field *N

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selection error involving field *N

    I am trying to find the salesman#'s in FILE A that are not in FILEB which also contains salesman #. The salesman# if FILEA is signed(3,0), the salesman # in FILEB is Char(3). Unfortunately, some of the rows in FILEB contain character values. When I use the following SQL

    select * from orinsls a
    where ORISNU not in
    (select
    cast(orslsm as numeric(3,0 )) from orhead where
    substring(digits(orcedt),5,2) ||
    substring(digits(orcedt),1,4) > '170101' )

    I get
    Selection error involving field *N

    Any ideas how I can ignore the rows that produce the errors or handle the error

  • #2
    Instead of casting the character salesman to numeric, cast the numeric salesman to character:
    Code:
    select * from orinsls a where digits(ORISNU) not in
    (select orslsm from orhead where substring(digits(orcedt),5,2) || substring(digits(orcedt),1,4) > '170101' )

    Comment


    • #3
      I assume it is not neccesary to use a sub-select:
      The follwoing SELECT statement should return what you want:
      Code:
      Select a.*
        from ONINSLS a Exception Join ORHEAD
             on     ORISNU = ORSLSM
                and Substr(Digits(ORCEDT, 5, 2) concat
                    Substr(Digits(ORCEDT, 1, 4) > '170101';
      Birgitta

      Comment


      • #4
        Originally posted by Brian Rusch View Post
        Instead of casting the character salesman to numeric, cast the numeric salesman to character:
        Code:
        select * from orinsls a where digits(ORISNU) not in
        (select orslsm from orhead where substring(digits(orcedt),5,2) || substring(digits(orcedt),1,4) > '170101' )
        Of course, that makes perfect sense

        Comment


        • Vectorspace
          Vectorspace commented
          Editing a comment
          Bear in mind that digits() will keep leading zero's. So for example, this: digits(cast(12 as decimal(3,0))) will return '012'. So if FILEB contained salesman# ' 12' or '12 ' instead of '012', it would not match a numeric 12 converted with digits to character.

      • #5
        Is there a way to ignore errors instead of having the query abort on the 1st time it finds a non numeric value it's trying to cast

        Comment

        Working...
        X