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()?
Table 1 have 3 columns ID, CUS_NAME, LOC insert into table1 values (001,ABC,North); insert into table1 values (002,DEF,South); insert into table1 values (003,GHI,West);
Table 2 have 3 columns ID, CUS_NAME, LOC insert into table2 values (001,ABC,North); insert into table1 values (002,DEF,East); insert into table1 values (003,JKL,South);
Table 3 is Result_Tab table having 8 columns ID, TAB1_CUS_NAME, TAB2_CUS_NAME, Cus_Name_Res, TAB1_CUS_LOC, TAB2_CUS_LOC, Cus_LOC_Res, Comment.
I have written two cursors which fetches data from both the tables and compares each data between each other and inserts the value into the result table.
the code is as follow:
Insert into Result_Tab values (T1.ID, T1.Cus_Name, T2.Cus_Name, decode(T1.Cus_Name,T2.Cus_Name,'Y','N'),T1.LOC, T2.LOC, decode(T1.LOC,T2.LOC,'Y','N'),Null);
Now I want the resul as follows: ID T1.N T2.N N_Res T1.L T2.L L_Res Comment 001 ABC ABC Y North North Y Null 002 DEF DEF Y South East N Loc 003 GHI JKL N West South N Name, Loc
Is there a way wherein i could capture the column names in decode function when it doesn't match, so that I can insert the same in the comment column.
The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.
?I have the following requirement, the output should be:
Ticket count (sr_number) % of tickets inside DL Number of tickets inside DL Average cycle time (cycle time = closed date - created date) Total cycle time (cycle time = closed date - created date) Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
It appears that the Oracle RAC cluster does not recognize the 'dba' group membership assignment to a user, unless it is assigned as the user's primary group.This also only affects newly created users, older users do not have this issue.
This affects us in the following way:
We are unable to create new cluster resources with the SAPCTL tool with the <sid>adm user.
When we try to create a resource using sapctl as the <sid>adm user we receive the following error:
CRS-0259: Owner of the resource does not belong to the group. SAP ABAP VIP creation failed.
The description of this error is that the resource owner (<sid>adm) is not a member of the group defined by 'osdbagrp' (what the cluster expects group membership for os users to control cluster resources), in our case this group is 'dba'.This is despite the user definitely being a member of the group.
Resource creation workaround:We are able to get around the resource creation by creating the resources either as root user, or by assigning 'dba' as <sid>adm's primary group.However, although we are able to control the VIP cluster resource, we are unable to control the other SAP enqueue resources as we assume that they need to be created by a user with 'sapsys' as a primary group (i.e. the resources do not start or it appears that there is not even an attempt to start the resources as there are no SAP startup logs created).
We have attempted multiple variations of creating and controlling cluster resources: changing <sid>adm primary group, changing cluster resource permissions, with nothing working for us.
The bottom line is that we need to be able to create these sapctl resources with a <sid>adm user that has 'sapsys' as a primary group while also being a member of the 'dba' group.
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]....
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')
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 #########################
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.
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.
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.
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.
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.
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
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
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)
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.
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.
i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:
select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818 (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or 928818 is null)
and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.
