none
ORA-00907: missing right parenthesis

    Question

  • hi,

    i'm trying to build an oracle query using an expression in SSIS, but i'm getting the error message in the title of this question. it's a little big and i have looked for a missing parenthesis without success. it runs OK in SQL Developer.

    "SELECT do.empnom "Origem", cp.cpsnom "Campus", u.unides "Unidade", r.regdes "Regional", c.curdes "Curso", cc.ccpcod "Cod Curso Campus", cc.ccpdes "Desc Curso Campus", cc.ceccod "Centro Custo", t.trnred "Turno", mo.moddes "Modalidade", tp.tpcdes "Tipo Curso", tf.tfodes "Tipo Formação", pl.peldes "Periodo Letivo", de.espcod "Cod Esp", de.espdesc "Desc Especialidade", m.mtrra "RA", m.mtrtipaluno "Tipo Aluno", m.mtrsitdes "Sit Matrícula", m.mtrdatsit "Data Sit Matr", m.mtrdatcur "Dt Status Cursando", m.mtrtipingresso "Tipo Ingresso", m.mtrturma "Turma", trim(lpad(m.mtrserie,2,'0')) "Semestre", trunc((sysdate - pf.psfdatnas)/365) "Idade", (case when trunc((sysdate - pf.psfdatnas)/365) <= 18 then 'De 0 a 18 anos' when trunc((sysdate - pf.psfdatnas)/365) between 19 and 25 then 'De 19 a 25 anos' when trunc((sysdate - pf.psfdatnas)/365) between 26 and 35 then 'De 26 a 35 anos' when trunc((sysdate - pf.psfdatnas)/365) between 36 and 45 then 'De 36 a 45 anos' when trunc((sysdate - pf.psfdatnas)/365) > 45 then 'Maior que 45 anos' end) "Faixa Etária", decode(pf.psfsexo,'M','Masculino','Feminino') "Sexo", m.mtrinstaca "Instância Acadêmica", m.mtrinstfin "Instância Financeira", m.mtrindrem "Ind Rematrícula", m.mtrindevadido "Ind Evadido", m.mtrpago "[Re]matrícula Paga" FROM bi.dm_matricula m, bi.dm_pss_fisica pf, bi.dm_curso_campus cc, bi.dm_curso c, bi.dm_campus cp, bi.dm_modalidade mo, bi.dm_turno t, bi.dm_tipo_curso tp, bi.dm_tipo_formacao tf, bi.dm_periodo_letivo pl, bi.dm_origem do, bi.dm_especial de, bi.dm_unidade u, bi.dm_regional r WHERE m.data_base = to_date(sysdate,'dd/mm/yyyy') and m.pelcod = 20111 and m.psfcod = pf.psfcod and M.DMECOD = de.dmecod and m.pelcod = pl.pelcod and de.ccpcod = cc.ccpcod and cc.curcod = c.curcod and cc.cpscod = cp.cpscod and cc.modcod = mo.modcod and cc.trncod = t.trncod and cc.tpccod = tp.tpccod and cc.tfocod = tf.tfocod and cp.empcod = do.empcod and cp.unicod = u.unicod and u.regcod = r.regcod"
    

    any help will be welcome.

    thanks,

    levogiro.


    Benedito R. Almeida Filho Business Intelligence
    Tuesday, May 03, 2011 2:11 PM

Answers

  • I think I've found the problem. There was a Package Configuration and when I deleted it from the package, it worked fine.

    Now I'm going to do some tests to find out if it was in fact that Package Configuration file.

    Thanks for your help.

    Levogiro


    Benedito R. Almeida Filho Business Intelligence
    Wednesday, May 04, 2011 11:19 AM

All replies

  • The problem is the double-quotes embedded in your expression.  SSIS is reading those as delimiters of the string literals.  The error message you're getting isn't what I'd expect from that - I'd expect it to report that it doesn't know what to do with Origem.

    Anyway, what you have to do is escape all of the embedded double quotes in the expression.  Here's a start:

    "SELECT do.empnom \"Origem\", cp.cpsnom \"Campus\", u.unides \"Unidade\",


    Todd McDermid's Blog Talk to me now on
    Tuesday, May 03, 2011 4:55 PM
    Moderator
  • Hi, Todd,

    I eliminate the use of double-quotes replacing the spaces between words with "_" character, but I'm still getting the same error message.

    If I use a literal ("SELECT TO_DATE('27/04/2011','dd/mm/yyyy')") it works fine. When I try to replace the literal with a user variable, like ("SELECT TO_DATE(" + @[User::DataBase] + ",'dd/mm/yyyy')") then I get this error message.

    Thanks,

    Levogiro.


    Benedito R. Almeida Filho Business Intelligence
    Tuesday, May 03, 2011 5:35 PM
  • You get an error message about parentheses?  That doesn't sound right.

    What does the SSIS variable DataBase have in it?


    Todd McDermid's Blog Talk to me now on
    Tuesday, May 03, 2011 5:48 PM
    Moderator
  • This variable has '02/05/2011' including the quotes. I tried to put them in the expression, but I got the same error message. Here in Brazil we use 'dd/mm/yyy' date format.
    Benedito R. Almeida Filho Business Intelligence
    Tuesday, May 03, 2011 6:09 PM
  • Hit the "evaluate" button in the expression editor, and copy and paste that into your Oracle equivalent of SSMS.  Does it run?
    Todd McDermid's Blog Talk to me now on
    Tuesday, May 03, 2011 6:45 PM
    Moderator
  • Yes, I tested on SQL Developer and there was no problem.
    Benedito R. Almeida Filho Business Intelligence
    Tuesday, May 03, 2011 7:13 PM
  • Hi, Todd,

    It's me again. I put the single quote in the expression field SELECT TO_DATE('" + @[User::DataBase] + "','dd/mm/yyyy')) and now I'm getting a new error message. I removed the single quote from variable initial value. This is the error message: Description: "ORA-01830: date format picture ends before converting entire input string".


    Benedito R. Almeida Filho Business Intelligence
    Tuesday, May 03, 2011 7:35 PM
  • The only reason you would get different results is if you're not clear on what the expression is actually evaluating to.  If you try your two different expressions - one with the single quotes inside the variable, and one with them in the expression instead - do they evaluate to the same string?
    Todd McDermid's Blog Talk to me now on
    Tuesday, May 03, 2011 9:36 PM
    Moderator
  • Hi,

    After looking at your first original SQL, may I suggest replacing the following: 

    WHERE m.data_base = to_date(sysdate,'dd/mm/yyyy')
    

    with

    WHERE to_char(m.data_base,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
    

    ?

    It might well be a issue of different NLS_DATE_FORMAT?

     

    Hope this helps.

    J.

    Wednesday, May 04, 2011 1:15 AM
  • Good morning, Jonathan,

    I tried your suggestion, but I got a new error message: Description: "ORA-00933: SQL command not properly ended". This is the command evaluated:

    SELECT do.empnom Origem, cp.cpsnom Campus, u.unides Unidade, r.regdes Regional, c.curdes Curso, cc.ccpcod Cod_Curso_Campus, cc.ccpdes Desc_Curso_Campus, cc.ceccod Centro_Custo, t.trnred Turno, mo.moddes Modalidade, tp.tpcdes Tipo_Curso, tf.tfodes Tipo_Formacao, pl.peldes Periodo_Letivo, de.espcod Cod_Esp, de.espdesc Desc_Especialidade,  m.mtrra RA, m.mtrtipaluno Tipo_Aluno, m.mtrsitdes Sit_Matricula, m.mtrdatsit Data_Sit_Matr, m.mtrdatcur Dt_Status_Cursando, m.mtrtipingresso Tipo_Ingresso, m.mtrturma Turma, trim(lpad(m.mtrserie,2,'0')) Semestre, trunc((sysdate - pf.psfdatnas)/365) Idade, (case when trunc((sysdate - pf.psfdatnas)/365) <= 18 then 'De 0 a 18 anos' when trunc((sysdate - pf.psfdatnas)/365) between 19 and 25 then 'De 19 a 25 anos' when trunc((sysdate - pf.psfdatnas)/365) between 26 and 35 then 'De 26 a 35 anos' when trunc((sysdate - pf.psfdatnas)/365) between 36 and 45 then 'De 36 a 45 anos' when trunc((sysdate - pf.psfdatnas)/365) > 45 then 'Maior que 45 anos' end)  Faixa_Etaria, decode(pf.psfsexo,'M','Masculino','Feminino') Sexo, m.mtrinstaca Instancia_Academica, m.mtrinstfin Instancia_Financeira, m.mtrindrem Ind_Rematricula, m.mtrindevadido Ind_Evadido, m.mtrpago Re_Matricula_Paga FROM bi.dm_matricula m, bi.dm_pss_fisica pf, bi.dm_curso_campus cc, bi.dm_curso c, bi.dm_campus cp, bi.dm_modalidade mo, bi.dm_turno t, bi.dm_tipo_curso tp, bi.dm_tipo_formacao tf, bi.dm_periodo_letivo pl, bi.dm_origem do, bi.dm_especial de, bi.dm_unidade u, bi.dm_regional r WHERE to_char(m.data_base,'dd/mm/yyyy') = '27/04/2011' and m.pelcod = 20111 and m.psfcod = pf.psfcod and M.DMECOD  = de.dmecod and m.pelcod = pl.pelcod and de.ccpcod = cc.ccpcod and cc.curcod = c.curcod and cc.cpscod = cp.cpscod and cc.modcod = mo.modcod and cc.trncod = t.trncod and cc.tpccod = tp.tpccod and cc.tfocod = tf.tfocod and cp.empcod = do.empcod and cp.unicod = u.unicod and u.regcod = r.regcod

    The first parameter is a string. Maybe it's the OLE DB Driver I'm using. I'll check here at work.

    Thanks,

    Levogiro.

     


    Benedito R. Almeida Filho Business Intelligence
    Wednesday, May 04, 2011 11:01 AM
  • I think I've found the problem. There was a Package Configuration and when I deleted it from the package, it worked fine.

    Now I'm going to do some tests to find out if it was in fact that Package Configuration file.

    Thanks for your help.

    Levogiro


    Benedito R. Almeida Filho Business Intelligence
    Wednesday, May 04, 2011 11:19 AM