SQL & PL/SQL :: Displaying Record When All Columns Have Matching Records?
Jul 3, 2012
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
View 8 Replies
ADVERTISEMENT
Nov 16, 2012
create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...
Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like
CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234
View 11 Replies
View Related
Jul 26, 2011
I developed a report in 10g which displays more than 100 records.The requirement is to display each record on single page.Does Oracle Reports have the option to display each on single page?
View 7 Replies
View Related
Mar 27, 2013
In PL/SQL, the following sql causes an error when there is no record matching the search criteria:
-----
select pin_number from table1 where id = 1;
-----
error: No Data Available
Is there a way to get a return value of 0 whenever there is no matching record?
View 3 Replies
View Related
Mar 15, 2010
I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48
Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72
this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
View 4 Replies
View Related
Aug 10, 2011
I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".
which is the best option to make most efficient search --
1) rownum < 201
2) row_num()
3) rank/denserank etc
View 3 Replies
View Related
Dec 19, 2012
I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc ,
For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.
View 7 Replies
View Related
Oct 20, 2012
I have table having below records.
empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30
Normal Output
deptno count(*)
-----------------
10 2
20 2
30 1
I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1
View 1 Replies
View Related
Nov 22, 2010
I am able to get a list of all the duplicate location_code values.
select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.
select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code
View 4 Replies
View Related
Oct 10, 2013
My data is like this.
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTAmountFor_qtrFor_year237898270KirtikarWESTEnterprise100000Q1 2012237898720KirtikarWESTEnterprise310000Q12013237898720KirtikarWESTEnterprise400000Q22013237898720KirtikarWESTEnterprise2500000Q32013237898720KirtikarWESTEnterprise520000Q42014
Required Output:
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTQ1_AMTQ2_AMTQ3_AMTQ4_AMTYear237898720KirtikarWESTEnterprise31000040000025000005200002013-2014237898720Kirti
[code]......
View 11 Replies
View Related
Mar 16, 2011
displaying an alter message after inserting a record into the database...i have designed form through forms builder.i want 2 display a alert message for showing a message 'RECORD INSERTED SUCCESFULLY ' along with the value entered in the textitem.this is the code i used
declare
d varchar2(30);
begin
go_block('block3');
insert into patient(ptno,ptname,dob,age,ptel,blood,address)values(:block3.item4,:block3.item
5,:block3.item6,:block3.item7,:block3.item8,:block3.item9,:block3.item10);
commit_form;
set_alert_property('alert2',alert_message_text,'RECORD INSERTED');
d:=show_alert('alert2');
end;
BUT I NEED TO SHOW THE VALUE FROM THE TEXT ITEM INTO MY ALERT MESSAGE..
View 2 Replies
View Related
Feb 12, 2012
I want to ask what's wrong with my multi-record block.
When i click a node on my tree, it should display the records on it. For example, my root node is Sections, the parents are 1st sem and 2nd sem. In each sections in 1st sem, whenever i click a child node, it should display on display items. I have set its number of items displayed to 9. Since each sections have 9 subjects. The problem when i run it is that it displays many rows.
View 35 Replies
View Related
Jul 8, 2012
In form i call a record from database table through execute_query. i have lov on ITEM_NO
i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.
if i press the down arrow it should show next record of the same item_no.
View 5 Replies
View Related
Apr 11, 2013
I have a view that is displaying records that are almost duplicates with the exception on one column.
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 B 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
DENVER Steve 222 D 11/24/2012 9:00
I have a situation where I need to only display one status per name at a specific time.I would like to return the following:
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
So I decided to include a rank for the different statuses to identify the highest rank status and eliminate the lowest rank status records I don't want.
Select distric,name,id,status,
case status
when 'A' then 1
when 'B' then 2
when 'C' then 3
else 4
end statusrank,
logtime
from tst;
[code]...
I would like to return the record with the highest statusrank when they duplicates with the exception of the status column.Is there a built in function I can use for this ? The statusrank can be returned in query. My final return would be:
Distric name id status statusrank logtime
------- --------- ---- ------ ---------- ---------------
ATLANTA John 007 A 1 4/5/2011 8:20
ATLANTA John 007 D 4 10/5/2012 8:20
JERSY C. Eric 111 C 3 8/1/2012 4:45
PLANO Jake 522 A 1 1/2/2012 7:22
DENVER Steve 222 C 3 11/24/2012 9:00
View 7 Replies
View Related
Feb 14, 2012
I have for example two text items(number of records to display is set to 9). What I want is when i go to studentprofile block, it should automatically shows his subjects that are already taken up. the first text item is for 1st semester subjects and the other for 2nd semester.
here is the structure of my subjsec table:
ID number(1)
subj_code varchar2(10)
day varchar2(10)
and for subjects table:
subj_code varchar2(10)
description varchar2(50)
units number(1)
semester varchar2(10)
I restricted subjects that are in 1st sem. So i did something like this in my
DECLARE
CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, g.subj_code, s.description, s.units, s.semester
FROM studgrades g , subjects s
WHERE g.subj_code = s.subj_code
AND g.stud_id = :Studentprofile.student_id;
[Code]....
View 32 Replies
View Related
Mar 17, 2012
i want to display records from a table in tabular form with one condition.
i made a tabular form.and in WHEN-NEW-FORM-INSTANCE i write
DECLARE
CURSOR hascr IS
SELECT
BILL_ID,
BILL_DATE,
MAT_CODE,
PLACE_FROM,
[code]....
when i run the form its not showing any errors but displaying only one record.that is the first record whose bill_type is send to other store.
am i using the right trigger or my select statement has problem?
View 9 Replies
View Related
Aug 27, 2010
After setting up a data entry page,The logic in my page requires that all the records in my tabular form should be displayed .The tabular form is based on a collection, and the user selects rows through a checkbox.Any reload of the page due to pagination will break the logic, as some calculation and display events occur during page load, based on the previous page.
I need to display about 25 rows.I have changed the report attribute to display 50 rows, but it displays rows only up to the bottom of the screen, i.e. 11 rows.
Unfortunately I only just added some new rows and saw this behaviour else I would not have gone that way, as I expected APEX to display all rows if required.
View 19 Replies
View Related
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Nov 29, 2012
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
View 1 Replies
View Related
Feb 8, 2013
I want to learn how to make columns out of records?
View 8 Replies
View Related
Oct 8, 2010
I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
View 31 Replies
View Related
Nov 25, 2011
if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
View 4 Replies
View Related
May 31, 2010
I would like to UPDATE the columns p1 and p2 of my table student (studentid:pk,name,p1,p2,...) for a given studentid.and I have a when-button-pressed trigger with this
UPDATE student
SET student.p1=:validation.proj1,
student.p2=:validation.proj2
where UPPER(student.studentid)=UPPER(:validation.studentid);commit_form;
when I run my form with a correct studentid, I got this error: FRM-40508: ORACLE error: UNABLE to INSERT record
but it is cworking correctly in sqlplus; and I have all priveligies.
View 4 Replies
View Related
Dec 16, 2011
I am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
View 2 Replies
View Related
May 2, 2012
I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?
Let's consider an example with 5 records:
Table data:
CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/
[Code]...
So when we check the table, we get:
SELECT * FROM enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
What I would like to have when I run the select query:
SELECT * from enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.
View 6 Replies
View Related
Jun 15, 2012
I have Leave Balances In such Format
EMP_COD CategoryJON_DTE LEV_BAL
3900000385101/02/200760
3900002678107/01/200840
Now I want to Separate it into Three and Two Partitions Year Wise.
Like this
3900000385101/02/200720 2012
3900000385101/02/200720 2011
3900000385101/02/200720 2010
3900002678107/01/200820 2012
3900002678107/01/200820 2011
View 7 Replies
View Related
Dec 18, 2008
I am using toad to output the details of the database. (I need these details in a format which i can then import into excel) However, When I run sporadic individual records through my query, the results come back correct and as expected. But when I run the full data set through my query a lot of the records have different values than what they do when run individually!
Ive spent all day on this script and its really a hack of various other cursors and scripts brought together. Its driving me mad as my code doesn't seem to be wrong (since the results are correct when a single record is run) yet there must be something wrong as my full data set does not return correctly!
View 2 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Jun 29, 2011
I am working on forms 6i.
I have datablock 'EMP', it's multi record block. I have a button 'CREATE', I have 'commit_form' in 'When button pressed' trigger, to commit records. It's working fine.
Now, i have a check box in the same block, i want to insert records which are checked only.
View 7 Replies
View Related
Jul 13, 2010
I have created one tab canvased form
On first tab I am taking input (search criteria)
on that input i am trying to display all records one by one as user click on next record button....
for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.
View 1 Replies
View Related