cannot implement this inner join in oracle

Question

I have recently started to learn oracle, and I am having difficulty understanding this inner join on the tables.

INSERT INTO temp_bill_pay_ft
SELECT DISTINCT
    ft.ft_id,
    ft.ft_credit_acct_no,
    ft.ft_debit_acct_no,
    ft.ft_stmt_nos,
    ft.ft_debit_their_ref,
    ft.ft_date_time
FROM
    funds_transfer_his ft
    INNER JOIN temp_bill_pay_lwday_pl  dt 
   ON ft.ft_id = dt.ac_ste_trans_reference || ';1'
   AND ft.ft_credit_acct_no = dt.ac_id;

It is this line specifically which I dont understand, why do we use || here, I suppose it is for concatenation.

ON ft.ft_id = dt.ac_ste_trans_reference||';1'

Can somebody please explain to me this sql query. I would really appreciate it. Thank you.

This is too vague. What step don't you understand? Ask 1 specific researched non-duplicate question about it. Don't ask us to rewrite an SQL textbook, introduction or manual. [ask] [help] PS Please in code questions give a [mre]--cut & paste & runnable code, etc. Ask about the first subexpression that returns a value you don't understand. — philipxy, Jun 13 '20 at 07:43
sorry, if I hadnt framed my question properly, what I dont understand is what does this syntax particularly means here ft.ft_id = dt.ac_ste_trans_reference||';1' with reference to this query. I think, it concatenates the values in column ac_ste_trans_reference with string;1 and then checks if the value matches the values in column ft_id but I aint sure, if I have understoood it correct. — habiba, Jun 13 '20 at 07:59
Please clarify via edits, not comments. And you don't need us to find out what || means, it is a google away. Make a [mre] & show what is output for an example before you consider posting. PS Re "checks if ...": That's unclear. Say, the = call/expression returns whether ... is equal to .... PS Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS Read the manual or any introduction re the order that clauses are evaluated in & the order that an ON text is evaluated, etc. [Re inner join on.](https://stackoverflow.com/a/25957600/3404097) — philipxy, Jun 13 '20 at 08:08

score 2 · Accepted Answer · answered Jun 13 '20 at 11:33

This is string concatenation. The need is because there is a design error in the database and join keys are not the same in the two tables. So the data might look something like this:

ft_id             ac_ste_trans_reference
123;1                     123
abc;1                     abc

In order for the join to work, the keys need to match. One possibility is to remove the last two characters from ft_id, but I'm guessing those are meaningful.

I can speculate on why this is so. One possibility is that ft_id is really a compound key combined into a single column -- and the 1 is used to indicate the "type" of key. If so, then there are possibly other values after this:

ft_id
123;1
garbled;2
special;3

The "2" and "3" would refer to other reference tables.

If this is the situation, then it would be cleaner to have a separate column with the correct ac_ste_trans_reference. However that occupies additional space, and can require multiple additional columns for each type. So hacks like the one you see are sometimes implemented.

Thank you so much! That very well explains it. – habiba Jun 13 '20 at 14:59 — habiba, Jun 13 '20 at 14:59

score 0 · Answer 2 · answered Jun 13 '20 at 06:46

0

Yes it is used for concatenation.

But only somebody having worked on this database model can explain what table data represent and why this concatenation is needed for this joining condition.

answered Jun 13 '20 at 06:46

pifor

7,419
2
8
16

so ac_ste_trans_reference||';1' means that it concatenates the values in column ac_ste_trans_reference with string;1 and then checks if the value matches the values in column ft_id. – habiba Jun 13 '20 at 06:55

cannot implement this inner join in oracle

2 Answers2