-3

We have a table like this: 

number branch code
1000 center XXCO
1001 xyz XXER
1002 zyx XXRE
2000 center2 YYCO
2001 xyz YYER
2002 zyx YYRE

All the items that have "CO" in the end of their code are parents to the items that have the same first two symbols in the code.

The task is to get a list of all the branches with their respective parents, like this:

center xyz XXER
center zyx XXRE
center2 xyz YYER
center2 zyx YYRE

I try the following code:

SELECT b.branch, a.branch, a.code
FROM tmp a JOIN tmp b ON
left(a.code,2) = left(b.code,2)
AND a.code NOT LIKE '%CO' AND b.code LIKE '%CO';

But I get an error pointing at the "= left" position in the script.

ORA-00904: "LEFT": недопустимый идентификатор
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 3 Column: 17

What can be the reason for this? Can anybody please help?

user1688682
  • 41
  • 1
  • 2
  • 3
  • possible duplicate of [LEFT function in Oracle](http://stackoverflow.com/questions/11309247/left-function-in-oracle) – APC Sep 21 '12 at 12:31
  • LEFT is not a function in Oracle. It may be a user defined function, though that is unlikely – Ghost Sep 21 '12 at 19:51

2 Answers2

6

Oracle doesn't have a LEFT function - use SUBSTR(a.code, 1, 2) instead (assuming you want to compare the first two characters).

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
2

There is no LEFT function in Oracle. Use SUBSTR(code,1,2) instead of LEFT(code,2)

GTG
  • 4,914
  • 2
  • 23
  • 27
  • Why is LEFT listed in the Oracle documentation: http://docs.oracle.com/cd/E17952_01/refman-5.1-en/string-functions.html#function_left – stephenbayer Oct 20 '14 at 17:01
  • @stephenbayer The documentation you are referring to is for MySQL, a different database engine than the classic Oracle database. Oracle Corporation now owns MySQL as well, but it's a different product. – GTG Oct 21 '14 at 10:46
  • Ah, thank you. I've been using that documentation wondering why the examples weren't working. – stephenbayer Oct 21 '14 at 14:07