SQL & PL/SQL :: How To Show Unique Record Only In Query
Jun 20, 2012
I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
View 11 Replies
ADVERTISEMENT
Sep 24, 2012
I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
View 7 Replies
View Related
Apr 9, 2013
I have following records in table (T)
Idtype_cdNbrDt
1A1001/1/2013
2A101Null
3B100Null
4C100Null
5A100Null
Based on the type-cd, I need to create Unique index on Nbr & Dt columns
In above record set, I want to create Unique index on type_cd=A and not on B&C (Here i expect duplictes for a combination of Nbr & Dt)
Can we create Unique index on subset of record?
View 1 Replies
View Related
Sep 25, 2012
I have a table
TAB1 with data
no name
1 abc
2 abc
3 xyz
4 xyz
5 cvb
now I would like to create a form with name counts as
abc 2
xyz 2
cvb 1
I would like to create a datablock to display all the unique names, not sure where I should be writing the query to display unique names. I would like to create a textitem box to display the counts when the form is compiled, but not sure which trigger to use to write the query.
View 2 Replies
View Related
Aug 30, 2010
Using Forms 10g and Webutil. I am uploading my file using the following code. The Status shows file upload is Successful.. but NO Record exists in the Table...!
Here is my Table Structure:-
CREATE TABLE ST_ATTACHMENT_ACCOUNT
( ACCOUNT_CDE NUMBER(8,0) NOT NULL,
FILE_NAME VARCHAR2(100),
FILE_BLOB BLOB)
TABLESPACE PRODTABLES;
Here is my -
PROCEDURE UPLOAD_DB IS
lb_success boolean;
ls_full_filename varchar2(200);
ls_filenm varchar2(100);
BEGIN
[Code]...
It shows me the progress bar for upload also but I cannot see any record on the table.
SELECT dbms_lob.getlength(FILE_blob)
FROM ST_ATTACHMENT_ACCOUNT
WHERE ACCOUNT_CDE = 2;
0 rows selected... what is wrong...?
View 8 Replies
View Related
Mar 13, 2013
I have a multi-record block with several text items.
On one of the text items i want to enter a value and then check whether the same value for the item has already been entered on any other records within the block.
If it has already been entered then i want to display a message and null out the field.
I have tried using app_record.for_All_records from a when-validat-item trigger but this does not work as you get a 'FRM-40737 Illegal restricted procedure go_block in when-validate-item trigger ' error
View 17 Replies
View Related
May 10, 2011
I have the search screen in my form so after searching if i select the row by using the button it will navigate the first tab page that is "gas" screen here if i tried to change the value like update and save the form it is not allowing me to update the value raising the error message "oracle unable to insert the record". if i see the "display error" in menu it is having the select statement with error "unique key violation error ora-00001".
In save button containe "commit_form"
View 15 Replies
View Related
Jun 30, 2011
When i create report in master detail. When detail record end on the page, the second record show on same page. Problem is that detail show on other page. I want that if record is completed on that page then ok. Otherwise it show on next page.
View 3 Replies
View Related
Nov 11, 2010
I want to show 1 record per page in matrix report. I set Main section orientation as landscape and width is 14. but it show multiple records on single page.
View 2 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
Feb 7, 2012
SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
View 14 Replies
View Related
Jun 21, 2011
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
View 2 Replies
View Related
Sep 12, 2010
I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies
View Related
Feb 23, 2010
I have a table that has three columns.
UserId - DateStamp - Action
An action contains user activities such as "logon". A user may logon multiple times in a day.I'm trying to create a query that displays the number of unique logons for each user per day.
By doing the following:
select userid, to_char(creationtimestamp,'YYYY-MM-DD'), count(*)
from useraction
where action = 'logon'
group by to_char(creationtimestamp,'YYYY-MM-DD'), userid
order by to_char(creationtimestamp,'YYYY-MM-DD') desc;
I can get the following results
userid - date - count
1 - 2/21/2010 - 8
1 - 2/22/2010 - 3
2 - 2/22/2010 - 2
1 - 2/23/2010 - 1
4 - 2/23/2010 - 6
6 - 2/23/2010 - 1
What I'm trying to get is
2/21/2010 - 1
2/22/2010 - 2
2/22/2010 - 3
View 2 Replies
View Related
Jul 12, 2012
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
View 2 Replies
View Related
Apr 23, 2010
i got problem that i want to know Metadata in my Oracle DB 2 version there're OracleDB 9i and OracleDB 10g ,So i want to know about sql command to show all metadata of these Database, because i want to list My metadata and improve to Metadata's Standard(ISO/IEC 11179). How can i show all metadata of these database. is it the same sql command line with this 2 version Database to show all metadata?
View 1 Replies
View Related
Jul 20, 2011
me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
View 1 Replies
View Related
Jan 29, 2013
I have such a database "ZOO" in oracle PL/SQL. URL.....The query is "Show all the keepers, who cares of all animals in the zoo.
View 14 Replies
View Related
Sep 16, 2013
i am trying to use the query builder in my 11g xe database but vainly ,there are no results shown ?
View 6 Replies
View Related
Jul 12, 2004
I'm trying to create a query which will show sales of products by month, something like this: -
JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015
The data is held on 3 tables: -
TB_ARTICLES
ID
CODE
DESCRIPTION
TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE
[code]....
Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.
View 12 Replies
View Related
Sep 5, 2013
the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.
CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));
INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');
[Code] .....
With the sample data there are 4 groups based in c1 column:
1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7
I'm trying to get and output like :
A1,A2,A3 : 1,3
A1,A2,A4 : 2,4
I was trying something like :
select t1.c1 as gr1, t2.c1 as gr2, t1.c2
from test_data t1,test_data t2
where t1.c1<>t2.c1 and t1.c2=t2.c2
and
(select count(*) from test_data t3 where t3.c1=t1.c1)=
(select count(*) from test_data t4 where t4.c1=t2.c1)
order by 1 asc, 2 asc
but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.
View 4 Replies
View Related
Jun 26, 2012
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies
View Related
Aug 14, 2013
Using Oracle 11g, below is the table, partitions, unique and non-unique local index:
CREATE TABLE DOCA( DOCA_ID NUMBER NOT NULL , DOCA_BKG_PAX_ID NUMBER NULL , ROW_PURGE_DATE DATE NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))( PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')), PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),) TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX XPKDOCA ON DOCA( DOCA_ID ASC, ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0; ALTER TABLE DOCA ADD CONSTRAINT XPKDOCA PRIMARY KEY (DOCA_ID); CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA( DOCA_BKG_PAX_ID ASC)LOCALREVERSETABLESPACE I0;
I would like to know the difference between the performance of the unique and non-unique local indexes?.
View 10 Replies
View Related
Mar 6, 2011
I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.
p.s: That record group is built in run-time.
View 1 Replies
View Related
Apr 3, 2010
How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.
the record group exists and I must get its query not make it.
and I know about Populate_Group_With_Query for changing but cant I have the query to change?
FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;
and I use it in an LOv.
Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?
View 2 Replies
View Related
Apr 2, 2012
I am in the need of using a table type object in SQL query.
I have a package which has a spec in which I have declared :
TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;
In one of the package procedures I am collecting data into the above indicated table type object (TTR):
SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;
This works fine. The values get collected into TTR and am able to print them too.
But when I :
SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;
in the same procedure immediately after the collection I get the error while running the procedure :
ORA-21700 : Object does not exist or is marked for delete.
View 5 Replies
View Related
Aug 18, 2013
We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL
View 15 Replies
View Related
Mar 13, 2013
I am trying to get the last 7 days of record from today date, this query runs every night and I always want the last 7 days. Example - today is 3/13/2013 so I want record from 3/7/2013 to 3/13/2013 and tomorrow it would be 3/8/2013 to 3/14/2013
Here is my query, dont mind the ****,$$$ or ####
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, [code]..........
View 16 Replies
View Related
Oct 10, 2013
I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.
update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";
I understand that problem is there with inner select query.
View 3 Replies
View Related
Jan 31, 2013
SELECT COUNT(*)
INTO v_ctr
FROM leave_type_govt_service
WHERE (
:block_name.min BETWEEN minimum AND maximum
[code]......
This code in my forms really works for inserting a new record. But if I will update the record itself for example, in the table 'Type 1'
Minimum Maximum
1 5
6 10
11 15
and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
View 1 Replies
View Related
