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.

Answer 1

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);

Answer 2

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

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

Answer 3

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.