how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
View 3 Repliesi want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :
DECLARE
Cursor cur IS
SELECT numb_ph AS phone,
name_emp AS name
[code]...
but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..
I need to write a query to sort the records in a particular order,
Say if I group the records by Dept number
Dept no Name
10A
10G
10f
20B
20K
30I
30M
30R
30Y
I need to write a query that will make this records listed like
Dept No Name
10A
20B
30I
10G
20K
30M
10F
20null
30R
10null
20null
30Y
I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------
CREATE TABLE temp_table1
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
CREATE TABLE temp_table2
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE
------------ ------------ ----- ---------------------
john smith MA 12/1/1979
null null AZ null
null null CT null
null null MA null
null null CT null
how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.
select distinct comp_id as compid,
comp_disp_name as company,
plcy_id as policyid,
[Code]....
output
-----------------
compid compname policy_id policy_name
19734 Save the Children 9013 GPA
19734 Save the Children 9012 GMC
20097 JMT 9486 GTL
10890 Steelco Gujarat Ltd. 9727 CAR
17330 Golden Jubilee Hotels Limited 8915 CGL
23117 NBHC 9093 GMC
17542 Heinz India 10693 Fire
19821 KSK Fabricators 10341 D&O
3769 Jones Lang Lasalle India 9199 WC
19821 KSK Fabricators 10340 WC
I am using Oracle 11g
The procedure is compiled without any errors.
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);
[Code]....
I have created two tabs tab1 & tab2. after changed(not while changing/before changing)) into tab2 all records should be display.
I have written execute_query in when-tab-page-changed.but that is before/while changing.i want after entered in tab2 it should be display.
want to display records of two tables in in one form.I have 2 tables. tab1 and tab2
columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.
Common column in both the tables is code.
i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty
i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)
what i want is 0 to be displayed over there in the below table
s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F
920110600000156431372010043424541500065457709539XXXXXXXXXXU
10201106000001565313720100534245415000009539XXXXXXXXXXU
11201106000001566313720100634245415000009539XXXXXXXXXXU
12201106000001567313720100738555415000009539XXXXXXXXXXU
13201106000001568313720100836445415000009539XXXXXXXXXXU
1420110600000157131372010110000009539XXXXXXXXXXU
15201106000001572313720101276881082200001888009539XXXXXXXXXXU
1620110600000157331372011010000009539XXXXXXXXXXU
My query formation is like below..
*Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi*
SELECT T1.COL1,T1.COL2,
T1.COL3,T2.COL2,
T3.COL1,T3.COL2,
T4.COL1,T4.COL3,
<CASE statements and calculations results - Some Amount1>,
<CASE statements and calculations results - Some Amount2>
[code]..........
First I need to display all the records from TAB_ONE which contains more than 10million records. If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which in turn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.
To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours for inserting this into a new table.
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
I have created one tab canvased form
On first tab I am taking input (search criteria)
on that input i am trying to display all records one by one as user click on next record button....
for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
how to display check box selected records in one column of form when push button is pressed.
View 1 Replies View Relateda sample query to display second MAX value from the oracle database
View 1 Replies View RelatedWe deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
I have a following table,
create table test ( col1 date,col2 number);
Data:
col1 Col2
----------
NULL NULL
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
I am bit confused to display all the rows in a column using stored procedure like
In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.
In oracle:
create or replace procedure test
is
begin
select * from table;
end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
How may I achieve it.
how to display the dupicate record in table
View 1 Replies View Relatedhow to create a procedure to display all the rows in a table.
I wrote the below PL/SQL but im hitting as below.
DECLARE
howmany NUMBER;
some_full_name login_user.full_name%TYPE;
some_login_id login_user.login_id%TYPE;
some_users login_user%ROWTYPE;
[code]....
error :
Error report:
ORA-06550: line 3, column 19:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 19:
PL/SQL: Item ignored
ORA-06550: line 4, column 18:
[code]....
in the below query
select
b.user_code
, b.user_name
, b.user_disabled
, b.user_email
, b.user_created_by
, b.user_created_date
, b.user_approved_by
, b.user_approved_date
, b.flag
, b.user_status
from mst_user_checker b, mst_user a
where a.user_code=b.user_code
and b.flag in ('I','P','R','F')
If the flag is 'F', then I need to display the data from the mst_user table. Where should i modify the above query ?
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls
2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
Trying to auto insert the newest records from one table into another Table. I have a vendor provided table that is part of my database (running Oracle 9i) so I can't change the underlying structure to it or their process stops fluxing. However, I can add a trigger to it. What I want to do is this:
When the vendor's software inserts a new row (through their own automated process) I want to insert data from that same new record into another table of my own. (where of course I can re-format it, etc., and make the data my own)
The original vendor table does not have a insertion timestamp field to work off of.What is the best way to trigger an insert off the latest inserted record? It works to replace all the records in the entire vendor table but I only want to insert one record at a time.
I would like to format my output for the select query.
Example:
SQL>select empno, ename from emp;
empnoename
1987766Jack Blake
187765Greg Ni
But I would like to get the output in the below format
EMPNO:.................1987766
ENAME:.................Jack Blake
EMPNO:.................187765
ENAME:.................Greg Ni
I will need printing the column name. I would like to know how do we address the column name to print.
How can we display the rows which are existed in T_SOURCE table and which doesn't existed in T_TARGET table using outer join.
My code is as follows.
CREATE TABLE T_SOURCE(sno NUMBER,sname VARCHAR2(20));
CREATE TABLE T_TARGET(sno NUMBER,sname VARCHAR2(20));
INSERT INTO T_SOURCE VALUES(1,'A');
INSERT INTO T_SOURCE VALUES(2,'B');
INSERT INTO T_SOURCE VALUES(3,'C');
INSERT INTO T_SOURCE VALUES(4,'D');
INSERT INTO T_SOURCE VALUES(4,'E');
INSERT INTO T_TARGET VALUES(1,'A');
INSERT INTO T_TARGET VALUES(2,'B');
I tried this but I am getteing all the rows.
select t_source.sno,t_source.sname
FROM t_target,t_source
WHERE t_target.sno(+)=t_source.sno
AND
t_target.sname(+)=t_source.sname
How to display table columns on oracle form?
View 7 Replies View RelatedI used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
View 1 Replies View Related