Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Help comparing '01234' with '1234' (with complications)

    I have to join on two fields, both are of type char(8), but one is entered by the application so it maintains leading 0's while the other is hand entered, so there are no leading 0's (and we're never going to get our users to ALWAYS enter leading 0's).

    So, since they're numbers anyway, I figured I would just convert them both to integers and join on that as in:
    Code:
    ...CAST(ord_no1 as integer) = CAST(ord_no2 as integer)
    Which works , BUT...

    Since the ord_no field is actually a char(8) field, users can enter stuff other than numbers in it. Is there a way that I can use CAST as in the above, but let it ignore values that can not be converted? If it's not all numberals, it won't match anyway so I don't need to worry about them, however, when joining on that field, it has to look at all the records and perform the calculation and it's failing on a handful of them.

    Any help would be greatly appreciated, thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did we get past 1st normal form?



    SELECT ord_no1, ord_no2
    FROM myTable99
    WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    How can I either:

    1. Convert to Integer, but gracefully ignore values that don't convert

    OR

    2. Convert to something like varchar or something that will let me join on '01234' and '1234 '

    Thank you.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT OFF
    CREATE TABLE myTable99(ord_no1 char(8), ord_no2 char(8))
    GO
    
    INSERT INTO myTable99(ord_no1, ord_no2)
    SELECT '01234','1234' UNION ALL
    SELECT 'X1234','1234' UNION ALL
    SELECT '6789','6789' UNION ALL
    SELECT 'Brett','Brett'
    
    SELECT *
    FROM ( SELECT * 
    	 FROM myTable99 
    	WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1) AS XXX
    WHERE CONVERT(int,ord_no1) = CONVERT(int,ord_no2)
    UNION ALL
    SELECT *
    FROM myTable99 
    WHERE (ISNUMERIC(ord_no1) = 0 OR ISNUMERIC(ord_no2) = 0)
      AND ord_no1 = ord_no2
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    THANK YOU!!!!


    I can't believe I'm doing this because I have a lot of issues with my accounting systems DB design, but it is in normal form.

    When performing a warehouse transfer, a inventory issuance is entered for the warehouse the product is shipping from with ord_no = 1000 and doc_ord_no = 1000.

    Then, an inventory receipt is entered for the warehouse the product is shipping to with ord_no = 1001 and doc_ord_no = 1000 which relates that receipt with the issuance.

    I am trying to create a transfers report so I can list all the issuances and then do a join between ord_no and doc_ord_no (limiting the second table to only receipts) to show which transfers have arrived and which are in transit.

    Thank you again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •