SQL & PL/SQL :: Add Parenthesis To Query

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


ADVERTISEMENT

Query - Missing Right Parenthesis?

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

Top / Bottom Query - ORA-00907 / Missing Right Parenthesis

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

Parenthesis Create Error In Query / How To Clean Text Before Use

Nov 10, 2008

i am binding a string to a CONTAINS search (oracle text) and its throwing an error because of characters within the string.

this causes the error.
Quote:
(A. A.)
if i take out ( and ) it works
Quote:
A. A.

i thought binding took care of these issues as well as sql injection?

example:

SELECT
score(1) the_score,
id
FROM
t_table
WHERE
contains(text_column, :BINDVAR1, 1) > 0

how can i get around this and other character errors on the fly?

View 4 Replies View Related

SQL & PL/SQL :: Update Query Fail With Error - Missing Right Parenthesis

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

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 View Related

PL/SQL :: Missing Right Parenthesis

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

ORA-00907 - Missing Right Parenthesis?

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

ORA-00907 - Missing Right Parenthesis

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

SQL & PL/SQL :: Ora-00907 - Missing Right Parenthesis?

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

Missing Right Parenthesis Error?

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

PL/SQL :: ORA-00907 - Missing Right PARENTHESIS?

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

Missing Right Parenthesis All In Line 2?

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

SQL & PL/SQL :: ORA-00907 / Missing Right Parenthesis

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

ORA-00907 Missing Right Parenthesis

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

Missing Right Parenthesis Error On Line 7

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

Server Utilities :: ORA-00907 / Missing Right Parenthesis

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

PL/SQL :: Insert Results To Fact Table - Missing Right Parenthesis

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

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

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

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: How To Read Field From Query In Another Query

Sep 19, 2010

I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.

Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3

Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5

View 4 Replies View Related

PL/SQL :: Converting Inner Query To Sub Query

Sep 18, 2012

This query is written in inner join, can any one try to write using sub query.

SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Query For Top N Max

Feb 23, 2012

I have a a table like with columns ( date_field, client_id(c_id), transaction_id(trx_id), mobile, amount )

table example data like

date_fieldc_idtrx_idmobileamount
24-JAN-1215100100120111111100100
24-JAN-1217100100220111111112150
24-JAN-1215100100320111111113100
24-JAN-1216100100420111111114200
24-JAN-1215100100520111111115100
24-JAN-1216100100620111111116100
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1215100100920111111119200
24-JAN-1216100101020111111110100
24-JAN-1215100101120111111111100
24-JAN-1216100101220111111112100
24-JAN-1215100101320111111113100

Now using the unique index (Trx_id) I need to get max 3 records for each client (c_id).

Expecting result should be

date_fieldc_idtrx_idmobileamount
24-JAN-1217100100220111111112150
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1216100101020111111110100
24-JAN-1216100101220111111112100
24-JAN-1215100100920111111119200
24-JAN-1215100101120111111111100
24-JAN-1215100101320111111113100

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved