SQL & PL/SQL :: Derive Query To Find EmpID Linked To Only DeptID 101?
Oct 20, 2011
I want to derive a query to find the Empid's that are linked to only deptid 101 .
Empid DeptId
1 101
2 102
2 101
3 101
5 103
ie, in the above table empid 2 is linked with deptId's 102 and 101 and empid 3 and empid 1 is linked only with deptid. So i want the query which fetches only empid's 3 and 1 and should not fetch 2 as it is linked with deptid 102 also.
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal 023 | John | 6000 143 | Tom | 9000 876 | Chi | 4000 987 | Alen | 7800
The code was originally an MS Access Query with linked tables to the Oracle DB however trying to remove the need for access and have it drop directly into Excel using Recordset.
SELECT SWPRO.CASE_INFORMATION.CASEDESC, SWPRO.OUTSTANDING_MAIL.CASENUM, CASE_DATA_1.FIELD_VALUE, SWPRO.OUTSTANDING_MAIL.SENTDATE, SWPRO.OUTSTANDING_MAIL.DEADDATE FROM
I have to build something like an export-interface-mappingtool. So user defines more or less a query and I have to display the attributes of this query to enable a mapping.
If I build up a ref cursor fom this query, I did not find the possibility to read its metadata (mostly Type/length is enough).
What I do not want to do is creating a temp. table or parse the query string to derive it from the dictionary.
Is it possible at all to get the metadata of a dynamic SQL ?
I am doing a report to show current balance, aging (30-60), (60-90), (90-120) and 120+ and a combination of all of these should be the total balance overdue.
If i use a formula cclumn in the report builder, how should i write this query to calculate the total?
i just posted another topic where i heard about external table and i had a few questions concerning them. I thought it was best to create a new topic than to continue on the other one...
I noticed that to create an external table the CTL is like this: CREATE TABLE emp_load (FIELDS description) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS (employee_number CHAR(2),
[Code]...
1) This creates an external table, but, is it possible to Create a normal table in a CTL file? For physical tables, the table has to exist right?
2) if you create a view linked to 2 external tables and if the CSV files are updated each day, the external tables will be updated automatically, and the view will be updated as well?
3) Can't there be any synchronisation problems?
4) What happens if a select request (or someone requests on the view) while the CSV file is being updated?
5) Is there anyway you can protect the accesses from those tables/views when the CSVs are being updated?
6) Is it possible to create an index on these sort of tables?
7) Is it possible to index a view?
8) Are external tables visible on a tool like sql developper?
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.
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
I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._
Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.
*1, Using OPENQUERY:* SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
*2, Using Exec:* DECLARE @sql NVARCHAR(MAX); SET @sql =(’Select * from OracleTableName ‘); EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
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
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
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.
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.
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.
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.
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))
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.
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.
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
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
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
