I have a table emp where empid,employee name,job,salary are three columns.I need to retrieve empid,count of emp,emp name,emp salary of job = manager and total salary of all employees with grouping by job in one query.
View 13 RepliesCan we search for a column name if we have some value with us, and we are not sure which column it belongs to.
View 3 Replies View RelatedI am trying to find a value in any column of any table ? How can i do that ? A value example would be 'FEDERAL' Using Oracle 11g and SQL Developer.
View 6 Replies View Relatedcan we make a search based on column , actually i have one requirement where the data is stored in column as comma seperated values like below
arif,123455,123456,77777
sait,123455,123456,66666
tomm,666666,123455,123456
I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below
create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000));
insert into prd_mast values ('1','arif,123455,123456,77777');
insert into prd_mast values ('2','sait,123455,123456,66666');
[Code]....
in my databse i have column name as email_id
in this column email id of cutomers are stored but in some emaild contains space
i want to find out such email id
I have one table for eg. TB_Fruits.
In that i have one column FruitsName(Varchar)
In that column i am storing string in comma separated values.
Select FruitsName from tb_fruits;
Result: orange,banana,apple
Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.
Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')
the orange,banana,apple can be in any position.
How to check if any of these names already exist or not in the column fruitsname?
I cannot use like or INstr function here. because the position is not fixed not even string.
in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function
CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;
[code]....
but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?
I have a column in a table say ename in emp table, which allows values with newlines and carriage return.
I need to find all the values (i.e ename's) which are having newline chars or carriage return.
I want to select the view names from text column in dba_views.
View 7 Replies View Related have a performance problem with a simple query, for example:
SELECT *
FROM CA_ADDRESS
WHERE address_k1 = 'L163'
I have an index created in column address_k1. CA_ADDRESS is a 3-4 Million Record table.
I need to improve the performance of the query. How I can improve it?
I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.
View 1 Replies View Related(1.) We have an Oracle 10g DB.
(2.) We want to store PDFs/MS Word docs inside BLOB column in a table.
(3.) We need to show PDF/MS Word content to the user, without opening the PDFs/MS World like we normally do (i.e. Using PDV reader/ MS Word).
(4.) i.e. We need to use SQL statements and
(a.) Get all contents of the PDF/MS WORD doc.
(b.) Search for specific strings inside these docs in the BLOB columns.
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
I have created a IR report and I set the default search column using the suggestion found in the thread URL....but how can I keep this default even after the research was done?Now it is resetted !
View 1 Replies View RelatedI have the following query:
SELECT AGENCY, COUNT(*)
FROM (SELECT A.AGENCY,SUM(A.NUM_LOGIN)
FROM GOVAGENCY A
WHERE AGENCY = 'DOD1'
GROUP BY A.AGENCY
[code].......
The results are:
AGECNY COUNT(*)
-------- -----------
DOD1 1
DOD2 1
2
The rollup value has a blank label instead I want to have a label to the rollup result also ex:
AGENCY COUNT(*)
--------- -----------
DOD1 1
DOD2 1
DEPT. OF DEFENSE 2
I am new to PL/SQL environment.. ( USING PL/SQL Oracle)
SELECT TABLE1.PRODUCTID, TABLE1.TYPE , TABLE1.PRODUCT, TABLE2.#SOLD
FROM TR.TABLE1
INNER JOIN
PR.TABLE2
ON (TR. PRODUCTID = PRODUCTID )
Query result.
Product ID Product Type #Sold
1 Fruit Banana 2
2 Food Rice 4
3 Food Sugar 6
4 Fruit Orange 2
5 Fruit Apple 10
6 Food Sugar 12
7 Food Corn 3
8 Fruit Apple 5
9 Fruit Banana 2
10 Food Sugar 1
11 Fruit Banana 5
12 Food Sugar 7
But would like to add Four More Column and group by by PRODUCT.Final Result will look like :
{Product} , {Type} , {#Sold} , 0 to 3 , 4 to 9 , >10 Total Sold
, {Corn} , 3 , , ,
, {Rice} , , 4 , ,
, {Sugar} , , , , 26
{Food} , , , , , 33
, {Apple} , , , , 15
, {Banana} , , , 9 ,
, {Orange} , , 2 , ,
{Fruit} , , , , , 36 }
getting expression into a Group By query in oracle.
I have a simple table with two columns. 'ID' and 'Amount'
I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.
select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID
This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).
earlier the query was running on same record group but i hav to delete some code so i did but after made such changes itreturns this error.but still query is correct i complied on toad.
View 10 Replies View RelatedI found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.
View 11 Replies View RelatedI'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?
View 3 Replies View RelatedI'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables
View 1 Replies View RelatedI am using oracle 10g 10.1.0.2.0 . I want to set lov search value means when we open lov then in search text box it show % but i want to set 1% .
View 5 Replies View Relatedi have a text field search_part1 in which i put the item for search another text field i send_by which contains items a single item comes in list many times.i have to find the item one by one.i have make this statement for seraching items
Declare
typ varchar2(200);
L_NAME VARCHAR2(50) := :BLOCK3.SEARCH_PART1;
begin
go_block('BLOCK3');
first_record;
loop
if :BLOCK3.SEND_BY = L_NAME then
[code]....
bt the problem is that if a single items comes more than one time then it search only one time and next time it exit.
I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.
how to do this interactively, without preparing a SQL script with all exisiting tables?
I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.
I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.
I can not work on stored proc, as I do not have access to create that on server. So, any script will work.