Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936: missing expression (SQL*Plus)
ORA-00936: missing expression [message #329847] Thu, 26 June 2008 13:44 Go to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

Hi there

I am a bit of a novice at creating scripts, but I am creating a spool script that keeps on giving me the error:
"ORA-00936: missing expression"

The script looks like this:
"
spool H:\file.csv
select cfo.tipo || ',' ||
cfo.descricao || ',' ||
--etc...
from table.cfo
where cfo.dt_efetiva = TO_DATE('25/03/2008','DD/MM/YYYY')
and ( (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15,112,14,115,60,111,116,90,113))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(100))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(115))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(80))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(70))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(113)) )
order by cfo.dt_efetiva,
cfo.num_empresa,
spool off
"

I'm not sure what is wrong with it, but it indicates the error at the 2nd part of the where clause "SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1)"

Any ideas on what could be the problem, and also, is there a better way of incorporating multiple where clauses with multiple AND and OR's?
Re: ORA-00936: missing expression [message #329849 is a reply to message #329847] Thu, 26 June 2008 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>from table.cfo
I doubt this is valid

[Updated on: Thu, 26 June 2008 13:57] by Moderator

Report message to a moderator

Re: ORA-00936: missing expression [message #329851 is a reply to message #329849] Thu, 26 June 2008 14:08 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

sorry, changed the actual table name in my example to "table", which is a reserved word, oops...

what the script actually looks like:

spool H:\Jaco\Cashfiles\SDOPEN\testsdopen.csv
	select     		     cfo.tipo || ',' ||
                                     cfo.descricao || ',' ||
                                     cfo.id_oper || ',' ||
                                     cfo.id_evento_oper || ',' ||
                                     cfo.descricao_evento || ',' ||
from       vop_lanc_contabil_evento cfo                                          
where cfo.vlr_lancamento <> 0  
and (	(SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15,112,14,115,60,111,116,90,113))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(100))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(115))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(80))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(70))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(113))	)
order by cfo.dt_efetiva, 
	 cfo.num_empresa, 
	 cfo.cod_tit, 
	 cfo.id_agencia, 
	 cfo.descricao, 
	 cfo.descricao_evento, 
	 cfo.id_oper,
         cfo.ind_rateio_contabil, 
         cfo.num_tipo_lanc_contabil;
spool off
Re: ORA-00936: missing expression [message #329852 is a reply to message #329849] Thu, 26 June 2008 14:10 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

The response from oracle was:

SQL> @test_open_cashfile_XXX.sql
DOC>*                                     TEST OPEN CASH FILE - testsdopen.csv                         *
DOC>************************************************************************************************
and (   (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15
                                                *
ERROR at line 34:
ORA-00936: missing expression
Re: ORA-00936: missing expression [message #329853 is a reply to message #329847] Thu, 26 June 2008 14:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>SQL> @test_open_cashfile_XXX.sql
prior to doing above, please do
SQL> set term on echo on

>cfo.descricao_evento || ',' ||
I suspect the trailing "||" has confused sqlplus
Re: ORA-00936: missing expression [message #329854 is a reply to message #329853] Thu, 26 June 2008 14:23 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

I removed the trailing || ',' || but still receive the same error:

SQL> @test_open_cashfile_XXX.sql
SQL> set lines 999 pages 999 trimspool on arraysize 100 maxdata 32000 num 15
SQL> set heading off feedback off echo off
DOC>*                                     TEST OPEN CASH FILE - testsdopen.csv                         *
DOC>************************************************************************************************
and (   (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15
                                                *
ERROR at line 31:
ORA-00936: missing expression
Re: ORA-00936: missing expression [message #329855 is a reply to message #329847] Thu, 26 June 2008 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>SQL> set heading off feedback off echo off
having SET ECHO OFF in the file overrides my request for visibility
SQL> SET TERM ON ECHO on

can't you simply CUT & PASTE the raw SELECT into sqlplus?

Something in the file is confusing sqlplus, IMO.
Re: ORA-00936: missing expression [message #329856 is a reply to message #329855] Thu, 26 June 2008 14:39 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

OK, did that, but it still results in the same error

SQL> set term on echo on
SQL> @test_open_cashfile_XXX.sql
SQL> set lines 999 pages 999 trimspool on arraysize 100 maxdata 32000 num 15
SQL> set heading off feedback off
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
SQL> 
SQL> /**********************************************************************************************
DOC>*                                     TEST OPEN CASH FILE - testsdopen.csv                         *
DOC>************************************************************************************************
SQL> --
SQL> -- *VERIFICAR SE O SYSDATE DO ORACLE ESTA EM UM DIA UTIL*
SQL> --
SQL> -- ATENCAO! O NOME DO ARQUIVO sdndf.csv DEVE ESTAR EM MINUSCULO
SQL> --
SQL> -- ===>>>***ATUALIZAR O DIRETORIO PARA O QUAL SERA GERADO O ARQUIVO
SQL> --
SQL> spool H:\Jaco\Cashfiles\SDOPEN\testsdopen.csv
SQL>         select                       cfo.tipo || ',' ||
  2                                       cfo.descricao || ',' ||
  3                                       cfo.id_oper || ',' ||
  4                                       cfo.id_evento_oper || ',' ||
  5                                       cfo.descricao_evento || ',' ||
  6                                       cfo.dt_entrada || ',' ||
  7                                       cfo.dt_efetiva || ',' ||
  8                                       cfo.nome_empresa || ',' ||
  9                                       cfo.nome_contraparte || ',' ||
 10                                       cfo.id_agencia || ',' ||
 11                                       cfo.cod_sis_custodia || ',' ||
 12                                       cfo.dt_ini || ',' ||
 13                                       cfo.dt_vcto || ',' ||
 14                                       cfo.vlr_operacao || ',' ||
 15                                       cfo.taxa || ',' ||
 16                                       cfo.id_tipo_indexador || ',' ||
 17                                       cfo.cod_tit || ',' ||
 18                                       cfo.num_tipo_lanc_contabil || ',' ||
 19                                       cfo.descricao_lanc_contabil || ',' ||
 20                                       cfo.id_roteiro_contabil || ',' ||
 21                                       cfo.descricao_roteiro || ',' ||
 22                                       cfo.num_conta_debito || ',' ||
 23                                       cfo.num_conta_credito || ',' ||
 24                                       cfo.vlr_lancamento || ',' ||
 25                                       cfo.num_empresa || ',' ||
 26                                       cfo.cod_isin
 27  from       vop_lanc_contabil_evento cfo
 28  where cfo.vlr_lancamento <> 0
 29  and cfo.num_empresa = 117568
 30  and cfo.dt_efetiva  = TO_DATE('25/03/2008','DD/MM/YYYY')
 31  and (      (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(
 32           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174) and SUBSTR(cfo.
 33           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591) and SUBSTR(cfo.id_roteiro_contabil,1,
 34           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,) and SUBSTR(cfo.id_roteiro_contabil,
 35           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,
 36           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,
 37           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3)
 38  order by cfo.dt_efetiva,
 39           cfo.num_empresa,
 40           cfo.cod_tit,
 41           cfo.id_agencia,
 42           cfo.descricao,
 43           cfo.descricao_evento,
 44           cfo.id_oper,
 45           cfo.ind_rateio_contabil,
 46           cfo.num_tipo_lanc_contabil;
and (   (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15
                                                *
ERROR at line 31:
ORA-00936: missing expression


SQL> spool off
SQL> 
Re: ORA-00936: missing expression [message #329857 is a reply to message #329856] Thu, 26 June 2008 15:10 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
jacovdh wrote on Thu, 26 June 2008 15:39
 31  and (      (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(
and (   (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15
                                                *
ERROR at line 31:
ORA-00936: missing expression



Please cut and paste in FULL. Line 31 is cut off at IN(, yet the error message implies there is more on the line. You probably have typos or unbalanced parenthesis, but if you insist on hiding them from us, this will go on and on.
Re: ORA-00936: missing expression [message #329859 is a reply to message #329847] Thu, 26 June 2008 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>32 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174) and SUBSTR(cfo.
This also is obviously malformed.

along with all the trailing commas

33 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591) and SUBSTR(cfo.id_roteiro_contabil,1,
34 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,) and SUBSTR(cfo.id_roteiro_contabil,
35 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,
36 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,

[Updated on: Thu, 26 June 2008 15:17] by Moderator

Report message to a moderator

Re: ORA-00936: missing expression [message #330069 is a reply to message #329859] Fri, 27 June 2008 09:00 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

Not sure why, but it was "cutting off" the ends of those rows. Not sure if that explains it clearly...

I have adjusted the script, so that everything in the script now shows in SQL*Plus when I run it.

Output as follows:
SQL> @test_open_cashfile_XXX.sql
SQL> set LIN 999 pages 999 trimspool on arraysize 100 maxdata 32000 num 15 WRAP on
SQL> set heading off feedback off
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
SQL> 
SQL> /**********************************************************************************************
DOC>*                                     TEST OPEN CASH FILE - testsdopen.csv                        
DOC>************************************************************************************************
SQL> --
SQL> -- *VERIFICAR SE O SYSDATE DO ORACLE ESTA EM UM DIA UTIL*
SQL> --
SQL> -- ATENCAO! O NOME DO ARQUIVO sdndf.csv DEVE ESTAR EM MINUSCULO
SQL> --
SQL> -- ===>>>***ATUALIZAR O DIRETORIO PARA O QUAL SERA GERADO O ARQUIVO
SQL> --
SQL> spool H:\Jaco\Cashfiles\SDOPEN\testsdopen.csv
SQL>         select                       cfo.tipo || ',' ||
  2                                       cfo.descricao || ',' ||
  3                                       cfo.id_oper || ',' ||
  4                                       cfo.id_evento_oper || ',' ||
  5                                       cfo.descricao_evento || ',' ||
  6                                       cfo.dt_entrada || ',' ||
  7                                       cfo.dt_efetiva || ',' ||
  8                                       cfo.nome_empresa || ',' ||
  9                                       cfo.nome_contraparte || ',' ||
 10                                       cfo.id_agencia || ',' ||
 11                                       cfo.cod_sis_custodia || ',' ||
 12                                       cfo.dt_ini || ',' ||
 13                                       cfo.dt_vcto || ',' ||
 14                                       cfo.vlr_operacao || ',' ||
 15                                       cfo.taxa || ',' ||
 16                                       cfo.id_tipo_indexador || ',' ||
 17                                       cfo.cod_tit || ',' ||
 18                                       cfo.num_tipo_lanc_contabil || ',' ||
 19                                       cfo.descricao_lanc_contabil || ',' ||
 20                                       cfo.id_roteiro_contabil || ',' ||
 21                                       cfo.descricao_roteiro || ',' ||
 22                                       cfo.num_conta_debito || ',' ||
 23                                       cfo.num_conta_credito || ',' ||
 24                                       cfo.vlr_lancamento || ',' ||
 25                                       cfo.num_empresa || ',' ||
 26                                       cfo.cod_isin
 27  from       vop_lanc_contabil_evento cfo
 28  where cfo.vlr_lancamento <> 0
 29  and cfo.num_empresa = 117568
 30  and cfo.dt_efetiva  = TO_DATE('25/03/2008','DD/MM/YYYY')
 31  and (      (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1)
 32  and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15,112,14,115,60,111,116,90,113))
 33           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174)
 34           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(100))
 35           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591)
 36           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20))
 37           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,)
 38           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(115))
 39           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21)
 40           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(80))
 41           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21)
 42           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(70))
 43           or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1)
 44           and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(113))       )
 45  order by cfo.dt_efetiva,
 46           cfo.num_empresa,
 47           cfo.cod_tit,
 48           cfo.id_agencia,
 49           cfo.descricao,
 50           cfo.descricao_evento,
 51           cfo.id_oper,
 52           cfo.ind_rateio_contabil,
 53           cfo.num_tipo_lanc_contabil;
and (   (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1)
                                                *
ERROR at line 31:
ORA-00936: missing expression


SQL> spool off
SQL> 
Re: ORA-00936: missing expression [message #330079 is a reply to message #330069] Fri, 27 June 2008 09:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

37 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,)


Just a piece of note. From next time if you don't mind could you also post the create table script along with it.

Regards

Raj
icon14.gif  Re: ORA-00936: missing expression [message #330081 is a reply to message #330079] Fri, 27 June 2008 09:46 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

Thanks Raj and everyone else for your help!

This just shows me again that I need to be more precise in checking ALL of the script when there is an error, and not just the part relating to the error message...

Smile
Re: ORA-00936: missing expression [message #330082 is a reply to message #329847] Fri, 27 June 2008 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
jacovdh,
As a quick & dirty exercise please use:
http://www.orafaq.com/utilities/sqlformatter.htm
& post CUT & PASTE results back here.
Re: ORA-00936: missing expression [message #330091 is a reply to message #330082] Fri, 27 June 2008 10:09 Go to previous messageGo to next message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

OK, tried that at http://www.orafaq.com/utilities/sqlformatter.htm

Input - incorrect script (including comma that is not supposed to be there:
set lines 999 pages 999 trimspool on arraysize 100 maxdata 32000 num 15
set heading off feedback off echo off
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

/***************************************************************************************************
*                                     TEST OPEN CASH FILE - testsdopen.csv                         *
***************************************************************************************************/
--
-- *VERIFICAR SE O SYSDATE DO ORACLE ESTA EM UM DIA UTIL*
--
-- ATENCAO! O NOME DO ARQUIVO sdndf.csv DEVE ESTAR EM MINUSCULO
--
-- ===>>>***ATUALIZAR O DIRETORIO PARA O QUAL SERA GERADO O ARQUIVO
--
spool H:\Jaco\Cashfiles\SDOPEN\testsdopen.csv
	select     		     cfo.tipo || ',' ||
                                     cfo.descricao || ',' ||
                                     cfo.id_oper || ',' ||
                                     cfo.id_evento_oper || ',' ||
                                     cfo.descricao_evento || ',' ||
                                     cfo.dt_entrada || ',' || 
                                     cfo.dt_efetiva || ',' ||
                                     cfo.nome_empresa || ',' ||
                                     cfo.nome_contraparte || ',' ||
                                     cfo.id_agencia || ',' ||
                                     cfo.cod_sis_custodia || ',' ||
                                     cfo.dt_ini || ',' ||
                                     cfo.dt_vcto || ',' ||
                                     cfo.vlr_operacao || ',' ||
                                     cfo.taxa || ',' ||
                                     cfo.id_tipo_indexador || ',' ||
                                     cfo.cod_tit || ',' ||
                                     cfo.num_tipo_lanc_contabil || ',' ||
                                     cfo.descricao_lanc_contabil || ',' ||
                                     cfo.id_roteiro_contabil || ',' ||
                                     cfo.descricao_roteiro || ',' ||
                                     cfo.num_conta_debito || ',' ||
                                     cfo.num_conta_credito || ',' ||
                                     cfo.vlr_lancamento || ',' ||
                                     cfo.num_empresa || ',' ||
                                     cfo.cod_isin
                                     -- cfo.dt_termo || ',' ||
                                     -- cfo.ind_rateio_contabil || ',' ||
                                     -- cfo.id_evento_contabil
from       vop_lanc_contabil_evento cfo                                          
where cfo.vlr_lancamento <> 0  
and cfo.num_empresa = 117568 
and cfo.dt_efetiva  = TO_DATE('25/03/2008','DD/MM/YYYY')
and (	(SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20,15,112,14,115,60,111,116,90,113))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(89,18,157,158,159,173,174) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(100))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(591) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(20))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(115))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(80))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(70))
	 or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(1) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(113))	)
order by cfo.dt_efetiva, 
	 cfo.num_empresa, 
	 cfo.cod_tit, 
	 cfo.id_agencia, 
	 cfo.descricao, 
	 cfo.descricao_evento, 
	 cfo.id_oper,
         cfo.ind_rateio_contabil, 
         cfo.num_tipo_lanc_contabil;
spool off


Output:
"Output SQL: There are errors while formatting sql,check here "

"Error Messages
)(52,54) expected token:( + - IDENTIFIER CASE CLOSE CURSOR FUNCTION PACKAGE TYPE BY INSERT IS LIKE NOT NULL REVOKE TRIGGER USER CAST"


Re: ORA-00936: missing expression [message #330156 is a reply to message #330091] Fri, 27 June 2008 12:37 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
SUBSTR returns a string.
in (1) is a number (as are the other "in" operations).
Do not compare a string to a number.
Re: ORA-00936: missing expression [message #330157 is a reply to message #330091] Fri, 27 June 2008 12:40 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
jacovdh wrote on Fri, 27 June 2008 11:09

or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(18,1,)


This is invalid.

Quote:

or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(80))
or (SUBSTR(cfo.num_tipo_lanc_contabil,1,3) IN(20,21) and SUBSTR(cfo.id_roteiro_contabil,1,3) IN(70))



These should be combined.

Previous Topic: append description
Next Topic: one-time-only Procedure
Goto Forum:
  


Current Time: Sat Dec 10 20:47:17 CST 2016

Total time taken to generate the page: 0.20021 seconds