Just joined this forum today and got a question on "joining tables" .
I was facing difficulty in joining two tables in Oracle(Any join or simple nested query) where the RELATED COLUMN in one of the table has same data but with a specific prefix.
For example :
Table 1: Account(Related column : Event_ID)
Table 2: Transaction(Related column : Transaction_ID)
Event_ID contains : 10, 12, 20, 50, 100......
Transaction_ID contains(same number but with a prefix) : PK10, PK12, PK50, PK100......
In simple situation, it would have been like this:
select * from Account AC, Transaction TR where AC.Event_ID = TR.Transaction_ID
However since Transaction_ID has a prefix, how to join these tables ?
Hi MMKirushna, Thank you very much for responding. Howver this query is not working, giving error : Invalid number. I have also tried with removing quotes and adding double quotes - same error.
Also should the condition be the other way? (Even that's not working as well): TR.Transaction_IDA = 'PK'+ AC.Event_ID
(To remove confusion, I re-labelled the prefix from 'AC' to 'PK')
Rahul, while doing concatenation make sure both arguments are same data type 'PK' and AC.Event_ID
AC.Event_ID should be Char/varchar data type otherwise cast the column to char datatype as below
'PK'+ CAST(AC.Event_ID as varchar(10)