Linking "text" item with "numeric" item -- Please HELP !

• finance_teacher

  SSC Eights!

  Points: 855

  More actions

  June 10, 2004 at 6:25 pm

  #180384

  How can I link the following ?

  SELECT     OFFERITEMS.UNITPRICE

  FROM         ITEMMAST INNER JOIN

                        OFFERITEMS ON ITEMMAST.EDPNO =

  SUBSTRING(OFFERITEMS.OFFERITEM, 1, 8)

  WHERE     (SUBSTRING(OFFERITEMS.OFFERITEM, 1, 8) = '130IN000')

  GROUP BY OFFERITEMS.UNITPRICE

  The main issue is that I am trying to

  link a 0000001 "text" edp from "offer-items"

  to a 1 "numeric" edp in "itemmast"

   1. use trim ?

   2. use cast/convert ?

   3. use temp table ?

  OfferItem Record Examples

   layout   = Offeritem, Price

   record 1 = 130IN00000000001, 25.25

   record 2 = 13GSD00000000002, 32.35

   record 3 = 130IN00000000052, 15.36

   record 4 = 13GSD00000002527, 15.63

   record 5 = 130IN00000001536, 34.36

   record 6 = 13GSD00000000035, 76.33

  ItemMast Record Examples

   layout   = Item, Description

   record 1 = 1, Cheese

   record 2 = 2, Bacon

   record 3 = 52, Salt

   record 4 = 2527, Candy

   record 5 = 1536, Cookies

   record 6 = 35, Pizza

• David Burrows

  SSC Guru

  Points: 65141

  More actions

  June 11, 2004 at 7:02 am

  #510035

  Use cast to convert text to int/numeric

  OFFERITEMS ON ITEMMAST.EDPNO = CAST(SUBSTRING(OFFERITEMS.OFFERITEM, 9, 8) as int)

  Far away is close at hand in the images of elsewhere.
  Anon.