cancel
Showing results for 
Search instead for 
Did you mean: 

Joining 2 columns with different data types

Former Member
0 Kudos

Hi All,

I have a doubt regarding a join condition, hope anyone of you can help in resolving it

1st column (ACCNO) in the join is always 8 digit numeric value and

2nd column (UNIT) has mostly numeric values (but with different lengths) and some string values(Alphabhets)

We want to implement a case statement to do this where if the column has numeric value then the join should convert the UNIT column to 8 length and connect to ACCNO else it should be null

How to find if the implement this as a join condition (does Digits function in IDT work for this?)

To change the length of 2nd column, I'm planning on using RIGHT('00000000' || B_LEDGER.UNIT,8)

Client Data base: DB2

Universe is built on IDT 4.0 SP04 version

Thanks in advance,

Mitch

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Performance will be horrendous. I'd recommend adding a new column and populating it appropriately.

If you can't, then take a look at Expression Based Indexing:


https://www.ibm.com/developerworks/community/blogs/DB2PLSQL/entry/db2_expression_based_indexes?lang=...

former_member285534
Active Participant
0 Kudos

Hi Mitch,

I believe this other strategy will work

1. Convert ACCNO to CHAR

2. Use your proposed technique to convert UNIT to a 8-character field with padded zeros.

3. JOIN the expressions

The join condition should look like this:

CHAR( CHAR( ACCNO ), 8 ) = RIGHT('00000000' || B_LEDGER.UNIT, 😎

Innermost CHAR converts ACCNO from numeric to char

Outermost CHAR trims the expression to 8 characters (maybe you won't need this one but I include it because I'm not pretty sure that the result of the innermost CHAR function will be of length 8).

Alphanumeric ocurrences of b_ledger.unit will be automatically discarded because they will not satisfy the join condition.

Regards,

Fernando

Former Member
0 Kudos

Thanks for your reply

Requirement is to make the join work only when UNIT has numeric value

However, I tried using the method you suggested (converting ACCNO to CHAR). All I'm getting for ACCNO value is NULL values and the UNIT is coming with all values including Alpha Alphanumerical

Former Member
0 Kudos

Why don't you convert your first column ACCNO to char and then append 0 on left.

First make sure what is the maximum length for any of the column.

Then using same you can convert the 1st column to character first and then append 0 on left for remaining characters.

Like wise for second column you can do that same.

Now both the columns are in character you can then join the same.

Thanks,

Swapnil

Former Member
0 Kudos

Changing the ACCNO col type at DB level is not possible, there are only few report that use this join condition and, there are others that need it as numeric column

This is the reason why we want to use CASE function at the join. Max length is 8

former_member285534
Active Participant
0 Kudos

Dear Swapnil,

I want to apologize with you because I suggested Mitch the same you had already suggested.  I hadn't realized until now that I've read the other thread and wondered what had happened there.  I've now read carefully this thread and realized that my reply was essentially the same than yours.  I want to tell you that this was not done intentionally and I feel very sorry (I really mean it).

Mitch,

Please if you find later that the advice is worth any points, they should be assigned to Swapnil not to me.

Thank you both

Fernando

Former Member
0 Kudos

You don't have to change it to database level.

You can do same at universe level.

Thanks,

Swapnil

Former Member
0 Kudos

Hi Fernando,

You don't need to apologise for that.. My post is first in sequence.

It's Ok.

Thanks,

Swapnil

Former Member
0 Kudos

When I tried converting the column to char and connect it. it was only giving NULL for ACCNO for all values of UNIT

we are having another reporting application where they are using the following function to do this join

accno=case qgpl.fn_sd_isdigits(unit) when '1' then cast(unit as decimal(8,0)) else null end

I want to implement this in IDT

Former Member
0 Kudos

What is your backend database?

Former Member
0 Kudos

We are using DB2

Reason for not using char() is that, it will mess up the indexing of the table as it's the primary key of the table

Former Member
0 Kudos

Is your Unit column will contain data other then Numeric??


Former Member
0 Kudos

Yes, but you can expression based indexing as I mentioned earlier . I agree that it's not best practice but it's a quick win if you've got a tight deadline. If you want to do it properly, add a column in the database that is of the correct data format.