on 08-04-2015 10:36 PM
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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.