SQL & PL/SQL :: Linear Output Of Multi Row Data
Feb 21, 2011
I've below table structure with given table data. I need below given output i.e. a linear output. It should be dynamic i.e. there are only three columns for Manoj and four columns for Arun. If I insert one more row for Arun the new added row should also be reflected in the same row and every column should give same contact type.
CREATE TABLE TEST123
(
PERSON_NAME VARCHAR2(100),
CONTACT_TYPE VARCHAR2(20),
CONTACT_NO VARCHAR2(15)
) ;
[code]...
how go generate this output on single query.
View 9 Replies
ADVERTISEMENT
Jul 25, 2012
I am trying to get a line of best fit for a set of data in oracle
TERM Count
TERM_080178
TERM_080258
TERM_08030
TERM_080482
TERM_08050
TERM_08060
TERM_080732
TERM_08080
TERM_090192
[code]...
View 4 Replies
View Related
Sep 25, 2012
I've got date column with increasing date (populated as sysdate) , in point of time I created index (compute statistics) on that column without histogram (only min/max value).So if I dont regather stats on that column, my cardinality estimate will go lower day after day till value of 1 , right (because out of linear decay)?
View 13 Replies
View Related
May 7, 2013
Provided a sample table to determine the sales agents performance below.
Id AgenName Zone1 Zone2 Zone3 Zone4
1 ABC 90 85 65 55
2 SDD 45 67 78 90
3 ERF 85 30 52 45
If in any zone, the sales is less than 40, it should be failed.This can be determined using the below query
select Id, AgenName, Case when Zone1 < 40 or Zone2 < 40 or Zone3 < 40 or Zone4 < 40 Then 'Failed'
Else 'Eligible'
End as Flg_Comm
from sales_table;
But the problem is when the zone grows (zone5, zone6) every time the query requires to be added with or condition.
Is there a way to dynamically determine the result even when no. of columns grow (or added)?
View 1 Replies
View Related
Sep 12, 2012
table 1
empno, salary
1001,9800
1002,10000
1003,4000
1004,6000
table 2
sal_from, sal_to, rate
1000, 3000, 0
3000, 4500, 10
4500, 5500, 12
5500, 6500, 30
6500,999999 50
now how can i compare values from table 1 in table2 and apply RATE on table1 SALARY like (SALARY*RATE)
View 3 Replies
View Related
Nov 15, 2011
We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.
Problems with each of the Oracle loaders.
1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.
2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.
I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions
1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?
2) Do any of you maybe have another strategy to do this?
View 8 Replies
View Related
Aug 10, 2013
I have a form with 3 blocks in our oracle application. Block1 is a single record block. Block2 and block 3 are multi record blocks with no of records 5. Block1 and block2 are in same canvas1 of a window1. Block3 is in canvas2 of window2. There is a relation between block1 and block2. Another relation with block2 and block3 is also available.
Now my issues is while creating a record, first i entered the data in block1 (which is a single record block). Then i entered data in five rows of second block. Then i entered data in block3(multi record block) by pressing the button in block2 (each button for each row). But before saving the entire form, when i checked the data in block3 by pressing the button of first record of block2, screen shows empty.
Code in button of block2 is as follows
show_window(WIN2');
Go_block('BLOCK3');
Code in button of block3 is as follows
hide_window('WIN2');
go_block('BLOCK2');
If post; is included in block3 button. then problem we will be solved. But we don't want to use post; as it lock the table.
View 16 Replies
View Related
Jun 15, 2011
I have data block that has hundreds of records. On a screen I display 20 records at a time. On that same screen I have "find badge" function where user types badge/employee id and presses a button to find the record in a data block. I have no problem finding the records in a list and pointing the curser to appropriate record. My question is (which is a user request) if the badge/employee id is found to make that record to be the first record in a list.
View 2 Replies
View Related
Nov 19, 2010
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
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
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
View 12 Replies
View Related
May 18, 2011
how can i get the output format from given input table
View 2 Replies
View Related
Jul 1, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
View 2 Replies
View Related
Sep 25, 2013
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
View 6 Replies
View Related
Dec 2, 2006
I would like to find a way to do a multi row insert in Oracle similar to the examples below ... if possible.
Note that I need it to work with constant values and not values selected from (another) table (ref examples below).
In MySQL and DB2 I can do this:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
values (8000,0,'Multi 8000',1),(8001,0,'Multi 8001',1)
In MSSQL, PostgreSQL, and SQLite I can do this:
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 union all select 8001,0,'Multi 8001',1
View 14 Replies
View Related
Sep 16, 2010
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.
View 7 Replies
View Related
Jun 2, 2012
My application spools the data from database into file and file is fixed length. My application working fine when database has single byte characters. But I am getting fields postion misposition and length mismatch in spooled line when data base has Multi byte characters(ê).
I want to convert the multi byte characters into single byte while spooling the data( ê ==> e)
The below query has converted the multi byte to single byte character. But actual value is modified into lower case.
####################################################
select utl_raw.cast_to_varchar2((nlssort(c.Name,'nls_sort=binary_ai'))) sbyte, '|'
c.Name
from EMP c
viva! experiencias | Viva! Experiências
####################################################
View 2 Replies
View Related
Oct 13, 2011
I have a table with, for example, three columns: A, B,C.
I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2
Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?
View 4 Replies
View Related
Jan 24, 2012
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
View 4 Replies
View Related
Mar 12, 2012
I want to enter data in Arabic and English through Oracle Forms6i.
View 8 Replies
View Related
Mar 8, 2012
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
[code]....
error : 30/44 PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
when i removed bulk collect from fetch commands, it works fine.
View 2 Replies
View Related
Oct 3, 2011
I have a multi tab form. When I open any form; the form no. and form lists are automatically uploaded. I can enter the rest of the data and save the form. The problem is; if I try to open another tab(form), system did not allow it and ask to enter data in the already open form fields. I can only save the form, which I open first.
View 2 Replies
View Related
Jul 22, 2012
I been doing a little testing with indexing the contents of URLs (database v11.2.x.x).
ctx_ddl.drop_preference('TEST_URL');
ctx_ddl.create_preference('TEST_URL','URL_DATASTORE');
ctx_ddl.set_attribute('TEST_URL','Timeout','3600');
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
View 0 Replies
View Related
Aug 23, 2012
whether it is possible to implement multi-threading in plsql?
we have a plsql procedure in our application which processes around 50 MM records per day. I am looking for options to implement multi-threading from the same procedure, so that work can be distributed across parallel threads/sessions and will finish processing faster.
View 4 Replies
View Related
Dec 11, 2012
explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.
A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.
View 2 Replies
View Related
May 3, 2011
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.
View 7 Replies
View Related
Oct 17, 2008
I have a multi row datablock with scroll bar. In each record I have a checkbox (let's call it check_record), and above these checkboxes, there is a checkbox to check/uncheck all the checkboxes (let's call it check_all).
If all the "check_record" are checked, the "check_all" must be automatically checked, else if one "check_record" is not checked, it will be unchecked.
To do that, I have put a loop in the WHEN-CHECKBOX-CHANGED of "check_record" to loop over all the "check_record" and see if they are checked or not.
Normally, this loop will automatically go to last record. But to go back to the selected record (where the "check_record" was clicked), I have used go_record with the record ID.
==> No problem in that, it's working fine.
Now, this is my problem: When clicking on a "check_record", the scroll bar moves to show the selected row in the first (or last) VISIBLE row in the data block, and that's because of using "GO_RECORD(<CURRENT_RECORD>)".
Example: if I select the third record in the data block, the scroll bar moves to show it in the first visible row.
Is there a way to "freeze" the scroll bar and keep the record in its initial position in the data block?
View 7 Replies
View Related
Feb 3, 2011
I am doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have written the code for program now i found that select statement is return multi rows.
code is
when_button_pressed trigger.
declare
fdcode dmas.dcode%type;
fcodno dmas.codno%type;
famtrs dmas.amtrs%type;
fdate donres.rdate%type;
begin
[code]......
View 4 Replies
View Related
May 18, 2010
I have two block namely block-A, Block -B
Block A data queried from a view (it have 10 columns).
Block b (it have 5 columns all columns are same in block A column)
When i query of block - A i have to receive the same copy in Block B too.
View 6 Replies
View Related
Aug 4, 2013
I have a master detail form and in the detail block when i am scrolling up the records are getting disappeared , but actually they are not getting deleted, once if i requery the data will appear, what could be the problem.I am using the property change record for block.
View 5 Replies
View Related
Jan 23, 2011
How to create multi selection tree in oracle forms ?
View 1 Replies
View Related
Aug 30, 2013
I have a requirement to send a table data through mail, so am using procedure inside which am using execute statement after opening the connection and am using the following PLSQL code, which am failing to execute successfully.
My code goes like this.
0 10 20 30 40 50
1 CREATE OR REPLACE PROCEDURE SEND_TABLE_DATA( FROMAD IN VARCHAR2,
2 TOAD IN VARCHAR2,
3 SUBJECT IN VARCHAR2,
4 MESSAGE IN VARCHAR2,
5 DOCID IN VARCHAR2,
6 DOCDT IN DATE,
7 PRODOAID IN NUMBER )
[code].....
View 2 Replies
View Related
