ORA-00923 FROM keyword not found where expected

Question

I am trying to concatenate some fields to return a single string for each row from an oracle table. This is in 10g. Here is my query:

SELECT t.value || '|' || t.label || '|' t.label_abbrv || '||' "mylist" 
  FROM list_value t
 WHERE t.value BETWEEN 195001 AND 195300;

I'm getting the "FROM keyword not found where expected" error. This is really annoying. It's a simple query. I'm sure it's something simple I'm missing.

Theresa · Accepted Answer · 2015-05-01T14:31:47.663

2

D'oh! I found the problem. I'm missing a concat!

SELECT value || '|' || label || '|' ****||**** label_abbrv || '||' "mylist"
from list_value where (value between 195001 and 195300);

edited May 01 '15 at 14:31

answered Sep 17 '09 at 13:52

Theresa

3,515
10
42
47

The "mylist" might be a problem too... but I suspect you didn't mean it literally. If you replaced it with 'bananas|apples|sofas', then no problems. – Kieveli Sep 17 '09 at 14:29
I suppose "mylist" is a column alias ;) – Juergen Hartelt Sep 17 '09 at 15:58
@jhartelt: yes, "mylist" is just the column header; I fixed where the missing pipes were...I couldn't copy/paste as the query is on a different network/computer – Theresa Sep 17 '09 at 17:15

score 2 · Answer 2 · answered Sep 17 '09 at 14:04

If you used SQLPLUS client, it would have saved you a little time:

SQL> SELECT value || '|' || label || '|' label_abbrv || '||' "mylist"
  2  from list_value where (value between 195001 and 195300);
SELECT value || '|' || label || '|' label_abbrv || '||' "mylist"
                                                *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

You can break up your query to multiple lines to isolate the problem:

SQL> edit
Wrote file afiedt.buf

  1  SELECT value || '|'
  2  || label ||
  3  '|' label_abbrv ||
  4  '||' "mylist"
  5  from list_value
  6  where
  7* (value between 195001 and 195300)
SQL> /
'|' label_abbrv ||
                *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

You might find SQLPLUS to be "primitive," but, hmmm, that's good for another question. Let me see if anyone else has asked about it yet.

SQLPlus may be primitive but its the only tool that always works and never lies. (for the record I spend most my day in TOAD). One up vote for the great answer. — berlebutch, Sep 17 '09 at 15:53
@ericp: great suggestion! I'll have to try breaking up the query into multiple lines the next time it won't run. — Theresa, Sep 17 '09 at 17:09

score 1 · Answer 3 · answered Sep 17 '09 at 14:01

1

I think your answer to your own question is still wrong - it should be:

SELECT value || '|' || label || '|' || label_abbrv || '||' "mylist" 
                                   ^^^^

answered Sep 17 '09 at 14:01

Tony Andrews

129,880
21
220
259

ORA-00923 FROM keyword not found where expected

3 Answers3