Block Can Contain Rows / Data From Multiple Tables?
Jul 1, 2013
A block shouldn't have rows from multiple tables... Is that true? I read in one of the OTN thread (i don't exactly remember the thread name) that a block can have data from multiple tables. If it doesn't have, what's the table directory in block signifies?
I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below. ------------------------------------------- Create or replace procedure p_proc as spidm spriden.spriden_pidm%type; cursor mycur is select * from mytable; begin for rec in mycur [code]...... ----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
Desired Output: ====== First Second Third 11 21 31
I have tried the below query SELECT DECODE (name,'Nexus', parameter) First, DECODE (name, 'GPlay', parameter) Second, DECODE (name, 'Demo', parameter) Third FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third 11 <Empty> <empty> <empty> 21 <empty?> <empty?> <empty?> 31
Is there any way to get the output in single line.
I'm using Oracle forms 10g. In the form i have this multiple records under one block. After performing the query, all records are cannot be updated. And i have this buttons at the right side that will enable the certain item that can only be updated. My problem is i cannot enable the item in the first record,instead of first record all records in that item was enable.
I have a master detail based transaction form, i want to create three check boxes on header block, based on selection sorting must happen on detail block which has three fields, for example there are three fields item_code,item_name,item_qty, if user selects first check box then sorting will be on item_code, if he presses second then sorting should be based on item_name, likewise if he presses check box three then sorting will be based on qty, if choses two or more fields then sorting will according to that combined order.
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop begin execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null'; commit; [code]........
tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang ( Person_ID NUMBER NOT NULL, Language_ID NUMBER NOT NULL, Contact_Name VARCHAR2(255 CHAR), Main_Phone VARCHAR2(255 CHAR), Secondary_Phone VARCHAR2(255 CHAR),
I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where A.client_id = B.client_id and time_id between 20090101 and 20091001 and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date- Fine - Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date DATE Fine 2 Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query DECLARE TYPE all_dest IS TABLE OF NUMBER; destIds all_dest; BEGIN SELECT dest_id from sb_packet WHERE src_id = 32; RETURNING dest_id bulk collect into destIds; END;
in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY
Input data
SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL
Output expected CLIENT_IDDT_REGCITY 100100105/11/2010 LONDON 100100105/11/2010 PARIS 102220105/11/2010 MADRID
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.
For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.
I am pasting the test data below:
create table employee (eno number(9), ename varchar2(40)); insert into employee values(1,'Jack'); insert into employee values(2,'Jim');
[Code]....
I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.
Is there any better way to write this query in terms of performance?