SQL & PL/SQL :: Get Weekend Data From 2 Tables?
Mar 24, 2011
I'm having trouble to get the weekend records from 2 tables. table 1 has a start date and an End Date. Table 2 has END_INTV_TIME ( HOUR FORMAT AS 0100-1am,0200-2am.......1300-1pm,1400-2pm.....2400).
create table t (traffic_sample_id number,
site-id_nbr varchar2(12),
start_date varchar2(6),
end_date varchar2(6)
[code]....
My requirement is to get the weekend days and i tried using the function
( TO_DATE (start_date,
'mmddrr'
)
+ TRUNC (rn / 24),
'Day'
)
{code}
But the problem is its going just after the table1 START_DATE and getting me the weekend day based on start date, but when i join the tables the problem is with the END_INTV_TIME, the recods start at 1000 (10am) (bin_data_id =1037804) so according to my logic the weekend records will be 48 records from the first record, but i want to take into consideration the END_INTV_TIME as well when its (0100 1am) then my weekend starts ie(1037843 bin_data_id) instead my weekend record is coming from 1037852 (1000 10am) ,
View 4 Replies
ADVERTISEMENT
Jun 10, 2011
I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..
select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual
[Code]...
View 31 Replies
View Related
Apr 22, 2013
I need to block the weekends in Date Picker item. It must appear disabled.
View 1 Replies
View Related
May 18, 2011
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
View 5 Replies
View Related
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
Oct 11, 2012
I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies
View Related
May 30, 2011
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies
View Related
Apr 12, 2008
I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
View 3 Replies
View Related
Feb 15, 2009
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
View 2 Replies
View Related
Sep 23, 2011
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
View 9 Replies
View Related
Sep 12, 2011
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies
View Related
Mar 16, 2010
I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
View 8 Replies
View Related
Feb 11, 2011
What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies
View Related
Jun 2, 2011
I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.
I want to build a query such that it will give all the data from child tables.
create table main_tab ( tab_name varchar(10));
insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');
Now I have got the query to combine all the table data using
select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab
but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.
I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?
View 12 Replies
View Related
Sep 12, 2012
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PS:All the columns are same in all the 3 tables
table1_COLLECTIONS_a
table1_COLLECTIONS_b
table1_COLLECTIONS_c
View 7 Replies
View Related
May 13, 2013
In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries
I am trying it to use CASE or DECODE..
Here is the Query..
SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)
[code].....
The output is as follows..
7839KING5000PRESIDENT7839 KING Manager 5000 0 10
7698BLAKE2850MANAGER 7698 BLAKE 7839 2850 0 30
7782CLARK2450MANAGER 7782 CLARK 7839 2450 0 10
7566JONES2975MANAGER 7566 JONES 7839 2975 0 20
7902FORD3000ANALYST 7902 FORD 7566 3000 0 20
7788SCOTT3000ANALYST 7788 SCOTT 7566 3000 0 20
I am able to display only the Manager details or employee details and not both at the same time..
View 13 Replies
View Related
Aug 19, 2010
I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example
insert into A (a,b,c,....
select a,b,c......
from X;
insert into B (a,b,....
select a,b,....
from A joined with Y;
commit;
After executing the script in our development DB
select count (*) from A
returns 200
When the script is executed in production, A table record count is 5
select count (*) from A joined with Y; (used in the second insert)
returns 200.
It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.
Will executing
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY ;
View 8 Replies
View Related
Aug 2, 2010
i have source table having 1000 records, i want insert first 100 rows in table1,second 200 rows in table2 and remaining row in table3.
View 12 Replies
View Related
Aug 15, 2012
My tnslistner is not working that is why i am not able to login in my database. so i have planned to reinstall it and if possible get my database back from old files.
i do not have backup, is there any way to get tables and data back.
View 5 Replies
View Related
Jul 21, 2012
I Have to write a procedure which takes XML data and inserts into some tables.
If the XML format is fixed then i can use extract function for parsing and can insert into the tables.
But the problem is there is not fixed format for the xml.
Are there any built in packages which takes the xml data for parsing..
View 3 Replies
View Related
Jun 27, 2007
I have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
View 2 Replies
View Related
Mar 13, 2007
how can I insert data into tables on another user. They both are in the same table-space.
View 1 Replies
View Related
Jun 16, 2011
In my select statement i am fetching the data from
OE_ORDER_HEADERS_ALL,OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS,WSH_SERIAL_NUMBERS.
I need appropriate links for that tables.
View 2 Replies
View Related
Aug 29, 2008
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
View 3 Replies
View Related
Apr 19, 2011
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
how to take care of this sequence no.
View 15 Replies
View Related
Apr 30, 2013
TEMP1--ORINIGAL TABLE NAME
DESC IS:
SNO NUMBER;
SNAME VARCHAR2(100);
BACK UP TABLES ARE
TEMP1_1;
TEMP1_2;
TEMP1_3;
.
.
.
.
.
TEMP1_10
IN THE ABOVE 11 TABLES SNAME COLUMN HAVING THE DATA AS 'NARESH'
IN SOME OF THE TABLES AND NOT IN OTHERS.
REQUIRMENT:
I WANT THE TABLES ONLY HAVING THE
SNAME COLUMN 'NARESH'.
View 7 Replies
View Related
Jan 13, 2011
I'm having problems transferring data from one table to another as as one table as only 2 fields and the one data meant to go to has id which i just want to increment value because its primary key
table bill fields (name, age)Values (gate,85)
table oracle (pl_id, pl_name, pl_age) often wont insert cz data too short
this how i tried and failed
INSERT INTO oracle (TIME_ID) VALUES(SEQ_CUST.NEXTVAL)
(SELECT name, age FROM gates WHERE name = 'gates');
also tried
SQL> SELECT SEQ_CUST.CURRVAL FROM DUAL;
SELECT seq_cust.currval FROM dual;
*
ORA-08002: sequence SEQ_CUST.CURRVAL IS NOT yet defined IN this session
SQL> SELECT SEQ_CUST.NEXTVAL FROM DUAL;
1
SQL> SELECT SEQ_CUST.CURRVAL FROM DUAL;
1
INSERT INTO gate (TIME_ID) VALUES(SEQ_CUST.NEXTVAL)
(SELECT age, name FROM CRIME_ACTIVITY WHERE ID = '1');
or have both tables with
View 2 Replies
View Related
Jul 3, 2012
I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.
Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.
INSERT INTO TB_B
SELECT *, sysdate from TB_A;
View 9 Replies
View Related
Jul 24, 2012
i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
View 39 Replies
View Related
Mar 1, 2010
I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.
Table1:
col1col2category
A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1
Table2:
colAcolBActive
A10Y
A20N
B30Y
B40N
Z35Y
Table3:
STUDENTSCORE
A20
B40
C10
D20
J30
Z35
query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.
View 8 Replies
View Related
