0

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.

Improve this question
3
  • 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. How to Ask help center PS Please in code questions give a minimal reproducible example--cut & paste & runnable code, etc. Ask about the first subexpression that returns a value you don't understand. Commented Jun 13, 2020 at 7: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. Commented Jun 13, 2020 at 7: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 minimal reproducible example & 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. Commented Jun 13, 2020 at 8:08

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much! That very well explains it.
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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.