I am trying to find out the difference in time between peoples memberships and also the order that these memberships are taken out in. So far I have added in a rank statement to work out the order the memberships were created in, but now want to look at the difference between the dates returned. The SQL I used is:
SELECT owner_party_id, mem_number, support_id, mem_start_date, RANK() OVER (PARTITION BY owner_party_id ORDER BY mem_start_date ASC) MEMBERSHIP_SEQUENCE FROM membership_all WHERE version_type = 'CUR' AND owner_party_id IN ('65051', '65051', '65348', '65348', '65607', '65607', '65607')
I think that I need to use the Lag function in, but I am not too sure if it can be linked to look at the data within a grouping of owner party id, as it would make no sense to calculate the difference in dates for two different owner party ids.
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query: return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS c_ref VARCHAR2(32500) := null ; CURSOR c_refer IS
I need a query to retrieve a particular string from all the columns and all tables in a specified schema.
For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.
CREATE TABLE MAT (matrl varchar2(100), date_man date, weight number(10) );
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name, ------ ------ ---------- ------------------------- -----------
# of records not null # of records null ---------------------- --------------------
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
I would like to have a query which should fetch previous day records from column which is having timestamp data type.
select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;
But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.
Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.
I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
Table Two TableTwoId Name 1 Jones 2 Smith 3 Edwards 4 Camden
My SQL to fetch all records with Smith works great:
select Name from TableTwo Inner Join TableOne on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo) where Name = 'Smith'
Now I need to create an alias for the Name field. Here is my attempt:
select myAliasName from TableTwo Inner Join TableOne on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo), (select Name as myAliasName from TableTwo) where myAliasName = 'Smith'
This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?
DIDDOB_INFO(VARCHAR2) 34425 Mar 1967 123 12 MAR 1963;25 FEB 1974;25 AUG 1978 46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan : SELECT FIRST_NAME FROM CAMPA.TABLE_A WHERE NAME_ID = (SELECT NAME_ID FROM CAMPA.TABLE_B WHERE ban=:b1); [code]....
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
