Returning Selective numbers

• Neel 7777

  Right there with Babe

  Points: 778

  More actions

  January 28, 2013 at 1:43 am

  #273509

  Can anybody help me to return the actcode where last three digit don't contain 000 (three zero)

  SELECT COMCOD, ACTCODE, ACTDESC

  FROM ACINF

  WHERE (ACTCODE NOT IN

  (SELECT ACTCODE FROM ACTRNA)) AND (COMCOD = 3309)

  Returns :--

  COMCOD ACTCODE ACTDESC

  330910000000000DIRECT MATRIALS

  330910100000000DIRECT MATERIALS

  330910200000000M.S. ROD

  330910200100000M.S.ROD X-TREAM

  330910200101000M.S.ROD X-TREAM 500

  330910200200000M.S.ROD 60 GRADE D/BAR

  330910200201000M.S.ROD 60 GRADE D/BAR

  330910200201003M.S.ROD 60 GRADE  12MM

  330910200201004M.S.ROD 60 GRADE  16MM

  330910200201005M.S.ROD 60 GRADE  20MM

  330910200202000M.S. ROD 40 GRADE

  330910300000000CEMENT

  330910300100000CEMENT

  330910300101000CEMENT (GREY)

  330910300102000CEMENT (WHITE)

  330910300102001CEMENT (WHITE)

  330910400000000SAND

  330910400100000SAND

  330910400101000FILLING SAND

  330910400102000MEDIUM SAND

  330910400102001MEDIUM SAND FM-1.2

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 28, 2013 at 1:45 am

  #1581455

  What datatype is actcode?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Neel 7777

  Right there with Babe

  Points: 778

  More actions

  January 28, 2013 at 4:22 am

  #1581539

  nchar

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 28, 2013 at 5:20 am

  #1581559

  zahid_7777 (1/28/2013)

  ---

  nchar

  Thanks.

  WHERE RIGHT(ACTCODE,3) <> '000'

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Neel 7777

  Right there with Babe

  Points: 778

  More actions

  January 28, 2013 at 8:39 pm

  #1581876

  Thank you very much I will try.