Query regarding specific case in joining two tables

Hi Everyone,

Just joined this forum today and got a question on "joining tables" :slight_smile: .

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 ?

Thanks for your help in advance.

Rahul

Try this

select * from Account AC, Transaction TR where AC.Event_ID = 'AC'+ TR.Transaction_ID

1 Like

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)

1 Like

Hi

Please try this

select * from Account AC, Transaction TR where AC.Event_ID = replace(TR.Transaction_ID,'PK','')

also the datatypes have to match
AC.Event_ID
replace(TR.Transaction_ID,'PK','')

if they don't match need to use CAST

Please try let me know
Thanks

1 Like

who designed these tables? why is it designed as such?
do you want a hack or a solid sustainable, maintainable solution?

Thanks, I will try the Cast solution surely and inform you tomorrow, hopefully it should work.

Sure MM krishna, I would try this solution tomorrow and let you know. I am hoping this should work. Thanls a lot again.

I didn't get your purpose of asking.