SQL & PL/SQL :: How To Populate Distinct Column In A Table
Apr 22, 2013
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?
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.
I need to query a table to read the value, specifically a date, in one column and characters in another, i.e. and ID number, and populate a new column with new data. For example:
If Column A = 1/1/2009 and Column B = 0123 in table 'Persons' I need a query statement to populate Column Z with a 'Yes'
If Column A = 2/1/2009 and Column B = 9876 in table 'Persons' I need a query statement to also populate Column Z with a 'Yes'
I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time 1 jones tom lon gb 2:25 2 wall paul la usa 2:30 1 jones tom lon gb 2:50 3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg: ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10 7 lmn 10 . . . so on... (till some count) Result should be Query 1 should result ---> ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10
query 2 should result --> 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10
query 3 should result --> 7 lmn 10 . . 9 .. .. so on..
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;
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 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
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:
select distinct c.process_id a.role_id,a.email_address,c.process_name from role a, notification_role b, process c where a.role_id=b.role_id and b.process_id = c.process_id
My goal is to have a de-normalized summary table in which total communication volume for each distinct channel is displayed per customer.Briefly, I would like to reach the following output
When I run the sql below I am not able to de-normalize by customer id; counts are accurate.
SELECT distinct cil.customer_id, (CASE channel WHEN 'SMS' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil1 where cil1.channel='SMS' and cil1.customer_id=cil.customer_id) END) SMS_COUNT, (CASE channel WHEN 'Email' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil2 where cil2.channel='Email' and cil2.customer_id=cil.customer_id) END) EMAIL_COUNT FROM customer_interaction_log cil;
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]..........
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
-- Solution populate poplist ---------------------------- -- Name of block: block01 -- Name of poplist item: year -- Name table: tb_years -- Create on trigger when-new-form-instance
