1

This is the first table:

Line_Card
0-17
0-10
1-1
0-11
1-1-17
1-1-10
1-1-13
1-1-6
1-1-21

This is the second table:

Port
0-13-37
1-1-2-40
0-8-29
1-1-4-12
1-1-2-14
0-1-30
0-17-25
1-1-17-62
1-1-1-55

The below query is supposed to read from the product of the joined tables:

SELECT Line_Card, Port
FROM Table_1
LEFT JOIN Table_2 ON
Table_1.Column= Table_2.Column
             AND Table_1.Port ~~ LIKE_ESCAPE((Table_2.Line_Card || '-%'),'\')
             AND Table_1.Port !~~ LIKE_ESCAPE((Table_2.Line_card || '-%-%'),'\');

However, it shows an invalid SQL character error on oracle. It would be greatly appreciated if someone could also give me the equivalent of this statement or the infinity symbol in Teradata.

Omar
  • 9
  • 2
  • 4
    Has this query come [from PostgreSQL](https://stackoverflow.com/q/21925796/266304)? – Alex Poole Jul 03 '22 at 12:01
  • No, this is oracle – Omar Jul 03 '22 at 13:09
  • 2
    It isn’t valid syntax for Oracle. So where did it come *from*, and is your question really about converting a PostgreSQL query to Oracle dialect? (That linked question probably tells you what ~~ means, anyway.) – Alex Poole Jul 03 '22 at 13:38
  • Well the thing is that I am working through someone else's code and my understanding that this is oracle and that ~ has a purpose in oracle. The thing is that I can not find any reference to the double infinity ~~ symbol. – Omar Jul 04 '22 at 08:24
  • That's because it does not have a meaning in Oracle. Again, the link says they are PostgreSQL operators equivalent to LIKE and NOT LIKE, so should be simple to modify. (And `LIKE_ESCAPE()` looks like a bespoke function to escape wildcards - unclear if you need to replicate that.) – Alex Poole Jul 04 '22 at 08:55

1 Answers1

1

I am working through someone else's code and my understanding that this is oracle and that ~ has a purpose in oracle. The thing is that I can not find any reference to the double infinity ~~ symbol.

That's because it doesn't have any meaning in Oracle. It looks like this code is from PostgreSQL, where - as this answer says:

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

Even in PostgreSQL that implies you can just replace them with the LIKE and NOT LIKE equivalents; and that would work in Oracle too:

AND Table_1.Port LIKE Table_2.Line_Card || '-%'
AND Table_1.Port NOT LIKE Table_2.Line_card || '-%-%'

It appears that the LIKE_ESCAPE() part is a bespoke function that escapes wildcard characters. Given your sample data that doesn't seem necessary here.

db<>fiddle

Alex Poole
  • 183,384
  • 11
  • 179
  • 318