PL/SQL :: Ora-00936 Missing Expression?

Aug 22, 2012

i am posting create and insert statements for a problem i am facing

create table tgh(NAME VARCHAR(20),LANG VARCHAR(20))

insert into tgh values('AC','ORIYA')
insert into tgh values('DF','BENGALI')
insert into tgh values('ik','english')

the below statements are for table copu

create table copu(CNAME VARCHAR(20),CLANG VARCHAR(20))

insert into copu values('ACdf','oriya')
insert into copu values('deg','ptot')

when i do  another insert as shown below

insert into copu (cname,clang) values  where
copu.cname not exists in( select  tgh.name,tgh.lang from tgh where upper(tgh.name)=upper(copu.cname))

i get ora-00936 missing expression

View 12 Replies


ADVERTISEMENT

ORA-00936 :: Missing Expression?

Jul 9, 2010

im trying to take a query that pulls all the info in the current tables, and narrow it down to one day plus current. here is the error i am getting.

(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE, oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY
*
ERROR at line 5:
ORA-00936: missing expression

old query:

 select a.assign_no, a.assign_type, a.act_end, X.ord_no, x.account_no, 
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY 
from clsd_assign a, clsd_task t, clsd_task_detail td,  sku s,
(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE,  oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY

[code]...

new query

 select a.assign_no, a.assign_type, TRUNC(A.ACT_END), to_char(a.act_end), X.ord_no, x.account_no, 
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY 
from clsd_assign a, clsd_task t, clsd_task_detail td,  sku s
where  a.act_end  between to_char(sysdate -1, 'dd-mon-yyyy') and to_char(sysdate, 'dd-mon-yyyy')

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Getting ORA-00936 / Expression Missing Error?

Mar 25, 2013

I ran into a problem recently which has to do with the following query:

SELECT '' || ROUND(GEOGR_LAENGE,5) || ',' || ROUND(GEOGR_BREITE,5) || '," [BUE] ' || BAUFORM_GRUPPE || '"', KM
AS TEXT
FROM MYTABLEA mmtable
WHERE (mmtable.GEOGR_LAENGE IS NOT NULL
AND mmtable.GEOGR_BREITE IS NOT NULL
AND mmtable.STRECKE_NR IN
(

[code].....

The problem is, that Oracle alerts a missing expression at line 4, and highlights the "IS NOT NULL". Personally I don't see that anything is wrong with this line. I think the problems source is somewhere else but I cannot find it.

I'll give you a little bit of background to the script:

What I am trying to achieve is to request the values in the first line with the following conditions:

- mmtable.GEOGR_LAENGE, GEOGR_BREITE must not be empty
- rbtable.STRECKE_NR for the requested line (which is similar to mmtable.STRECKE_NR) must have "MITTE" as a value for NL_NAME
- mmtable.KM => rbtable.VON_KM
- mmtable.KM <= rbtable.BIS_KM

so that only those lines will be returned.

View 8 Replies View Related

Getting ORA-00936 / Missing Expression Error?

Dec 4, 2012

I'm working on Oracle SQL Developer: ODMiner. I keep getting a syntax error with the following code,

CREATE TABLE FINAL_WEBLOG AS
select SESSION_DT, C_IP, CS_USER_AGENT,
LISTAGG(WEB_LINK, ' ')
WITHIN GROUP (ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS",
FROM WEBLOG_VIEWS
GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
ORDER BY SESSION_DT

I understand if it's the semicolon that is missing after "FROM WEBLOG_VIEWS", but I'm not sure on how I can rephrase it.

The error I got was,

Error at Command Line:5 Column:2
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:

View 1 Replies View Related

SQL & PL/SQL :: ORA-00936 - Missing Expression Error While Running

Mar 28, 2013

I am getting the ORA-00936: missing expression error while running the below stated query. Problem is that it is running and fetching the data successfully for the first 5 runs. But from the 6th run it is not fetching the data and throwing the ORA-00936: missing expression error. consider I am passing parameters to the query and for the first 5 values i pass its runnign fine. when i run the query for the 6th time or more with different parameter, its throwing the error. it does not seems to be the data issue because when i pass the 6th parameter for the first run on the next day, it is fetching data.

(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,
ABC.EST_LIQN_D, '' AS EVNT_TYPE_C, ABC.CORR_STAT_C
FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED
WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
and ED.ENTR_DOC_I = '123'AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I
AND ED.ENTR_TYPE_C != 11
AND POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS'
[code]....

View 5 Replies View Related

PL/SQL :: Using NVL Function In Dynamic SQL / ORA-00936 / Missing Expression

May 8, 2013

I have created a procedure using the Dynamic SqL and while using the NVL() getting the following error . ORA-00936: missing expression.

The query I have written as

SQL_Txt:=' INSERT INTO VF.tblCData (A, B, C, D, E, F,G,H,I,J)
SELECT '||l_A||',
'||l_B||',
'||l_C||',
'||l_D||',
NULL ,
'||L_F||',

[code]....

For Param1 I have data for one execution and Param2 and Param3 is null for that execution.While executing the same I am getting below

INSERT INTO VF.tblCData (A, B, C, D, E, F,G,H,I,J)
SELECT 25,
1,
7,
6,
NULL ,
5,

[code]....

and error ORA-00936: missing expression is popping up for Param2 and Param3 NVL(,'')

View 10 Replies View Related

SQL & PL/SQL :: To_char Function In Combination With Distinct Clause - ORA-00936 / Missing Expression

Mar 2, 2012

I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.

When I use distinct clause inside a to_char function it gives the following error:

ORA-00936: missing expression
00936. 00000 - "missing expression"

The Sql is

select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales

I can't see any syntax error in the sql..but forsome reason, it doesn't work.

View 2 Replies View Related

SQL & PL/SQL :: Subtract Dates - Missing Expression

Nov 13, 2012

New to using Oracle and SQL Developer. I am trying to subtract a maximum date from today and adding back 1 to get a field named daysSinceLastActivity....

Syntax I am trying is (date()-max(activity_date))+1 as daysSinceLastActivity

I keep getting a error message of missing expression. After googling, I am not finding anything. This should be a simple calculation.

View 3 Replies View Related

PL/SQL :: Missing Expression Error during EXECUTE IMMEDIATE?

Jun 6, 2012

CREATE TABLE  "T_LIDER"
   (     "LIDER" NUMBER(5,0),
     "TRR" NUMBER(8,0),
                "SUG_I"  NUMBER(2,0),
      "LIDER_TAY" NUMBER(2),
   
[code]...

i've tried to simplify the problem and i saw that the function returns this error what seems to be the problem ?

View 11 Replies View Related

SQL & PL/SQL :: Dynamic Statement In Database Package - Missing Expression

Feb 29, 2012

I have the following statement dynamic sql statement in a database package.

begin
EXECUTE IMMEDIATE 'SELECT REPLACE(SUBSTR('||V_WHERE_CLAUSE||',1,3, ''AND'', NULL) INTO V_WHERE_FILTER FROM DUAL';
EXCEPTION
NULL;
end;

When executed it gives following error.

ORA-20000: ORA-20000: ORA-00936: missing expression
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 931
### END TRACE ###
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 935
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 164
### END TRACE ###
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 184
ORA-06512: at line 9

View 4 Replies View Related

JDeveloper, Java & XML :: Missing Expression And Then Group Function Not Allowed Error

Feb 23, 2011

I have 2 tables used in this problem: ODETAILS and ORDERS.

ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST

UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);

In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.

View 3 Replies View Related

ODP.NET :: Ora-00936 With Simple Select Statement?

Dec 19, 2012

I get "ORA-00936: missing expression" when I issue the following SQL:

select 4 - .2 from dual

from the following VB.NET code (3.5 framework):

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))

I have the following installed when connecting to 10.2.0.3 database:
11.2.0.1 client installed
11.2.0.3.20 ODP installed

This is really strange behaviour as the following SQL variants work:
select 4 - 0.2 from dual
select 4 - '.2' from dual
select -.2 + 4 from dual

This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11

View 11 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>create table test900 ( a number, b number);

Table created.

ved>insert into test900 values( 9,'');

1 row created.

ved>insert into test900 values( 10,null );

1 row created.

ved>select * from test900;

A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?

View 9 Replies View Related

PL/SQL :: How To Format A Value Using Reg Expression

Jan 11, 2013

I need to format a value using Regular Expression.

9911223344, 9911223344
9911223344
11223344Result
(99) 1122-3344, (99) 1122-3344
(99) 1122-3344
1122-3344

View 4 Replies View Related

PL/SQL :: Not A Group By Expression

Apr 1, 2013

when i am running the below plsql block i am getting the error like not a group by expressiong.

DECLARE
   CURSOR Cur_st
   IS                       
SELECT DISTINCT
            CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
            CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
            CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
            CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
            TO_DATE (A.SALES_DATE, 'YYMMDD'),
            CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
[code]....

View 19 Replies View Related

SQL & PL/SQL :: Regular Expression In Oracle 10g

Apr 13, 2013

What is the use of Regular Expression?

View 1 Replies View Related

SQL & PL/SQL :: How To Use Substr Or Regular Expression

Jan 21, 2011

I have a following table,

CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......

I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z

How do i use Substr and Regular expression here ?

View 11 Replies View Related

SQL & PL/SQL :: Objects Containing Expression Cannot Be Used As Assignment

Apr 4, 2012

I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...

CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT (
strName VARCHAR2(100),
--
CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT,
--
MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2,
MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2)
) NOT FINAL;
[code]....

How can I do this parentObject.getChildObject().setChildFunction()?

View 5 Replies View Related

SQL & PL/SQL :: Breaking Up Expression To Evaluate?

Sep 4, 2013

I have a complex expression coming as an input and I have to break that into smaller one to evaluate each of them. Explained in example:

Let's assume, I have an input string as "(({A}&{B})||({C}||{D})&({E}||({F}&{G})))". My requirement is to break into logical smaller groups like

1. ({F}&{G})

2. ({E}||({F}&{G}))

3. ({C}||{D})

4. (({A}&{B})

View 15 Replies View Related

SQL & PL/SQL :: ORA-00979 / Not A GROUP BY Expression

Jan 3, 2012

I wrote a query for minimum average salary of Job_id using inline view but it is not working..

The query is as follow

SELECT job_id, avg(salary)
FROM employees , (SELECT MIN(AVG(salary)) min_avg_sal
FROM employees
GROUP BY job_id) b
GROUP BY job_id
having avg(salary)=b.min_avg_sal;

It returns error as

################################
having avg(salary)=b.min_avg_sal
*
ERROR at line 6:
ORA-00979: not a GROUP BY expression
#########################

View 2 Replies View Related

PL/SQL :: Regular Expression Select

Jun 6, 2012

Sample Data:

SELECT 'A/BCD/CCE/DFFFF' test  from dual
Expected Output:

SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;  

View 6 Replies View Related

SQL & PL/SQL :: Expression Column With A Group By Clause?

Mar 28, 2011

getting expression into a Group By query in oracle.

I have a simple table with two columns. 'ID' and 'Amount'

I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.

select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID

This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).

View 5 Replies View Related

SQL & PL/SQL :: Evaluate Dynamic Math Expression?

Jun 22, 2008

I have a user interface where the user can enter a formula using a set of parameters and operators. For eg , the user is given a list of parameters, say : Width, Height , Gauge. The list of operators include standard math functions : + - / * ^

He can select and create a formula like gauge * width. This is stored in a table. At a later time a job is scheduled to evaluate the formula based on parameters for width, gauge and height.

I can evaluate the expression by substituting each parameter.

I can do this using dynamic sql. However I'm not sure how to evaluate the ^ symbol. The equivalent of this is power function.

So if a user enters (height * 2) + (width^2) . How do i evaluate this. I am doing this in pl/sql.

View 6 Replies View Related

SQL & PL/SQL :: Execute The Expression In Select Statement

Feb 12, 2013

CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;

CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;

I want to be execute the expression should run in select statement how to do? and tried as like below,it's not working.

select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;

View 15 Replies View Related

SQL & PL/SQL :: PL-00382 Expression Is Of Wrong Type

Apr 12, 2013

I am using pipelined functions. I've written a few with no problem this one seems to be giving an error when I am using techniques that appear very similar to ones that work.

I am doing this all in a package;

The type definition is;

TYPE SUSPECT_LINKAGES_FAC_RECORD IS RECORD
(
PATIENT_ID TUMOR.TUMOR_PATIENT_ID%TYPE,
CENTRAL_SEQ TUMOR.TUMOR_CENTRAL_SEQ%TYPE,
MP_REVIEW_FLAG NUMBER(1),
FACILITY_FLAG NUMBER
);

The variable definition is;

OUT_REC SUSPECT_LINKAGES_FAC_RECORD;

The line with the error is;

PIPE ROW(OUT_REC);

This is the entire function;

FUNCTION GET_SUSPECT_LINKAGE_FAC_FLAGS RETURN SUSPECT_LINKAGES_TABLE PIPELINED AS
CURSOR CURS_SUSPECT_LINKAGES IS
SELECT * FROM TABLE(TUMOR_UTILITIES.GET_SUSPECT_LINKAGE_FLAGS())
order by 1,2,3 DESC;
TEMP_REC SUSPECT_LINKAGES_RECORD;
MATCH_COUNT NUMBER;
OUT_REC SUSPECT_LINKAGES_FAC_RECORD;

[code].....

I get "PL-00382 expression is of wrong type" on both pipe row (out_rec); lines.

View 1 Replies View Related

SQL & PL/SQL :: PLS-00382 - Expression Is Of Wrong Type

Mar 19, 2013

i got this error 'PLS-00382: expression is of wrong type'
--declaration

l_recipe_detail_tbl apps.gmd_recipe_detail.recipe_detail_tbl;
begin
ln_recipe_id := NULL;

[Code].....

View 1 Replies View Related

Function To Take In Expression And Fetch The Result

Jun 5, 2009

I have an arithmatic expression which is dynamic say

expression = [Col5]/[Col1]

I will be using this in building a dynamic SQL.so i have to make sure that the divisible by zero is taken care. In the expression Col1,Col5 are values coming from a SQL.

For the above expression, my conversion will be

ROUND( ((CCol4 / DECODE (CCol1, 0, NULL, CCol1) )), 0).

I have to build a function which takes in the expression and fetches me the result.My expression can be any combination of arithmaticexpression involving columns.

Ex:
[Col4 + Col2]/[Col3-Col1]*[Col5].

View 1 Replies View Related

SQL & PL/SQL :: ORA-00979 - Not A GROUP BY Expression At Runtime?

Oct 13, 2010

I ran into something that has me wondering about which errors are caught at compile-time, and which are caught at runtime. There is a pretty big package with lots of complicated queries that heavily use GROUP BY, and in the last couple of years I thought I never had the case of an ORA-00979 happening an RUNTIME, which is now happening.

The last heavy changes in that package did I make in the Oracle 9.2 days, and I *believe* (although my memory might be wrong) that back then the ORA-00979 errors came up at compile time.

So is it normal that the error doesn't show up during compile time, only during runtime, or should it be considered a bug?

SQL> CREATE TABLE test_tab(a NUMBER(1), b NUMBER(1));

Table created.

SQL> INSERT INTO test_tab VALUES (1,1);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_prod AS
2 v_a NUMBER;
3 v_b NUMBER;
4 BEGIN
5
6 SELECT a,b
7 INTO v_a, v_b
8 FROM test_tab
9 GROUP BY a;
10
11 END;
12 /

Procedure created.

SQL>
SQL> BEGIN
2 test_prod;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "PFK.TEST_PROD", line 6
ORA-06512: at line 2

View 5 Replies View Related

SQL & PL/SQL :: Can Group BY Clause Have Expression Field

Apr 4, 2011

Can Group BY clause have an expression field?

Say for example, I want to group by the fields "Type" and and expression called "expression" (which is a case statement). I tried running this query and it says "expression" is invalide identifier.

select type, case when SUBSTR (glnumber, 1, 2) = '05' then 'IS'
when SUBSTR (glnumber, 1, 2) = '06' then 'IS'
else 'BS'
end "expression" , sum(balance)
from table
group by TYPE, "expression"

If there is any online material on how to GROUP BY on an expression(like above)

View 5 Replies View Related

PL/SQL :: Regular Expression Matching All Given Words

Nov 8, 2012

I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.

select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');

Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.

search words : one two

one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.

View 7 Replies View Related







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