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 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?
View 3 Replies View RelatedI am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level
Table =Level_Mst
Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2
Table=Users
User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4
Desired O/P
Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section
Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.
1.-) i got a page that contains 2 regions, lets say :master (HTML text) and a detail (tabular form updateable report) in my tabular form i got a hidden item that should take the value from the master form. how do i do this ?
2.-) i am calling a form from another form, sending a couple of fields as a link parameters. the second form is called as expected, but i need that records on the second form that match the parameters get displayed. how do i achieve that ?
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.
View 2 Replies View RelatedI'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
I have a table like this
Desc Salesperson;
Emp_name varchar2(20),
emp_id number,
begin_date date,
end_date date,
is_current varchar2(3)
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*?
I like to the following:
Objects: Car, Person
Person can own a lot of cars which i like to implement as table of ref in person!
When i poluplate the ref table, I always get null-references! Compiler is ok, no syntax errors or anything else
drop table person_tab;
drop table car_tab;
drop type person_type force;
drop type car_type force;
drop type owns_type force;
---------------------------Objects-----------------------------
create or replace type car_type as object
(cartype varchar2(10),
byear integer
);
/
create or replace type owns_type
as table of ref car_type;
/
create or replace type person_type as object
(fname varchar2(10),
owns owns_type
);
/
commit;
--------------------------Object Tabs--------------------------
create table person_tab of person_type
(fname primary key)
nested table owns store as owns_cars;
/
create table car_tab of car_type
(cartype Primary Key
);
/
commit;
------------------------Populate Tables------------------------
insert into person_tab values
('meier', owns_type());
/
insert into car_tab values
('audi', 2011);
/
insert into car_tab values
('vw', 2012);
/
insert into car_tab values
('bmw', 2011);
commit;
----------------------Populate Ref Tables----------------------
insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'audi';
/
insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'vw';
/
commit;
query for below requiremnet :-
I have table A having column name Varchar2(10), Seq Number ,Address varchar2(20)
Data in table as
name SeqAddress
A1bangalore
A2karnataka
A3India
B1Mumbai
B2Maharastra
B3India
I need to write query to get below Output
Abangalore,karnataka,India
BMumbai,Maharastra,India
I can not use any inbulit function of oracle like "SYS_CONNECT_BY_PATH", "LIST_AGGR" or any other function , even can not use any user defined function.Need to write only SQL to get this result.
how can we get above result
Create a form based on the following output use EMP Table
Create a non Database Block i.e Control Block----> Dept No
Create a Database Block -EMP
Create an LOV for the Dept no from dept table.
For the Current Dept No . Populate the Employee Records
-- Solution populate poplist
----------------------------
-- Name of block: block01
-- Name of poplist item: year
-- Name table: tb_years
-- Create on trigger when-new-form-instance
[code]...
.fmb file why this populate lov is not working
View 9 Replies View RelatedI want to populate only particular employee LOV ,when i am press "Populate LOV"..Button and i attached Form for testing and It is a 10g version..
View 5 Replies View RelatedI have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
I have the following issue i have two table
PRODUCT (id, product_name)
1, prod1
1, prod11
2, prod2
3, prod3
OSS(id, oss_name)
1, oss1
2, oss2
what i want to return is the product_name and the oss_name for each id.
1, prod1, oss1
1, prod11,
2, prod2, oss2
3, prod3,
How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)
CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),
[Code]...
After the Data is loaded, we see data look like the above.
(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.
If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?
Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL
[Code]....
i have a database in which some tables. Now i want to alter column by using this query.
ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))
this column properties is QTY NUMBER(5)
now i want to convert it into QTY NUMBER (5,2)
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;
/
ALTER TRIGGER "P_M_YES" ENABLE
/
I get an invalid trigger specification.
The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.
View 5 Replies View Relatedi want to create the list item dynamically on the when new form instance this is the code....
group_id := create_group_from_query('lst','select (imt.item_id), to_char(imt.item_code, from items_mt imt');
v_num := Populate_group(group_id);
populate_list(v_num,group_id);
but the list is not the populate.
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?
View 6 Replies View RelatedI am dynamically creating a staging table my_stg, and then populate it. Seems simple, but not sure why i get this error,
create table gtest4(myid varchar2(10), mykey varchar2(10));
create table gtest5(myid varchar2(10), mykey varchar2(10));
insert into gtest4 values(1,3);
insert into gtest4 values(2,7);
insert into gtest5 values(5,3);
insert into gtest5 values (1,7);
commit;
CREATE OR REPLACE PROCEDURE px
IS
TYPE rectype IS RECORD (
myid VARCHAR2 (100),
mykey VARCHAR2 (100)
);
TYPE tabtype1 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;
TYPE tabtype2 IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;
rec1 tabtype1;
rec2 tabtype2;
cur sys_refcursor;
[code]....
count current exists max min prior sql stddev sum varianc execute forall merge time timestamp interval date
pipe
<an alternatively-quoted string literal
i have created on query block , upon pressing the button there is a wehre clause in the block which will filter the records based on all alike items from two tables but my problem is , in one table there is no information of struct and in other there is struct information or data, what i want is even if i pass a parameter in the where clause all the records should be filtered.
CREATE TABLE OT_ACTUAL_ACT (ITEM_CODE VARCHAR2(30),ITEM_NAME VARCHAR2(30),WO VARCHAR2(12),STRUCT VARCHAR2(12))
INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT)
VALUES ('1001','HEA100X10','300',null);
[Code]...
I am getting this
ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010
i want all the records
ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010
1003 HEA100 300
1001 HEA100X10 300
CREATE TABLE TBL_LTEST
(
ID NUMBER,
MYPO NUMBER
);
alter table tbl_ltest add constraint pk_tbl_ltest primary key (id, mypo);
I want the data in this table to be like this:
ID MYPO
1000 401
1000 402
1000 403
2000 401
2000 402
2000 403
Rule of MYPO column population:
1. It should always start with 401 (I can handle that with Oracle check constraint that mypo should be > 401)
2. For each Id, the first insert has to be 401, then it should add one from there within the Id
(something like, mypo is a sequence within the ID.)
I am trying to populate mypo inside the trigger in this way:
CREATE OR REPLACE TRIGGER trigTBL_LTEST
BEFORE INSERT OR UPDATE
ON TBL_LTEST
REFERENCING NEW AS NEW OLD AS OLD
[code]...
I've got a situation where I need to populate a list box of lab numbers when the user selects a matching date received date.
Right now it only works when the user presses enter on the field used to accept a date.
It also works when a user fills in date and then clicks twice on the listbox of lab numbers. (assuming there are some records for the date in question).
Both fields are in a data block that is not connected to a table. Here is the when-mouse-doubleclicked trigger
LAUNCH_CALENDAR('DISPLAY.DATE_BEAN');
IF :DISPLAY.DATE_RECEIVED IS NOT NULL THEN
GO_ITEM('display.list_labno');
--POPULATE_LIST_ITEMS('date_received_records','display.list_labno');
do_key('list_values');
[Code]....
As you can see we have tried several different triggers to get this to work with a calendar date chooser (java bean). The bean works and allows for a date to be returned. However, it does not want to populate the listbox.
My Requirements:
1. i want to create form for student's attendance.
2. for this purpose i create a form . and create 2 Data Blocks
1. Non Database Data Block (layout type: form)
2. Database Data Block (layout type: Tabular)
3. in Non Database Data Block i add 3 Items.. on the basis of i get the records from database and show them into my into my 2nd Data Block...
4. my problem is this that how can i get records from database and show them into my forms and then save thats records into my table by using Database Datablock.
I have a table :
Product
A
B
C
D
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.
This would be done for all products for 4 years.
i have two blocks called BLOCK1 AND BLOCK2
BLOCK1 is a multi record block with a field, FIELD_A
BLOCK2 is a multi record block with fields FIELD_B,FIELD_C,FIELD_D
i have a button on BLOCK1 called 'FETCH'.
The requirement is when_button_pressed on 'FETCH'. BLOCK2 must be populated for each record of BLOCK1. Population logic is based on, values from a database table TABLE_A which has got column values corresponding to all fields of BLOCK1 and BLOCK2.