CODEWITHSUNDEEP
sqloracle-databasejoin
Bisma
Bisma

Reputation: 361

cannot implement this inner join in oracle

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.

Upvotes: 0

Views: 156

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

pifor
pifor

Reputation: 7882

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.

Upvotes: 0

Related Questions