Help with "ORA-00907: missing right parenthesis" error please

Question

I am getting a ORA-00907: missing right parenthesis from the following code below

 dbQuery:= TQuery.Create(nil);
 dbQuery.DatabaseName:= dbMain.DatabaseName;
 with dbQuery do
 begin
   SQL.Add('select payee_address_zip, EXTRACT(WEEKDAY FROM check_date) as DOW, ');
   SQL.Add('(cmcl_bank_cleared - check_date) as DateDiff from AP_Master ');
   SQL.Add('where (cmcl_bank_cleared is not null) AND ((cmcl_bank_cleared - check_date) >=:DaysParam)');
   SQL.Add('order by payee_address_zip, DOW, DateDiff');
  try
   ParamByName('DaysParam').AsInteger:= days_param_int;
   Open;
   //do something else here      
  except on E:EDatabaseError do
   begin
    raise ECustomException.create('Error opening query for step 1 of computing Float Factors!');
   end;//except
  end; //try
 end; //with
 dbQuery.Free;

Can someone tell me what is going on? I can't seem to see that i have left out a parenthesis, and this SQL works just fine against a Interbase test database. However, when moving it to a clients oracle database, it crashes, with the error above.

In such a situation it always helps me to print out the generated SQL and run it outside of the program. — , Apr 27 '11 at 12:41
This question is still open....if someone wants to give me a DELPHI answer to this question please....thankx — IElite, Apr 27 '11 at 14:20
There are no Delphi answers, that is a Oracle error... You get the same error even if you type: select extract(weekday from sysdate) from dual; it's that syntax that triggers the Oracle error. You have to rewrite your query to be an Oracle one, not an Interbase one. — , Apr 27 '11 at 14:29

score 10 · Accepted Answer · answered Apr 27 '11 at 13:10

10

Let's check with SQL*Plus, so you can see exactly at which place the parser expects a right parenthesis:

SQL> create table ap_master(payee_address_zip,check_date,cmcl_bank_cleared)
  2  as
  3  select 1,sysdate,sysdate+1 from dual
  4  /

Table created.

SQL> var DaysParam number
SQL> exec :DaysParam := 1

PL/SQL procedure successfully completed.

SQL> select payee_address_zip
  2       , EXTRACT(WEEKDAY FROM check_date) as DOW
  3       , (cmcl_bank_cleared - check_date) as DateDiff
  4    from AP_Master
  5   where (cmcl_bank_cleared is not null)
  6     AND ((cmcl_bank_cleared - check_date) >=:DaysParam)order by payee_address_zip, DOW, DateDiff
  7  /
     , EXTRACT(WEEKDAY FROM check_date) as DOW
                       *
ERROR at line 2:
ORA-00907: missing right parenthesis

So there is something with your EXTRACT function. This is the relevant piece of documentation about the EXTRACT (datetime) function:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions059.htm#sthref1117

It doesn't mention your WEEKDAY keyword. You can get the weekday by using the TO_CHAR function though. I don't know which date format element you want exactly. I guess 'D' or 'DAY'. You can look them up here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#CDEHIFJA

If you change the EXTRACT expression and replace it with a TO_CHAR, it will work:

SQL> select payee_address_zip
  2       , to_char(check_date,'d') as DOW
  3       , (cmcl_bank_cleared - check_date) as DateDiff
  4    from AP_Master
  5   where (cmcl_bank_cleared is not null)
  6     AND ((cmcl_bank_cleared - check_date) >=:DaysParam)order by payee_address_zip, DOW, DateDiff
  7  /

PAYEE_ADDRESS_ZIP D   DATEDIFF
----------------- - ----------
                1 4          1

1 row selected.

Regards,
Rob.

answered Apr 27 '11 at 13:10

Rob van Wijk

17,555
5
39
55

I get the same error as i stated in my original post. Even with the change of the function from Extract to to_char. – IElite Apr 27 '11 at 14:00
SQL.Add('select payee_address_zip, to_char(check_date, ' + 'd' + ') as DOW, '); – IElite Apr 27 '11 at 14:00
Because you are again missing a right parenthesis, i.e. you didn't quote your date format element. I'm not sure about delphi, but you probably need to use SQL.Add('select payee_address_zip, to_char(check_date, ''d'') as DOW, '), where you see two single quotes, and not a single double quote. Of course :-) – Rob van Wijk Apr 27 '11 at 14:07
i did a concatenation of strings - string + string + string – IElite Apr 27 '11 at 14:12
You need to quote your date format element, so the query becomes "... to_char(check_date,'d') ..." and not "... to_char(check_date,d) ...". Although that doesn't raise a right parenthesis error, but a ORA-00904: "D": invalid identifier. Anyway, use the same technique as I showed you. – Rob van Wijk Apr 27 '11 at 14:16
i did quote it. I tried it two ways. I did a string + string + string concatentation, and i did it witht he double single quotes. neither of which worked. Surprisingly, no matter what i do, i always get the ORA-00907 error! So, i guess, this solution does not solve it – IElite Apr 27 '11 at 14:19
Well, I proved to you that my query gets parsed successfully. You only need to find out where your query string differs from mine. – Rob van Wijk Apr 27 '11 at 14:24
This might help: http://stackoverflow.com/questions/587772/using-in-strings-in-delphi – Rob van Wijk Apr 27 '11 at 14:37
Well, it doesn't work, not even with the quotedStr used. So, im still waiting on someone to post a answer using delphi code, not what Oracle parsed for you. Thanks anyways. – IElite Apr 27 '11 at 16:33
SQL.Add('select payee_address_zip, to_char(check_date,' + quotedStr('d') + ') as DOW, '); – IElite Apr 27 '11 at 18:04
SQL.Add('select payee_address_zip, to_char(check_date,''d'' as DOW, '); – IElite Apr 27 '11 at 18:05
SQL.Add('select payee_address_zip, to_char(check_date,'d' as DOW, '); //this one wont compile of course – IElite Apr 27 '11 at 18:05
Well, i finally found the right way to enclose the 'd', and now i have a new error: ORA-00933 SQL command not properly ended – IElite Apr 27 '11 at 18:23

score 0 · Answer 2 · answered Apr 27 '11 at 12:58

0

It looks like you're missing a space on the end of this line:

SQL.Add('where (cmcl_bank_cleared is not null) AND ((cmcl_bank_cleared - check_date) >=:DaysParam)');

answered Apr 27 '11 at 12:58

bhamby

15,112
1
45
66

That's not it. SQL.Add() adds the new line of text (with a line terminator, typically a CR), so the spaces at the end are irrelevant. – Ken White Apr 27 '11 at 13:22
Ah, I was unaware of SQL.Add()'s functionality, so I was just guessing (I suppose I should have mentioned that). – bhamby Apr 27 '11 at 13:38
In Delphi, `TQuery.SQL` is a `TStringList`, so it has all of the functionality of that class. – Ken White Apr 27 '11 at 13:46
you say im missing a space , but you dont point out where? At the end? Not sur what you mean by that – IElite Apr 27 '11 at 14:01
`It looks like you're missing a space on the *end of this line*:` I did point it out, but according to Ken, it doesn't matter, anyway, since `SQL.Add()` automatically inserts a line terminator! – bhamby Apr 27 '11 at 16:48

Help with "ORA-00907: missing right parenthesis" error please

2 Answers2