i have master-detail form.in master my bill_id gets generated when new form is open and i copy the same bill_id in detail(tabular)for each item.all the items which i enter in detail form get save the same bill_id which got generated.
in another form(which is tabular) i want to display bill_id's from detail form.but in detail form . There are same bill_id's more than once.but i want to display those bill_id's which are more than once only once.
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
For every record which came yeterday in table ABC, I need to find if same record data does exists in historical records of the same table, and if match is find, I need to display all the occurrences.
For example Data got inserted yesterday as SQL RES : Col 1= "A" Col 2: "B" Col 3: "C" Date = Sysdate -1
then, I need to scan through Query results from the history where I can find if there exists same combination already.and if so I need to display both i.e Record form yesterday and from history.
Here is the one I am using to get the base data. with base as( select Col 1, Col 2, Col 3,A.Create_DT from A,B where A.ID = B.ID )
how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.
select distinct comp_id as compid, comp_disp_name as company, plcy_id as policyid,
19734 Save the Children 9013 GPA 19734 Save the Children 9012 GMC 20097 JMT 9486 GTL 10890 Steelco Gujarat Ltd. 9727 CAR 17330 Golden Jubilee Hotels Limited 8915 CGL 23117 NBHC 9093 GMC 17542 Heinz India 10693 Fire 19821 KSK Fabricators 10341 D&O 3769 Jones Lang Lasalle India 9199 WC 19821 KSK Fabricators 10340 WC
I have two tables Table1 Id1 Name1 1 Jack 2 Jack 3 John
Table2 ID2 Name2 NULL Jack NULL John
I would be assigning ID2 from ID1 based on name match.As Jack has 2 ids when I use the statement UPDATE TABLE2 set id2 = (select distinct ID1 from table1 where table1.name1=table2.name2);
I get an error message as select statement would return more than one row and the update statement fails completely. with the sql statement to update the ID2 as error when we have duplicate records and continue with the update for other records. like table ID2 Name2 ERROR Jack
I need to delete the duplicate values from plsql table OR move the distinct values in plsql table to other plsql table.
how can i do this ?
DECLARE TYPE alist IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; p_tbl alist; BEGIN p_tbl(1) := 'A1'; p_tbl(2) := 'B2J'; p_tbl(3) := 'A1'; [code]......
The p_tb1 table contains all the above values including duplicates. Now I need only distinct values to be copied in another plsql table of same type.
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select * from Germany t where (ufi,zc,limitid,depot) in ( select ufi,zc,limitid,depot from ( select ufi,zc,limitid,depot, count(*) n from Germany t group by ufi,zc,limitid,depot) where n<>1 )
Table contains duplicate data . Have to move data to another table. Criteria: check for duplicate values if duplicate exist move all duplicates except one to the history table. While moving to other table see if the record being moved already exists.
3456789 NIKE AERO 457899 707 CROFT GRAND RA 12345 1256789 NIKE AERO CORP 678899 707 CROFT SE GRAND RA 12345 5465455 BB SHIPPING 809708 201 SOUTH CT DESPLAINE 45434
[Code]....
FIRST 4 RECORDS ARE DUPLICATES FROM WHICH 1 RECORD GOES TO w_grp AND ONE GOES TO HISTORY TABLE. THE RECORD WHICH GOES INTO w_grp OUT OF THE DUPLICATES WILL DEPEND ON THE LAST MODIFIED DATE FOR EACH
DISTINCT VALUES GO IN w_grp TABLE DUPLICATE GO INTO match_his TABLE
id name plan code 1 sam normal 5 1 sam normal 6 1 sam special 5 1 sam Special 6
I need to delete data in such a way that one entry with normal and one entry with special plan should remain and should be with different code. Does not matter whether normal stays with 5 or 6 code.
I tried with rowid but it deletes either both normal or both special or returns same code for normal and special.
CREATE TABLE prim_tbl (id NUMBER,--- id is not primary key here description VARCHAR2(30));
INSERT ALL INTO prim_tbl VALUES (1,'aad') INTO prim_tbl VALUES (1,'aads') INTO prim_tbl VALUES (2,'bb') INTO prim_tbl VALUES (2,'cc') INTO prim_tbl VALUES (2,'dd') SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad 2, bb
I used: select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3 2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO 1, A, xyz, HELLO 1, A, xyz, BYE 1, A, xyz, GOODBYE
2, C, pqr, WELCOME 2, C, pqr, GOOD MORNING 2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3 FROM test_tab GROUP BY grp_col_1, grp_col2, grp_col3 HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3, /*some logic*/ dup_val FROM test_tab GROUP BY grp_col_1, grp_col2, grp_col3 HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y 3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement? Priview SQL statement
SELECT * FROM ( select id,hin_id,name,code,valid_date_from,valid_date_to from diaries )
[Code]....
In this case i got duplicate of entry TT2 id 50513 In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values
For example:
So when dept.deptno=10, I want to display these 2 columns values 1. dept.deptno 2. dept.dname
otherwise, display these 2 columns values 1. dept.loc 2. null
Can this be done with one case, decode or "other" type of structure going thru the table one time??
SELECT emp.empno, emp.ename, CASE WHEN dept.deptno = 10 THEN to_char(dept.deptno) [code].......
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
How I can build a query with conditions and calculations? E.g. I've got this table
Start | End | Working Place | Mandatory ------------------------------------------------------------------------------------ 01-JAN-13 | 11-JAN-13 | Office | 1 14-JAN-13 | 25-JAN-13 | Home Office | 0 04-MRZ-13| 15-MRZ-13 | Office | 0 11-FEB-13 | 22-FEB-13 | Office | 1
Now if column working place=Office and column mandatory=0 the new column "price" has to calculate: (End-Start)* $25.00 and if working place=Office and column mandatory=1 the "price" column has to calculate: (End-Start)* $20.60 else $0.00
I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".
Something like case when Working_Place = 'Office' and Mandatory=1 then ... else '0.00' end as PRICE ?????
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4 NAMEJOHN10ABC SMITH30DEF ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from ( select rownum rn, c1,c2,c3,c4 from table_new ) where rn =1 union select * from ( select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new ) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
I would like to have a table in HTML region and to display in the table some Page Items from the page.
I got the code like this: { <table> <tr><td>&P1_ECEMEA_ASSISTANCE</td></tr> <tr><td>&P1_ECEMEA_WIP</td></tr> </table> }
It shows the Page Item names as text, not the values. However this code without the table tags:
{ ECEMEA Request for CQT Assistance: &P1_ECEMEA_ASSISTANCE. ECEMEA CQT Work in Progress: &P1_ECEMEA_WIP. }
works and the Page Item values are displayed.
Is it possible to display the Page Item values in the HTML table at all or I have to use a different method of showing the values in a table? The above is only a snippet of the code. I need to display about 30 Page Items, formatted in a table with headings.
what kind of region is the best to be used or if I should only use some escape symbols in the table code.
how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare qry varchar2(5000); n number; alert number; Begin [code]....
