Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2017
    Posts
    5

    Unanswered: Query on tables with different format

    Hi All.

    I need help with a query.
    I have 2 tables. I need to join the tables based on Account field.

    In on the table the Account number displays in Hex decimal (we need to use HEX(account) to view the data.
    On the other table I can see it directly.

    Now join the tables by account number should I convert one of the account number fields.

    Please suggest your inputs.

    Regards,
    Naveen N

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Post the *datatype* (or DDL) of the Account field in both tables. Post your DB2-server version+platform (and fixpack ).

  3. #3
    Join Date
    Jul 2017
    Posts
    5
    HI,

    Please find below the data type. I am not sure of DB2 version.

    Host Format: PIC 9(9) COMP-3
    Data Type: INTEGER

    Host Format: PIC X(8)
    Data Type: CHAR (8)

    regards,
    naveen N

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Yes, you would convert the one from the table you access first on the join condition, so that you can still get index access on the other.

  5. #5
    Join Date
    Jul 2017
    Posts
    5
    Hi,

    Please suggest how we can convert, do we have any build in function.

    Should I write any program to do this.

    Regards,
    naveen N

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    You should be able to join on something like INTEGER(HEX(Account_on_HEX)) = Account_on_INT

    Depending on which DB2 version you are, you may have the possibility to create a function based index to improve the join.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Jul 2017
    Posts
    5
    Hi,

    Thanks for the update.

    I am getting the below error, please suggest how to resolve the errror

    DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT
    ACCEPTABLE TO THE DECFLOAT FUNCTION

    regards,
    Naveen N

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    Oh yes, HEX(Account) also has chars, so the INTEGER function fails.
    It would really help to post some sample data and what have you tried until now.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Jul 2017
    Posts
    5
    HI,

    When I query a table (packed decimal account number), we query like ANo = X'012345678F'

    The other table I quert by ANo = 12345678

    I am trying to use the below query, I am looking on how to convert

    SELECT DISTINCT (A.ANO)
    FROM table1 A
    LEFT JOIN
    table2 B
    ON A.ANO = B.ANO
    GROUP BY A.ANO

    Regards,
    Naveen N

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,540
    Provided Answers: 11
    Host Format: PIC 9(9) COMP-3 : this is a cobol declare
    Data Type: INTEGER

    Host Format: PIC X(8)
    Data Type: CHAR (8)

    if table is declared as integer you should not cast to decimal compact and hex function would not be needed
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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