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  |
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  |
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 |
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 |
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 |
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 |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:16:31 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
