Join fields with different length?

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
rpinxt
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Join fields with different length?

Post by rpinxt » Tue Nov 26, 2013 4:53 am

Hi All,

I was trying in a query to join LFBK table with TIBAN table.
Nice thing is that these 2 tables have 3 same key fields.
So you would think that would be easy.

But to get a meaningful output the field Bank Account BANKN is key.
Both tables have these field so one would think 1+1=2

Unfortunately not. Somehowe the length of TIBAN-BANKN is 35 where the length of LFBK-BANKN is 18

Is there a way to use these fields in a join to make sure the vendor from the LFBK table gets the correct IBAN number from the TIBAN table?
Thanks.

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Join fields with different length?

Post by Gothmog » Tue Nov 26, 2013 5:09 am

In a join ? No.
You'll have to select the relevant fields of TIBAN as additional fields in your query.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

rpinxt
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Join fields with different length?

Post by rpinxt » Tue Nov 26, 2013 6:29 am

Hmm yeah I understand now what you mean ;).
But would that work with the 3 keys in both tables?
So if I would use coding to get the IBAN from TIBAN would it then be possible to set the coding for the 3 keys (BANK/BANKL/BANKN)?
Even if the length of BANKN differs?

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Re: Join fields with different length?

Post by Rich » Tue Nov 26, 2013 7:17 am

I had the same problem with something like sales orders nd delivery details although I can't remember which field.

If you want to do a join with fields that are not identical in length you can do so using Native SQL (ie enclosed in EXEC SQL/ EndExec) rather than in Open Sql.
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Join fields with different length?

Post by Gothmog » Tue Nov 26, 2013 8:22 am

Alas, he's working on queries.
Did I mention I hate queries? Well, I hate queries. :?
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

rpinxt
Posts: 146
Joined: Mon Jul 23, 2012 8:02 am

Re: Join fields with different length?

Post by rpinxt » Tue Nov 26, 2013 9:21 am

Hmm thats ok Gothmog.
I manage to get what I want with what you thaught me earlier this month:

Code: Select all

CLEAR RP_IBAN.
SELECT SINGLE IBAN FROM TIBAN
  INTO RP_IBAN
  WHERE BANKN EQ LFBK-BANKN.
What I only was wondering....could the "Where BANKN EQ LFBK-BANKN" also consist of more conditions?
I tried with this but that just gave me empty field:

Code: Select all

CLEAR RP_IBAN.
SELECT SINGLE IBAN FROM TIBAN
  INTO RP_IBAN
  WHERE BANKN EQ LFBK-BANKN AND
BANKL EQ LFBK-BANKL AND
BANKS EQ LFBK-BANKS.
So I guess that piece of code is not correct.

And just for the record. I am also not fond of queries.
I am working for a multinational but sadly enough for my department Queries are the best data gathering / reporting system we have...
Go figure.

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Join fields with different length?

Post by Gothmog » Tue Nov 26, 2013 9:43 am

I would use all the keys I got from LFBK to select the right IBAN : BANKS, BANKL, BANKN and BKONT.
Also, ensure that you did select all these fields form LFBK if you want to be able to use them as conditions in your select.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

Post Reply