SQL & PL/SQL :: Sum When Reference In Another Table?
Dec 19, 2011
Two tables: article i mutations
Article:
artno descr qty sales
1 beer 1 5
2 coke 1 7
3 wine 1 4
4 beer ct 12 2
5 coke ct 6 3
6 wine ct 12 2
7 beer pl 336 1
8 coke pl 336 0
and mutations:
artno mutation
1 4
1 7
2 5
2 8
3 6
I want to get the result like
article sales
beer 365 '5+2*12+1*336
coke 25 '7+3*6
wine 28 ' 4+2*12
How to do a query.
View 39 Replies
ADVERTISEMENT
Sep 8, 2013
update one table with reference to another.
Table1:event_channel
Table2:event_inst
I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id).
event_inst table column(event_instance_id) has respective 4 records in event_channel
View 5 Replies
View Related
Mar 18, 2013
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
SELECT
TableA.ID,
SUBSTR(TableA.Code,1,4) Primary_Code,
[Code].....
View 6 Replies
View Related
Sep 13, 2012
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
View 2 Replies
View Related
Jan 11, 2012
There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
View 5 Replies
View Related
Jun 25, 2012
I have the following table:
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL);
INSERT ALL
INTO test_A VALUES (1, 'mem1', NULL)
INTO test_A VALUES (2, 'mem2', NULL)
INTO test_A VALUES (3, 'mem3', NULL)
INTO test_A VALUES (4, 'mem4', 1)
INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
1mem1(null)
4mem41
5mem51
6mem61
2mem2(null)
7mem72
8mem82
10mem102
3mem3(null)
9mem93
11mem113
View 3 Replies
View Related
Oct 1, 2011
I have to create a function which count the max id in the provided table.
For example: I have two table customer and book
and
create or replace function Row_Count(tab_nam) return varchar2 is
CONTR_NO varchar2;
begin
select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;
return(CONTR_NO);
end Row_Count;
when I call
Function( customer) ;
or
Function( book) ;
It should tell the max number with addition of one.
View 4 Replies
View Related
Sep 24, 2013
I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.
mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;
1. table parent (P_ID, P_Name)
2. table child (C_ID, C_Name, P_ID)
i need data bello way.
Parent Table data:
P_ID P_Name
----- ------
1A
2B
3C
4D
Child Table data:
C_IDC_NameP_ID
--------------
1test11
2test21
3test32
4test42
5test53
6test53
7test64
8test64
My controll file
-------------------
LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
[code].....
able to load parent data and generate P_ID, but i am not able to get P_ID for child records.
View 6 Replies
View Related
Jul 21, 2011
I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.
This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.
how to reference a field by its location or position in the query.
We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...
foreach($myrow as $field=>$value)
and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.
View 1 Replies
View Related
Jan 20, 2011
how I can write a query that get a column data point to another column data in a csv file? For example:
Column A Column B
First Name Mike
Last Name Smith
View 6 Replies
View Related
Dec 24, 2010
I simulated a sample procedure for my requirement.When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall.
create table dynamic (emp_name varchar2(20),emp_id varchar2(20), tel_no varchar2(20);
create table dynamic_1 (emp_name_1 varchar2(20),emp_id_1 varchar2(20), tel_no_1 varchar2(20);
insert into dynamic values ('Mike','1','123456');
insert into dynamic values ('Nike','2','1234567');
create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
[code]....
View 21 Replies
View Related
Jun 6, 2011
I use some quantity of functions with list of account id like in example below. Some functions use the same account id list, some use another. Everything works fine except those days when changes come and lists should be updated. Then I should edit each function... I think about creating new table for reference list like
CREATE TABLE MYREFERENCELIST
AS
SELECT XXXX AS ACCOUNTID, YYYY AS LIST1 FROM DUAL
UNION ALL
SELECT ZZZZ AS ACCOUNTID, UUUU AS LIST2 FROM DUAL
FUNCTION ACCOUNTID
(arc_date date,
cid number )
RETURN NUMBER
AS
[code]..........
View 11 Replies
View Related
Sep 24, 2013
problem with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
[code]....
View 13 Replies
View Related
Apr 18, 2011
I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.
CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,
[Code]...
View 1 Replies
View Related
Feb 19, 2013
When I am executing below code it is working fine
DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN
[code].....
But when i am executing below code it is showing error as subprogram or cursor 'C1' reference is out of scope
DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN
[code].....
The only difference in above two blocks is that EXIT WHEN Statement.
View 2 Replies
View Related
Mar 1, 2013
I am getting the error ORA-06531: Reference to uninitialized collection while calling a table function which is of collection type.
CREATE OR REPLACE FUNCTION FN_GET_LINK_SYS_ID
RETURN PGIPK_DM_PROCESS.DM_SYSID
PIPELINED IS
T_SYS_ID PGIPK_DM_PROCESS.DM_SYSID;
BEGIN
T_SYS_ID := PGIPK_DM_PROCESS.DM_SYSID();
[code]....
Definition of Type is below
--NESTED TABLE TO STORE SYS_ID S OF TABLE
TYPE TAB_SYS_ID IS RECORD(
POL_NO VARCHAR2(60),
POL_END_NO_IDX NUMBER,
PSEC_SEC_CODE VARCHAR2(30),
PRAI_RISK_ID VARCHAR2(10),
[code]....
when i m writing select * from table(FN_GET_LINK_SYS_ID) , its throwing ORA-06531: Reference to uninitialized collection.
View 6 Replies
View Related
Jun 2, 2009
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
[code]...
This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
A. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS);
B. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
C. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
D. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);
Answer: A
----I had gone through this question in dumps,about PRAGMA_RESTRICT_REFERENCES, but i m not aware of WNDS, WNPS..or reference for this term, & how it works in above exampl
View 1 Replies
View Related
Mar 6, 2013
I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):
create table Test_req (
Req_ID varchar2(4) not null,
Req_Comment varchar2(50) null
);
create table Test_n_cycle (
Req_ID varchar2(4) not null,
[code].....
Then I successfully performed the two alter statements:
Alter table test_n_cycle
add constraint test_n_cycle_pk primary key (req_id, test_id);
Alter table test_req
add constraint test_req_pk primary key (req_id);
Then tried this freign key reference statement
Alter table test_n_cycle
add constraint
foreign key (req_id) references test_req (req_id)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?
View 3 Replies
View Related
Aug 19, 2012
is it in Orcale not possible to refenerence two tables in a with clause?
With CTE
AS
(
Select ...
),
[code]...
If I do a select on my first table I get this error: ORA-32035: unreferenced query name defined in WITH clause - With CTE AS
In Sql Server it works fine ...but how can I create two CTE Tables in Orcacle?
View 17 Replies
View Related
Jul 8, 2013
I am using oracle 10G version. when i compiled the code I got the error "PLS-00487: Invalid reference to variable 'PROGRAM.MWPRG_PROGRAM_ID%TYPE'
correct the below PLSQL error code (marked Bold)?
PLSQL procedure:
===================
create or replace
PROCEDURE SPT_PROGRAM
IS
[code]....
View 2 Replies
View Related
May 6, 2011
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv
A
BA
CB
DC
EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
View 5 Replies
View Related
Jul 2, 2012
When I am trying to create a public synonym for a package then I am getting this error. This error tells us that non editioned object(Public Synonym) can not refenrence the editioned object(Package in this case).One of the solution provided on one of t he site is to create local synonym instaed of public synonym but I need to create public synonym only.
View 6 Replies
View Related
Jun 8, 2011
I need to know how to capture the Parameter Value of a Procedure in a Variable and reference that variable in another Procedure or Package.
View 12 Replies
View Related
Apr 1, 2013
I want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies
View Related
Aug 5, 2010
I have a problem :
In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.
View 9 Replies
View Related
Mar 30, 2011
I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .
CREATE TABLE ref_parent1 (
table_name VARCHAR2(30),
order_date DATE,
num_rows NUMBER);
INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100);
INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000);
INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000);
COMMIT;
[code]....
ORA-14652: reference partitioning FOREIGN KEY IS NOT supported
View 4 Replies
View Related
Jul 23, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf
[code]...
View 3 Replies
View Related
Oct 25, 2010
We have a package that contains mostly constants like:
c_flag_true CONSTANT CHAR(1) := 'Y'
When I try to reference it in these constants in an adhoc query it doesn't work:
SQL> select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true;
select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true
ORA-06553: PLS-221: 'C_FLAG_TRUE' is not a procedure or is undefined
Is there a simple solution to make the reference work?
View 4 Replies
View Related
Feb 19, 2012
I need to say I am an absolute NOOB when it comes to SQL.I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.
The current script which I use via TOAD on our companies READ ONLY database is this:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN ('1230001', '1230002', '1230003')
This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.
What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.So I imagine the query would look something like:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN (check
View 13 Replies
View Related
Jul 25, 2013
i am getting the error as : FRM-40105:UNABLE TO RESOLVE REFERENCE TO ITEM MTL_SYSTEMS_ITEMS.ORGANISATION_ID.
when clicking the button to enter to into other managed attachments .
View 1 Replies
View Related
