I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.
I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.
best way as it does a self join on big table of 9.5 million.
(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))
I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'
problem is report is showing 22 records of leavetype 'CL' of same leavedate!
i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003
For leavetype
FUNCTION Cf_1formula RETURN CHAR IS v_leavetype CHAR(40); BEGIN SELECT LT.description INTO v_leavetype FROM hrm_attendance L, hrm_leavetype LT, hrm_employees E [code]....
i'm trying to put more than one value in one column (by putting value '26','63' in column), so i have next problem:
1. Query select cr_pjid from acc_users where username='ACCBTPS121' give result CR_PJID --------- '26','63'
2. Query select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in ('26','63'); return 186 rows.
3. Query: select * from acc_accbtp_nova_view where ed_id=2 and to_char(pj_id) in (select cr_pjid from acc_users where username='ACCBTPS121'); doesn't return any row...
Most of the code is working properly except when it come to a person with more than one email type in the table:
Below is the
select emal_pidm, emal_email_address, emal_emal_code, case when emal_emal_code = 'PER' and lag(emal_emal_code,1,'?') over (partition by emal_pidm order by emal_email_code) = 'EMPL' then emal_email_address [code]......
Below is Sample data for testing
INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code) VALUES (1024069, 'emmaus.ferdinand@xxxx.edu','EMPL'); INSERT INTO emal (emal_pidm, emal_email_address, emal_emal_code) VALUES (1024069, 'emfer1@xxxx.edu','PER'); [code].......
The attachment is what the output should look like.
Here is my requirement..... I am pretty new to PL/SQL.
This is the procedure
CREATE OR REPLACE PROCEDURE ABCPROC.SP_ABC ( XML IN CLOB, P_refcursor OUT SYS_REFCURSOR ) AS BEGIN [code]........
For each account number (I_AC), we have multiple rows in ABC_SVC table. I want to return these values in the refcursor. The issue with above SQL program, the row is returning only when the account(I_AC) have values for all the products 100,101,102. If the row does not exist for one account, then the account row is returned with other products.
I want my output should be in the below format
ACCOUNT_NUMBER COMMISSION CONSUL CONTRA 1YYN 2NN 3N
create table teststr (indname varchar2(20), counter1 number,counter2 number,counter3 number,counter4 number); insert into teststr values('a',10,20,30,30); insert into teststr values('b',10,20,5,3); insert into teststr values('c',2,4,5,2); insert into teststr values('d',1,2,3,4); insert into teststr values('e',4,5,4,4);
Now i need the output if any of the column values are same.
output should be
select indname from teststr where counter1=counter2 or counter1=counter3 or counter1=counter4 or counter2=counter3 or counter2=counter4 or counter3=counter4
a c e
Is ther any other way to write the query instead of the numerous or conditions if i want to compare the column values in a table.
I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type 123Amount 123Percent 456Amount 789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30); insert into temp_acronyms values(1, 'ABC'); insert into temp_acronyms values(2, 'DEC//NOFO'); insert into temp_acronyms values(3, 'CBK//FO TO USA'); insert into temp_acronyms values(4, 'DEC//NO ENTRY'); insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM --- -------- 1 ABC 2 DEC//NOFO 3 CBK//FO TO USA 4 DEC//NO ENTRY 5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
I have a table which stores customer ID and price lists assigned to those customers. Each price list can have multiple customers assigned to them. So for example
Customer APrice_List1 Customer BPrice_List1 Customer C Price_List2 Customer D Price_List3
Now I am trying to write a SQL statement to return just pricelists which contain just ONE customer (so in the above example would just return Price_list2 and Price_List3).
We are using the function dbms_utility.get_hash_value to create a unique identifier, the hash is created based on statement of creating an index. We see that for a different statement is being generated the same value, which in this cause errors because this value must be unique.
The Hash is to be calculated as follows HASH_VALUE = dbms_utility.get_hash_value (sql_text, a 10000)
We are using the correct the hash function or should we use another way to generate unique values?
I don't want to generate row_number for unique values 'C' and 'E' in below query.
SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) FROM (SELECT 'A' NAME FROM DUAL UNION ALL SELECT 'A' FROM DUAL UNION ALL SELECT 'A' FROM DUAL
[code].....
Means row_number should be NULL for unique values.
Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.
A B ---------- ---------------------------------------- 1 Hello 2 Hello 3 No Hello 4 No Hello 5 Hello 6 Hello 7 Hello
I want to print the output of this table as -->>
A B ---------- ---------------------------------------- 1 2 Hello 3 4 No Hello 5 6 7 Hello
To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.
I have two different java process trying to insert in the same time in the same table for the same trade. The structure of the table Report is
create table report ( TRADE_ID NUMBER, VERSION NUMBER, MESSAGE_TIME TIMESTAMP)
There is a unique key on (TRADE_ID and VERSION) So if a new trade_id is inserted, the version is set to 1 and the second becomes 2 and so on. The version is calculated as last version of the trade_id ie. version + 1. It was woking fine till a new Java process was build that fired inserts through ten different java instances at the same time resulting in unique key error. So in detail what is hapenning is if three records of trade_id's comes in at the same time it should allocate versions in a first come first serve basis and there should be three versions of trade id 1,2 and 3. Now due to the multiple instances they all seems to get fired at once and all ending up with version one and thus resulting in unique key constrain error while trying to insert into the table.
In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).
To go into some detail -
I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),
however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,
so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).
In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.
My requirement is as below,
While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.
SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);
Index created.
SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE; ALTER INDEX index_debug1 ADD COLUMN MESSAGE * ERROR at line 1: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.
I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.
Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.
I'm trying to make a foreign key against a table which has a unique index instead of a primary key.
i get this error:
Error SQL: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:
or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)
emp_id number, name varchar2(30), from_dt date, remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark'); insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
