Reports & Discoverer :: Case Statement Inside Oracle Query - Missing Right Parenthesis
Jun 13, 2013
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
View 6 Replies
ADVERTISEMENT
Sep 10, 2003
My query
SELECT node_nme, os_nme, os_vrsn_nme, CPU_QTY, mch_id_nme
FROM node_t
WHERE node_nme IN
(
SELECT DISTINCT upper(node_nme) as node_nme FROM node_t WHERE row_status_cde IN (1,4)
[code]...
Now, when I run this i get the missing parenthesis error, though I don't konw why.The subselect in the IN works fine and returns exactly what it should.
View 2 Replies
View Related
Mar 13, 2007
I'm trying to create a Top / Bottom query from an example i already have but when i executed it, i got an error:
ORA-00907: missing right parenthesis
The coding i've got is:
SELECT customer_id, forename, surname, start_date_time, end_date_time, pos
FROM (
SELECT customer_id, forename, surname, start_date_time, end_date_time,
row_number() over (
partion by start_date_time
order by customer_id asc) pos
from customer, phone_call)
where pos = 1;
The error is on the partition line but i've closed all the open brackets,
View 2 Replies
View Related
Oct 14, 2010
Currently some jobs created in WBT scripting need to converted into oracle,plsql.There is one job in WBT scripting, which will invoke the oracle reports inside and generate the PDF files in the destination path as follows:
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?USERID=%LOGONINFO%+server=rep_pscm9722+destype=file+desname=D:ORACLE10G\%CCALLRptName%+desformat=PDF+PARAMFORM=no+report=PCCALL.RDF")
a = runhide("c:Program FilesInternet Exploreriexplore.exe", "http://pscm9722:7778/reports/rwservlet?
[code]...
Now, i want to convert this into oracle,plsql? Is it possible or not?
View 3 Replies
View Related
Feb 17, 2010
my update query seems to fail with error
ORA-00907: missing right parenthesis
Below is my test case:
create table t1(
sn varchar2(30),
inv_org number);
create table s1(
sn varchar2(30),
trx_date date,
inv_org number);
[code]...
View 8 Replies
View Related
Feb 12, 2013
I am running the following query but getting an error
Select
main.dietdate,
main.dietCombinationId,
main.diet_combination_name,
main.DTYPE,
Sum(main.TOTALVAL) as SCount,
[Code]....
ERROR at line Where Dem.diet_combination_id = DESS.diet_combination_id main , mas_diet_combination mmh ORA-00907: missing right parenthesis
finding where I am getting wrong
"WORST query formatting as the above query was written (by some developers) in MySQL and being migrated to oracle"
View 5 Replies
View Related
Oct 21, 2010
i'm trying to create table and im getting this error all the time.
CREATE TABLE Category (
Name CHAR(20) NOT NULL,
CategoryId INTEGER UNSIGNED NOT NULL,
RRO DOUBLE NULL,
LRO DOUBLE NULL,
IHRRO DOUBLE NULL,
PRIMARY KEY(Name, CategoryId)
);
View 2 Replies
View Related
Sep 21, 2010
I have been looking at this now for a good 2 hours. It seems like a simple error but I just cant see it. I must of counted the brackets 20 times. I have run this code in SQL developer using actual values and it works fine.
If your wondering about the esig class it just runs the sql with a few other SQL statements. I have changed the code below to a SELECT * statement just so see if it was actually that statement that was the problem. The select worked so it must be this.
Original
- oracle Code
$esig->setSQL("INSERT INTO document_ecopy
(DOCUMENT_ECOPY_ID
,DOCUMENT_UUID
[Code].....
View 2 Replies
View Related
Mar 28, 2010
I have following query. I checked manytime but I am unable to find the reason of error Ora-00907
SELECT Working_Days(sdate,edate),
Ddecode(Physical_files Between 1 And 500 And Working_Days(sdate,edate) Between 1 And 30,-1,
decode(Physical_files Between 501 And 3000 And Working_Days(sdate,edate) Between 1 And 180,-1,0)) col2
FROM table1 INNER JOIN table2
ON table1_Sno=table2_Sno
In this query, working_days is function that return working days.
View 5 Replies
View Related
Apr 2, 2008
Im running the following code and receiving 'error ORA-00907: missing right parenthesis' when I try and run the
to_char(((FAF_CALC_CCONTRIB (FA_ASSESSMENT.IDENTIFIER,'C'/7)*(TRUNC(TO_DATE(CPC_PROVISIONS.ACTUAL_END_DATE,'DD-MON-YY') - TO_DATE(CPC_PROVISIONS.ACTUAL_START_DATE,'DD-MON-YY')))),'FM9999990.00'),
If I remove this line then the SQL runs without problems. I think it's the first part FAF_CALC_CCONTRIB (FA_ASSESSMENT.IDENTIFIER,'C'thats causing the problems because I am using near identical code in another script which works perfectly.
View 2 Replies
View Related
May 1, 2013
CREATE TABLE titledds
( Title_Id NUMBER(4) PRIMARY KEY
TName VARCHAR2(20) NOT NULL UNIQUE
Price NUMBER(5)
Discount NUMBER(2)
TextCat VARCHAR2(5)
);
wen i enter this im getting an error @ TName VARCHAR2(20) NOT NULL UNIQUE
ORA-00907: MISSING RIGHT PARENTHESIS
im using 11g 2nd release..
View 3 Replies
View Related
May 22, 2011
CREATE TABLE LOCATION(
Locid NUMBER (5) LOCATION_LOCID_PRIMARY KEY,
Bldg_Code VARCHAR2 (10) NOT NULL,
Room VARCHAR2 (6) NOT NULL,
Capacity NUMBER (5));
CREATE TABLE FALCULTY(
[code]...
View 6 Replies
View Related
Sep 21, 2010
I am trying to execute the following query
SELECT inst_id, SID, serial#, module,username, machine, action, logon_time,status,event FROM gv$session
WHERE SID in
(
SELECT sid,id1,request FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
)
And I received the following error message
ORA-00907: missing right parenthesis
But when I execute
SELECT inst_id, SID, serial#, module,username, machine, action, logon_time,status,event FROM gv$session
and
SELECT sid,id1,request FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
separately. It works fine. Why is it erroring out when I include the query in WHERE clause. Rather than the work around, I would like to know the reason for the need of right paranthesis.
View 5 Replies
View Related
Aug 14, 2011
I am new to Pl/SQL, I have created the following statement but I am getting ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
SELECT P.PERSON_ID,
P.EMPLOYEE_NUMBER "Employee ID",
P.FIRST_NAME "FRSTNAME",
P.MIDDLE_NAMES "MIDLNAME",
P.LAST_NAME "LASTNAME",
[Code]....
View 3 Replies
View Related
Jun 20, 2013
I am using: Desktop / Discoverer 4.1 / Windows XP.
I am attempting to add a new calculated column and have had some success with the CASE function but need to add additional criteria.
What I have that works is:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)
What I need to add are a few additional criteria. I attempted and failed with a few variants of this:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND
Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)
The three criteria points that I am looking to includea are:
•Expenditure Type = 'Supplier Rebates'
•Capitalizable = 'Y'
•Task Owing Company = '534' OR '915'
View 8 Replies
View Related
Mar 10, 2012
I'm working on my database homework, and I came across an error that I cannot seem to fix. I'm getting a missing right parenthesis error on line 7, but I seem to have all my parenthesis, endless I'm overlooking one.
DROP TABLE RoomDM CASCADE CONSTRAINTS PURGE;
1 CREATE TABLE RoomDM (
2 rNo VARCHAR(4),
3 hNo NUMBER(3),
4 type CHAR(6) NOT NULL,
5 price NUMBER(3,2) NOT NULL,
6 CONSTRAINT RoomDM_PK PRIMARY KEY (rNo, hNo),
7 CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
8 REFERENCES HotelDM (hNo) ON DELETE CASCADE,
9 CONSTRAINT RoomDM_type_CK CHECK(type IN(single, Double, Family))
10 );
The error looks like this:
CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
* ERROR at line 7:
ORA-00907: missing right parenthesis
View 2 Replies
View Related
Jul 28, 2010
how could i rotate the text inside a matrix cell ??like
.......c....c....c
.......o....o....o
........l.....l.....l
.......#....#....#
.......1....2....3
----------------------
row#1
.......
row#2
.......
row#3
View 7 Replies
View Related
Apr 24, 2012
expdp test/test directory=dmpdir dumpfile=wip.dmp logfile=wip.log tables=wip_runcard query=wip_runcard:'"where org_id=51 and time<to_date ('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and ship_time is not null and wip_route=999 and used_status=5"'
but error:
ORA-31693: Table data object "TEST"."WIP_RUNCARD" failed to load/unload and is being skipped due to error:
ORA-00907: missing right parenthesis
how to use expdp query paramter with time?
View 6 Replies
View Related
Nov 14, 2013
im trying to insert these results to my fact table (fact_apartments) with the following:
INSERT INTO fact_apartments(avg_price, segmentid, projectid)
SELECT (avg(price), segmentid, projectid)
FROM projectdetails
WHERE projectid = '1';
but it return with the missing right parenthesis.
View 11 Replies
View Related
Oct 16, 2013
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
[code]....
View 23 Replies
View Related
Aug 14, 2012
I have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.
Here is my Cursor:
CURSOR c_chk_style IS
SELECT DISTINCT 1
FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
[code]...
Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.
View 15 Replies
View Related
Dec 27, 2011
i was searching for the example of subject mentioned and visited the following website
[URL]...
here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?First, they have created following Packages
PACKAGE concl_cv IS
type conclass_rec is RECORD
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
[code]...
then, created Functions which created in Ref Cursor Query in Data Model
function q_portdescRefCurDS return port_cv.portdesc_refcur is
temp_portdesc port_cv.portdesc_refcur;
begin
open temp_portdesc for select department_id, department_name from departments;
[code]...
nothing here any Close cursor statement.i am trying to explore this area of the development.
View 2 Replies
View Related
Jul 3, 2012
I would like to know if there is a way to define values in a field to a defined new data.
As an example:
If field A value is equals to May, then set field "Quarter" data value to "2nd Quarter".
If field A value is equals to January, then set field "Quarter" data value to 1st Quarter".
View 2 Replies
View Related
Feb 4, 2011
After validating the SQL statemnet during creating a Custom folder in OracleBi Admin following Error displays. While the same query execute in toad successfully.
The Custom SQL entered is not a valid SQL statement - ORA-01031:Insufficeint Privileges
View 1 Replies
View Related
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 4, 2010
Am looking for some stuff that allows me to get the query text of a query in Oracle Reports. Something like : get_query_text(query_name IN VARCHAR2);
View 5 Replies
View Related
Jun 10, 2013
i have this query
select emp_name, emp_id , telephone , address , age
from hr_detailes;
the data in column (telephone) such as 5555555 how can make all data in this column must be in a parenthesi such as (555555)
View 2 Replies
View Related
Mar 19, 2012
When running my query in sql developer, I have to execute the command apps.fnd_global.apps_initialize(user_id, resp_id, resp_appl_id) in order to show the result of my query. Without this, the query returns nothing. The problem is I am using this query as a custom query for my Discoverer report and my report does not show any record.
View 3 Replies
View Related
Sep 9, 2013
i have a table named actual_qty,
in that table i have data store in the following columns:
MaterialCode Location Qty
------------ -------- ---
1111 vizag 2
2222 vizag 1
3333 vizag 4
1111 hyd 4
2222 hyd 6
3333 hyd 2
1111 sec 9
2222 sec 1
3333 sec 0
i want query to display data data in the following form
MaterialCode vizag hyd sec
------------ ----- --- ---
1111 2 4 9
2222 1 6 1
3333 4 2 0
View 5 Replies
View Related
Aug 8, 2013
My user would like to have a way to attach files to database records. For simplicity, we'll say that each employee in the database could have 0-many file attachments associated with them. I know how to make this part work using Oracle Forms.
So that's the easy part for me. The tough part is that they want to be able to run an Oracle Report on our application server (displays as a PDF, also downloadable as a PDF) and have links on this report to the attachments that they uploaded using the forms process above.
For the idea of storing the files on a folder somewhere, I simply created a text object on the report that had it's hyperlink property pointing to the file location. Done.. opens fine.
However, I want to be able to have the option of storing these files in the database instead, just in case we can't go with the shared folder idea. I'm not sure how to make this work. I can store a blob in the database.. but how do I link to that blob on the report for them to be able to download it? Is this even possible?
how to create a download link on the Oracle Report that let's them download a file out of the database.
View 5 Replies
View Related
