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

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

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

Reports & Discoverer :: Displaying One Record Per Page For All Retrieved Records

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

PL/SQL :: Get A Return Value Of 0 Whenever There Is No Matching Record?

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

Oracle SQL - Update Table Value By Matching Columns

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

Any 200 Matching Records From A Table?

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

SQL & PL/SQL :: Group By Matching Records

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

Displaying Rows Into Columns In A Table

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

SQL & PL/SQL :: Displaying All Other Columns If Column Is A Dupe

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

PL/SQL :: Displaying Records In SQL

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

Displaying Alter Message After Inserting Record Into Database

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

Forms :: Multi Record Block Not Displaying Items

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

Forms :: Displaying Number Of Records With Condition

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

SQL & PL/SQL :: Displaying Records That Are Almost Duplicates With Exception On One Column

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

Forms :: Records Not Displaying In Specific Text Item

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

Forms :: Displaying Records From A Table In Tabular Form With One Condition?

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

Application Express :: Displaying All Records In A Tabular Form On Single Page

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

Displaying Records Between Specific Times On Specific Dates?

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

Avoid Updating Record If Columns Do Not Change?

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

SQL & PL/SQL :: How To Make Columns Out Of Records

Feb 8, 2013

I want to learn how to make columns out of records?

View 8 Replies View Related

SQL & PL/SQL :: To Compare Records In Two Columns

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

SQL & PL/SQL :: Finding Record In Table Having Value For Maximum Number Of Columns

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

Forms :: INSERT Record - Update Columns Of Table

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

SQL & PL/SQL :: Max Date Using Selected Columns - Millions Of Records

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

SQL & PL/SQL :: Inserting New Record In Between List Of Records

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

SQL & PL/SQL :: How To Partition Record Into Multiple Records

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

Different Results When Run Through All Records Compared To Running A Single Record?

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

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

Forms :: How To Insert Records Checked In Multi Record Block

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

Forms :: Display All Records One By One As User Click On Next Record Button

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







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