SQL & PL/SQL :: Query To Find Relevant Table Name

Jan 16, 2013

i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:

EMP
EMP_1
EMP_2
EMP_3
EMP_4

i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.

View 10 Replies


ADVERTISEMENT

PL/SQL :: Query To Find Table And Column Name By Using Value

Sep 3, 2013

I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are  

where owner NOT IN ('SYS','SYSTEM') and      data_type IN ('CHAR','VARCHAR2','NUMBER') 

My query as follows 

select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner

View 3 Replies View Related

SQL & PL/SQL :: Query To Find Values Are Not Present In Table

Nov 28, 2010

I have table called test script for table is given below

create table TEST
(
col1 Number
);

[Code]...

For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.

View 2 Replies View Related

Performance Tuning :: How To Find Top Query Running On A Table

Feb 19, 2012

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?

View 2 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Primary Keys Present In A Table?

May 17, 2011

is there any query to find number of primary keys present in a table.

View 13 Replies View Related

Dumping Relevant Data From Columns Of Different Tables - Report / Join

Nov 18, 2008

As the title of this topic illustrates, i'm having trouble dumping relevant data from columns of different tables. I am using isql*plus. I have three tables appropriatly related. A 'course' table, 'student' and 'next_of_kin' tables. I have many students enrolled on various courses but only a hanfull of courses offer the module option 'Database Systems'. I have no 'module' table but i know the three course names which provide the module option. I intend on producing a report hich lists all students enrolled on the courses which provid the module option 'Database Systems'.

I have attempted the report but i keep getting a 'cartesian product' displaying all next_of_kin names instead of the appropriate. Also i am struggling to come up with the right WHERE statement to depict only the three courses which provide the module option 'Database Systems' as defined by 'courseNo' in both 'course' and 'student' tables.

Here is the most recent attempt:

--set echo off
--set pagesize 24
--set feedback off
--set linesize 78
col A format 99999999 heading 'Student No'

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Query To Find Application Table Column Details Associated With Descriptive Flex Field

Feb 5, 2010

I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.

I tried the below.

/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');

/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';

There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?

I want to build a single query to fetch the application column name and flex field name for a specific table .

View 6 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

SQL & PL/SQL :: Query To Find MIN Date

Nov 5, 2010

building SQL query to get the result as shown below.

Create Table Temp

CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data

[Code]...

I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.

OUTPUT:

CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010

View 6 Replies View Related

SQL & PL/SQL :: Query To Find Usernames From Their IDs

Oct 23, 2012

the below requirement.

I have 2 tables, first one MAIN_T, which contains, user_id and username.

The second table is a relationship table which contains the referal details.

The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.

-- Test Case

CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);

INSERT INTO main_t VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');

[Code]....

I have written this query, which gives me the expected output.

SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id
AND rltn.frnd_id=frnd.user_id
/

MEMBER_NAM FRIEND_NAM
---------- ----------
u1 u2
u2 u3
u5 u4
u1 u5

4 rows selected.

a better way for this requirement.

View 2 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Empty Column?

Aug 3, 2010

I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.

I need a query that should return the column which is empty but its next and previous column is not empty.

View 13 Replies View Related

SQL & PL/SQL :: Query To Find Last 6 Month Records?

Aug 23, 2010

SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID

There is no date column in table using sysdate alone need to retrieve last 6 month records

how to use in where condition

View 13 Replies View Related

SQL & PL/SQL :: Query To Find The Number Of Alphabets?

Apr 20, 2013

I need to find out that assume i have a table having 2 column

Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani

I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets

View 6 Replies View Related

Forms :: Triggers Regarding Find Query?

Oct 24, 2013

I am working on a form, whose one block is query_find, so on clicking find button it will open another block.the find block contain 4 column and second block contain around 10 column.but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.there is a query which link those column.

I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.

View 13 Replies View Related

SQL & PL/SQL :: How To Find The Query Which Is Taking Most Time

Jul 4, 2011

I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.

View 2 Replies View Related

SQL & PL/SQL :: Analytical Query To Find First And Last Value In Date?

Jul 2, 2013

My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.

My table looks like this.

Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14

Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14

In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.

Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.

Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.

1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131

[code].....

My Query :
----------

I not getting the desired output here as the value in pseudo column is 3.

select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))

[code].....

Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);

[code].....

View 1 Replies View Related

SQL & PL/SQL :: Query To Find The Time Difference

Aug 21, 2010

I need to query a table to get the time difference based on different dates.

SAMPLE DATA:

JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12

in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as

01:23:12-00:56:00 and the difference should be in minutes.

for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.

View 2 Replies View Related

SQL & PL/SQL :: Query To Find History In Oracle 11?

Jul 29, 2011

find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?

SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,

[code]....

View 3 Replies View Related

PL/SQL :: Query To Find The Sum Of Different Groups Of Same Column

Feb 23, 2013

I have a table as follows:

customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012

I Have to write a single query to calculate the sum of credit and sum of debit value separately.

View 8 Replies View Related

SQL & PL/SQL :: Query To Find Is Employee A Manager

Feb 26, 2013

Refer the table below and provide me the query to find given employee is manager or not.

Refer the table below, emp#100 is manager, emp#300 is not a manager.

<emp> <emp_name> < manager >
100Smith null
200Ram 500
300Shankar 500
400Madhur 200
500Suman 100
600Sundar 100

View 2 Replies View Related

Query To Find Out Whether Exactly 2 Columns Are Used In Any Index

Dec 17, 2012

Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index

Ex. create index test1 on customer (ID , Status )

But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?

View 23 Replies View Related

Query To Find Current Queries Not Using Indexes?

Mar 29, 2012

hint of which DBA views we need to query to find current running queries which are not using indexex?

I suppose v$session is one , what else we need to look to?

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Nulls For Each Record

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

SQL & PL/SQL :: Find Query To Create Gap In Continuous Series?

Jan 16, 2013

I need to find a query to create a gap in continuous series. So that i can fill this gap with a new record.

Attached here is the test sql.

Sample Data is

test@orcl>select * from sales_mst;

NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N

[code]...

10 rows selected.

test@orcl>select * from sales_dtl;

NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1249 12 55 450

[code]...

16 rows selected.

Required Out Put

test@orcl>select * from sales_mst;

NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
1251 08-JAN-13 N
1250 08-JAN-13 R
1253 08-JAN-13 N
1252 09-JAN-13 R
1254 09-JAN-13 N
1255 09-JAN-13 N

10 rows selected.

test@orcl>select * from sales_dtl;

NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1251 12 55 450
1251 11 45 600

[code]...

16 rows selected.

Update or move only those records which have Type 'N'. I have tried the following query but it is giving me an error.

test@orcl>ALTER TABLE
sales_dtl
DISABLE CONSTRAINT
fk_sales_dtl_no;
test@orcl>update sales_mst set no=no+1 where type='N' and vdate > '07-JAN-13';
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated

View 15 Replies View Related

SQL & PL/SQL :: Query To Find Out Employees Who Are All Joined Before Manager

Jun 27, 2011

Query to find out employees who are all joined before Manager.

For Example the Table may look like.

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E106Manager 11-Oct-08
E107Programmer20-Nov-09
E108Coordinator12-Dec-10
E109DB Admin10-Feb-07
E110DB Analyst10-Aug-05

The out put must be..

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E109DB Admin10-Feb-07

View 4 Replies View Related

SQL & PL/SQL :: Query To Find Count Of Delimiter In Column Val?

Jun 29, 2010

Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.

Table:

CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)

Data:

INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;

Required output:

num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4

View 5 Replies View Related

PL/SQL :: How To Find Special Characters In A Single Query

Sep 12, 2012

My query is "How to find all special characters like (%$*&@,;'/+- etc. in a single query?"

e.g.

A_MIR
A%SIM
A*SIM
A)SIM

View 6 Replies View Related

PL/SQL :: Query To Find Number Of Days Across Each Month

Oct 1, 2012

We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.

We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.

We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below

Number of Working days in March is 31 - 3 + 1 = 29

Allowance A1 = (300 * 29 )/31

Number of Working days in April is 5 ( this also needs to find logical I am guess )
Allowance A2 = (300 * 5 )/30

Then A1 + A2.

The A(n) would be the total allowance where provided the number of month across.

View 10 Replies View Related

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related







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