Home » Developer & Programmer » Precompilers, OCI & OCCI » Problem with one query in pro*c
Problem with one query in pro*c [message #94292] Thu, 01 July 2004 02:46 Go to next message
Hector
Messages: 14
Registered: July 2000
Junior Member
Hello all,

I have a problem with one query in a program of pro*c, this query in the Toad is right, but when i insert in the pro*c program and i compile, appear many errors.

Can you help me, please?

Now, i explain all about this problem, machine, code, etc.

I have this machine

HP-UX

With this product installed:

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

The compilator is this

Pro*C/C++: Release 8.1.7.0.0

And then, when i make this query in the program

EXEC SQL DECLARE cursor CURSOR FOR

SELECT  ne.nr_asset_num cajero,ct.c_last_name entidad,si.si_site delegacion,
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS'))
-TO_DATE(ca.event_token,'DD/MM/YYYYHH24:MI:SS'))))*100/
(TO_NUMBER(TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')-TO_DATE(:sFecha1||'000000','YYYYMMDDHH24MISS'))+1),2) disp_real
   FROM call_req ca,net_res ne,ctct ct,site si
 WHERE
   ca.affected_rc=ne.id AND
   ne.nr_prim_c_id=ct.id AND
   ne.zdelegacion=si.id AND
   ca.status IN ('PC','FAC','CL') AND
   TO_CHAR(TO_DATE(ca.event_token,'DD/MM/YYYYHH24:MI:SS'),'YYYYMMDDHH24MISS')>= :sFecha1||'000000' AND
   TO_CHAR(TO_DATE(ca.event_token,'DD/MM/YYYYHH24:MI:SS'),'YYYYMMDDHH24MISS')<= :sFecha2||'235959' AND
   TO_DATE(ca.event_token,'DD/MM/YYYYHH24:MI:SS')<=TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') AND
   ca.event_token IS NOT NULL AND
   ne.nr_asset_num LIKE :sCaje AND
   ct.c_last_name LIKE :sEnti AND
   si_site LIKE :sDele
 GROUP BY  ne.nr_asset_num,ct.c_last_name,si.si_site;
EXEC SQL OPEN cursor;

When i compile, appear this messages:

Syntax error at line 319, column 37, file informe_disponibilidad.pc:
Error at line 319, column 37 in file informe_disponibilidad.pc
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_
DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH2
4MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),
0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60
*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','Y
YYYMMDDHH24MISS'))
....................................1
PCC-S-02201, Encountered the symbol "COUNT" when expecting one of the following:

   ( ) * + - / . @ | at, day, hour, minute, month, second, year,
The symbol "(" was substituted for "COUNT" to continue.

Syntax error at line 319, column 46, file informe_disponibilidad.pc:
Error at line 319, column 46 in file informe_disponibilidad.pc
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_
DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH2
4MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),
0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60
*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','Y
YYYMMDDHH24MISS'))
.............................................1
PCC-S-02201, Encountered the symbol "FROM" when expecting one of the following:

   , ) * + - / | at, day, hour, minute, month, second, year,

Syntax error at line 319, column 240, file informe_disponibilidad.pc:
Error at line 319, column 240 in file informe_disponibilidad.pc
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_
DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH2
4MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),
0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60
*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','Y
YYYMMDDHH24MISS'))
................................................................................
................................................................................
...............................................................................1
PCC-S-02201, Encountered the symbol "," when expecting one of the following:

   ) union, intersect, minus, order,

Syntax error at line 319, column 380, file informe_disponibilidad.pc:
Error at line 319, column 380 in file informe_disponibilidad.pc
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_
DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH2
4MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),
0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60
*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','Y
YYYMMDDHH24MISS'))
................................................................................
................................................................................
................................................................................
................................................................................
...........................................................1
PCC-S-02201, Encountered the symbol ":" when expecting one of the following:

   ( ) * & + - ~ ! ^ ++ -- ... char, const, double, enum, float,
   int, long, ulong_varchar, OCIBFileLocator OCIBlobLocator,
   OCIClobLocator, OCIDateTime, OCIExtProcContext, OCIInterval,
   OCIRowid, OCIDate, OCINumber, OCIRaw, OCIString, short,
   signed, sizeof, sql_context, sql_cursor, struct, union,
   unsigned, utext, uvarchar, varchar, void, volatile,
   an identifier, a typedef name, a quoted string,
   a numeric constant,

Syntax error at line 319, column 399, file informe_disponibilidad.pc:
Error at line 319, column 399 in file informe_disponibilidad.pc
ROUND((SUM(TO_NUMBER(DECODE((SELECT COUNT(*) FROM DUAL WHERE TO_DATE(TO_CHAR(TO_
DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60*60*24)),'YYYYMMDDHH2
4MISS'),'YYYYMMDDHH24MISS') - TO_DATE(:sFecha2||'235959','YYYYMMDDHH24MISS')>0),
0,TO_DATE(TO_CHAR(TO_DATE('19700101010000', 'YYYYMMDDHH24MISS')+ (ca.zsalida/(60
*60*24)),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),1,TO_DATE(:sFecha2||'235959','Y
YYYMMDDHH24MISS'))
................................................................................
................................................................................
................................................................................
................................................................................
..............................................................................1
PCC-S-02201, Encountered the symbol "'YYYYMMDDHH24MISS'" when expecting one of t
he following:

   : ( * an identifier,
Re: Problem with one query in pro*c [message #94298 is a reply to message #94292] Sun, 11 July 2004 08:16 Go to previous messageGo to next message
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
Senior Member
Agreed that oracle precompilers do not give very informative and clearcut error messages like C/C++ programs. But in case of syntax errors like this,It clearlt says like--> expecting "this" and found "that". it is sure that you have missed some symbol either in the statement or just before the statement. Can you please check that..
Or you can paste your program in which you are embedding this?
Re: Problem with one query in pro*c [message #94299 is a reply to message #94298] Sun, 11 July 2004 22:10 Go to previous messageGo to next message
Hector
Messages: 14
Registered: July 2000
Junior Member
Hello, thank for your answered but first i maked the query in TOAD and after i paste in my program pro*c and appear this message, do you know anything about this problem, because in the TOAD don't appear this message . . .

Thank you very much.
Re: Problem with one query in pro*c [message #94301 is a reply to message #94299] Mon, 12 July 2004 18:32 Go to previous messageGo to next message
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
Senior Member
You have declared cursor as
EXEC SQL DECLARE cursor CURSOR FOR

as

EXEC SQL DECLARE some_other_name CURSOR FOR

Seems that oracle is getting confused for you have taken the keyword as a variable name.
Does this solve you problem?
Re: Problem with one query in pro*c [message #94302 is a reply to message #94301] Mon, 12 July 2004 22:24 Go to previous messageGo to next message
Hector
Messages: 14
Registered: July 2000
Junior Member
Hello again.

I changed the name of the cursor but appear the same mistake.

I don't know, but i think the compiler of oracle don't understand the subquery-->
select count(*) from ...

I don't understand this.

Thank you very much.
Re: Problem with one query in pro*c [message #94303 is a reply to message #94292] Tue, 13 July 2004 02:04 Go to previous messageGo to next message
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
Senior Member
I simulated the same problem in my Pro*C code. I was getting exactly same error. You are correct that the query is failing because of select count(*)statement/expression as the first parameter. I am not sure how to make decode state work in the pro*C program.

PL/SQL works fine. So you can embed your Cursor portion in a PL/SQL stored procedure and call the same in your Pro*C Program.

SQL> edit
Wrote file afiedt.buf

1 declare
2 vnum NUMBER;
3 begin
4 select DECODE((SELECT count(*) FROM all_tables),1,8888,9999) into vnum from dual;
5 DBMS_OUTPUT.PUT_LINE('ans is '||vnum);
6* end;
SQL> /
ans is 9999

PL/SQL procedure successfully completed.

If anyone else can throw some light on this and make it work diredtly in the pro*C code, then it will be worth learning.
Re: Problem with one query in pro*c [message #94304 is a reply to message #94303] Tue, 13 July 2004 04:34 Go to previous message
Hector
Messages: 14
Registered: July 2000
Junior Member
Thank you very much.

I make this procedure.

Thanks again.

bye
Previous Topic: download pro * c+
Next Topic: Error in Pro*COBOL program
Goto Forum:
  


Current Time: Mon Aug 10 12:45:48 CDT 2020