SQL & PL/SQL :: Add Begin_Dt And End_Dt Columns To EFFDT Table And Populate Them
Mar 31, 2011
I am planning on adding Begin Date and End Date on an EFFDT (Effective dated table). easy method of populating the Begin and end Date columns based on the EFFDT already on the table. Below is an example:
Department Table
From:
DEPTID EFFDT ...DESCRIPTION
12345 1/1/1900 Accounting
12345 1/1/2000 Accounting Exp Unit
12345 2/1/2000 Acct. Expense Unit
12345 5/1/2000 Account Expense Unit
12345 10/1/2000 Account Exp Unit
TO:
DEPTID EFFDT ...DESCRIPTION BEGIN_DT END_DT
12345 1/1/1900 Accounting 1/1/1900 12/31/1999
12345 1/1/2000 Accounting Exp Unit 1/1/2000 1/31/2000
12345 2/1/2000 Acct. Expense Unit 2/1/2000 4/30/2000
12345 5/1/2000 Account Expense Unit 5/1/2000 9/30/2000
12345 10/1/2000 Account Exp Unit 10/1/2000 12/31/9999
This is the first time the table is getting loaded. I get the distinct emp name from table 'X' and insert into teh sales person table to list out all teh emp present in the company and give the corresponding details.
I am not sure how to write a proc to populate the whole table. emp id is sequence generated.
begin date any date say 10/01/2010 (mm/dd/yyyy) end date is 99/99/9999 indicating that the emp is not terminated is_current flag = 'Y' if end date is 99/99/9999.
I have done a distinct empname from table 'X' and inserted into the salesperson table. Do i proceed to update the table for individual columns? *is that the right approach*?
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES" AFTER INSERT OR UPDATE ON DOMAIN REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M FOR EACH ROW WHEN (NEW.P_M = YES) BEGIN INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID); END P_M_YES;
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
TABLE_FROM_BLOCK built-in Package is working in Forms 6i but not in Forms 10g. is there any other built -in available instead of this. how do we populate table from a block in Forms 10g?
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month A 2008 Jan A 2008 Feb A 2008 Mar A 2008 Apr A 2008 May A 2008 Jun A 2008 Jul A 2008 Aug A 2008 Sep A 2008 Oct A 2008 Nov A 2008 Dec A 2009 Jan A 2009 Feb Etc.
I've a staging table STG_TABLEA which has a primary key discount_seq_no.
I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.
I am using Oracle 11.2.0.2 version.
I've put together the below code, not sure on next steps...
BEGIN UPDATE STG_TABLEA SET A.DISCOUNT_SEQ_NO = "INSERT A SEQUENCE HERE AND KEEP INCREMENTING the seq value by 1 COMMIT; EXCEPTION WHEN OTHERS THEN RAISE; END;
create table src(id number,val number,data varchar2(100)) insert into src values (1,1,'SUN'); insert into src values (2,2,'WED'); insert into src values (3,3,'MON'); create table trg(id number,val number,data varchar2(100)) required rows to be inserted in the target table.
insert into trg values (1,1,'SUNDAY'); insert into trg values (2,0,NULL); insert into trg values (2,0,NULL); insert into trg values (3,0,NULL); insert into trg values (3,0,NULL);insert into trg values (3,0,NULL);
{code} based on the column value of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:
1)id -as it is
2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the value of val and data will be null
3)data - if the val of src is 1 then expand the abbreviation else null .
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE -reg_id -name
ADDRESS_TABLE *trigger to be fired when a new record is created here. -reg_id -srv_id
PROCESS_TABLE -srv_id -start_time -end_time
This is what I would like the logging table to look like:
how should i populate table column heading in list items of forms? I've create lov to select the column, then i have 10 separate list items. once i select the table from lov then list item should get populated with selected table column.
when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below
go_block('show'); clear_block(NO_VALIDATE); declare cursor c1 is select * from qtr_demand order by 1; begin [code]..........
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE The new table SQL> desc "TESTINVENTORY"."TTRANSACTION" Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TRANSACTIONIDNOT NULL CHAR(26) BRANCHCODE NOT NULL CHAR(3) EXTERNALSYSTEM NOT NULL CHAR(3) EXTRACTSYSTEM NOT NULL CHAR(3) OWNERBRANCHCODE NOT NULL CHAR(3) TRADEREFERENCE NOT NULL CHAR(20) [code]...
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:
Query 1
Update TableA A set (A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3 from TableB where A.CODE=B.CODE)
Query 2 Update TableA A set (A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3 from TableB where A.CODE=B.CODE) where exists A.code = (select B.code from TableB B where A.code=B.code)
When i execute these two above queries, it keeps executing indefinitely.
Perhaps this is a common request : I have 2 tables:
Table A ------- ID Value 1 a 2 b 3 c
Table B ------- ID AnotherValue 1 x 2 y
I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:
Table A
ID Value AnotherValue 1 a x 2 b y 3 c (null)
)
I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?
We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.
(This table has many fields, one of which is report Name field)
Now i am running a query like this...
SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'), DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A', a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) - TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM, b.PRVDR_ID, a.asctd_id, d.NM FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D' AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT) FROM STATUS_DIM d WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID ) ORDER BY a.RPT_RQST_ID, a.CREATE_DATE
Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??
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.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; BEGIN JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W'); UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert'); FOR REC IN JOBSCUR [code]....
is there anyway to print the values of full table .i have used all column names to print .
I need to add values of one column values from a table to another table each value as a column. Below i am considering only for 3 values in real time i have more than 50 values
CREATE TABLE TEST_REG ( VAL VARCHAR2(1));
INSERT INTO TEST_REG VALUES ('A'); INSERT INTO TEST_REG VALUES ('B'); INSERT INTO TEST_REG VALUES ('C');
CREATE TABLE TEST_HOLD ( COL1 VARCHAR2(1),COL2 VARCHAR2(1),COL3 VARCHAR2(1)); -- in realtime i have 100 columns
I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..
just wanted to know if i create a table with 1000 columns, and use only few of them and remaining are for future use, will it affect the performance and storage?
