SQL & PL/SQL :: Unique Values - Print Output Of Table
Dec 9, 2011
I am stuck with this query.
I have a table "xyz" as -->>
SQL> select * from xyz;
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.
Either of SQL or PLSQL would d for me.
View 11 Replies
ADVERTISEMENT
Mar 24, 2011
I have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
View 1 Replies
View Related
Jul 1, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
View 2 Replies
View Related
Jan 2, 2013
I am getting an error while printing a sql query which is in a string inside a stored proc. I used
dbms_output.enable(1000000);
dbms_output.put_line(v_query);
and error "I get ORU-10028: line length overflow, limit of 255 bytes per line"
How do I print everything?
View 3 Replies
View Related
Aug 4, 2010
Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.
View 10 Replies
View Related
Jan 26, 2012
create table a_user
(
u_name varchar2(30)
, grp varchar2(30)
)
;
INSERT INTO a_user VALUES ('abc', 'new');
[code].....
sql:
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
[code].....
getgrpname is a function to get grp when I input the u_name
Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:
create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
a.grp
[code].....
how to print output in SQL developer
View 2 Replies
View Related
Jan 24, 2012
When i run the below mentioned query(similar query)on TOAD. It will run without any errors and i can see "PL/SQL Procedure successfully completed" in the task bar.....
However i am unable to see the output for the query.
print the the query output on TOAD ....
**** Sample Query Starts *****
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;
--print i;
--dbms_output.put_line('i1: ' || i);
END;
**** Sample Query Ends*****
**** Actual Query Starts *****
-----------------------------------------------------------------
DECLARE
UnxDate number(6);
MyResult number(6);
Todaysdate date := TO_DATE('17-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
BEGIN
SELECT ROUND (Todaysdate - TO_DATE('01-jan-1970','dd-mon-yyyy') ) INTO UnxDate FROM dual;
[code]......
View 9 Replies
View Related
Apr 19, 2007
In Sales Order Form there is a print receipt button. In current situation On Clicking the 'Print Receipt Button' it submits a XML Publisher report(PDF output) to the concurrent manager. Currently to print that report i need to go view--> requests--> view output and then print. According to my new Requirement i need to print that report directly to local printer on one simple Print receipt button click in form.
I am trying to call the printers on server using custom.pll. The report is running successfully but it is not printing output to the local printer. I need to print the report to the local printers based on responsibility. Local printers are available on apps server.
Is it possible to print the document using custom.pll? Is there any other alternative to print the report on simple button click in form.
I searched many forums but i found customizations on form level. I am trying to customize the form using custom.pll. I found many examples like URL..... but i don't think i can use these practices in custom.pll. ADD_PARAMETER & RUN_PRODUCT dont work in custom.pll. I need to complete this requirement immediately.
Environment: Apps 11.5.10.2, Forms 6i
Here is the code that i am using in my custom.pll.
if (v_form_name = 'OEXOETEL' --and v_block_name = 'LINES_SUMMARY'
and name_in('parameter.ACTIONS') = 'PAYMENT_RECEIPT' )THEN
l_organization_id := Name_In('PARAMETER.OE_ORGANIZATION_ID');
l_order_header_id := Name_In('ORDER.header_id');
select to_number(oe_sys_parameters.value ('SET_OF_BOOKS_ID')) into l_sob_id from dual;
xml_layout := FND_REQUEST.ADD_LAYOUT('XXAFP','XXAFPOEXPMTRCRTF','en','US','PDF');
[code]....
View 2 Replies
View Related
Aug 21, 2013
I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.
View 44 Replies
View Related
Nov 6, 2012
I've 2 identical tables of 300+ columns each,and each of table is having pk as combination of 2 fields.There are some fields where data is not matching. need a query which will use these two tables and return the following-
1. primary key columns
2. null value for fields where data is matching
3. data for columns where there is data mismatch.
View 4 Replies
View Related
Apr 9, 2012
I need a query to get the below.
Source :
select * from test;
LVL
1
2
3
1
2
3
4
Output:
LVLSEQ
11
21
31
12
22
32
42
I need above to uniquely identify the set of data.
View 25 Replies
View Related
Feb 5, 2013
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.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
View 6 Replies
View Related
May 30, 2013
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).
View 3 Replies
View Related
May 8, 2012
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?
View 9 Replies
View Related
Jun 6, 2013
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.
View 12 Replies
View Related
Aug 26, 2011
I have a problem with some tables in database.
Table has three columns: userid, pname, pvalue.
Userid has unique values, for example: 234, 123, 587, etc.
In the field pname, there is three possible values: MAC, IP, S/N.
So if I go like this:
select pvalue
from table
where pname = 'MAC';
i get the values of MAC.
If I go like this:
select pvalue
from table
where pname = 'IP';
i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.
View 11 Replies
View Related
Nov 29, 2012
I have a list sample:
Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA
[code]....
What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:
Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE
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.
View 1 Replies
View Related
Apr 17, 2013
i am having a table with out pk along with data.
Now , i need to add one column to that table , and update this column with the sequence no
like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.
how can i do.
View 1 Replies
View Related
Jul 5, 2013
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]....
View 2 Replies
View Related
Feb 19, 2011
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).
View 1 Replies
View Related
Mar 10, 2013
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.
View 2 Replies
View Related
Jul 5, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
BEGIN
JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W');
UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert');
FOR REC IN JOBSCUR
[code]....
is there anyway to print the values of full table .i have used all column names to print .
View 6 Replies
View Related
Feb 11, 2011
Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.
View 8 Replies
View Related
Aug 24, 2012
Oracle Database Version is 10g ( 10.2.0.4.0)
I have two table emp_perm & employee.
1) Table emp_perm is a lookup table with 2 columns, temp VARCHAR2 & perm VARCHAR2.
Table emp_perm have below data in it:-
temp perm
1064885 0349034
0099982 7399982
6476456 9170346
5252525 52525252)
Now table employee might have following combinations of rows.
Records for both Temps file (1064885) and perm file number (0349034) does exist in employee table, in this case Delete Temp File Number records i.e. 1064885 and keep Permanent File Number (0349034) records as it is.
Records for only temp file number(0099982) does exists and corresponding perm file(7399982) does not exists in employee table in this case convert these records i.e. update 0099982 with its perm file number 7399982 using lookup table emp_perm.
Records for only perm file number exists (9170346) and corresponding temp file number (6476456) does not then do not amend employee table, as we are interested in perm file numbers.
If file number does exist in lookup table in emp_perm and both of them are equal (5252525) then do not amend employee table as temp and per file number are same.
View 5 Replies
View Related
Feb 3, 2012
What I am trying to do is print out page that displays all of the column titles and the data under them for a query given by the user. It is then going to be put into an excel spreadsheet.
I've done this before with Java, simply by using the getMetaData function, but I can't seem to find an alternative for PL/SQL. It seems at the very least I need to know the number of columns in a query, but that would defeat the purpose of this.
Is what I am trying to do even possible or is knowing the column names an absolute necessity when printing table data?
View 9 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Aug 2, 2011
I have created a table below, my TL asked me to create a local unique constraint for the below table.
I went through all sites and could not find the correct solution, how to create LOCAL UNIQUE CONSTRAINT ON SUB PARTITION TABLE and LOCAL UNIQUE INDEX ON PARTITION TABLE. Creating Local Unique constraint should take care of creating local unique index creation.
Unique key columns are DET,GDS,ARRIVE_DT
CREATE TABLE SUB_PAR_TAB
(
ID VARCHAR2(100) NOT NULL,
REGION VARCHAR2(40) NOT NULL,
SOURCE VARCHAR2(80) NOT NULL,
DET VARCHAR2(80) NOT NULL,
GDS VARCHAR2(40) NOT NULL,
ARRIVE_DTDATE,
SYS_SOURCE VARCHAR2(25) ,
[code]........
View 5 Replies
View Related
Sep 13, 2013
I have a table with a non-unique index consisting of three columns. The first column is not null while the remaining two are nullable. Queries using this index will chiefly be made in two ways.
1. Column one and two having values. Column three is null.
2. Column one and thre having values. Column two is null.
In both cases I expect range scan will be used since it's non-unique. In the first case the scan will be on values in column one and two. But what happens in case two. Will the range be on colum one, column two(being null) and cxolumn three? Or will it be on just column one since the second column is null? I have done some testing. I can see , using EXPLAN PLAN, that range scan is used in both cases. how the index is used?
Is there any other drawbacks with an index like this?
View 13 Replies
View Related
Aug 5, 2013
Create a function which will indicate if a given record in a table is unique or not. Unique means the data is occurring only once in the entire table.
Function should be in this signature
function IS_UNIQUE (tableName in varchar2,tableAttribute in varchar2)
return number
.....
begin
//logic to check if given data is unique
return 0; //return 0 if data is unique
else
return 1; //return 1 if data is duplicate
end;
Once I run this query
select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=0
All records of attribute1 which are unique need to be fetched. Similarly, select attribute1 from table1 where IS_UNIQUE(table1,attribute1)=1 should return all records of attribute1 which are duplicates.
View 13 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