Is there any function which return the next multiples of 10 value directly ?
Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50
I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.
How do you declare a field in Oracle as a true integer data type?
Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.
I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands
Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);
But here is the real issue. In colomn d I only need the integer portion of the division value.
For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.
I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.
I have times stored as an integer in a table.
e.g.
102506 = 10:25:06
130455
How can I convert this to time format HH24:MI:SS?
I want to find nearest integer value where mod returns 0 in sql statement. I've tried following but it doesn't fulfill my requirement.
My Try
SQL> select
2 ((1200*1000)+45-mod((1200*1000),45)) f1,
3 mod( ((1200*1000)+45-mod((1200*1000),45)),45 ) f2,
4 ((1200*1000)+45-mod((1200*1000),45))/1000 f3
5 from dual;
F1 F2 F3
---------- ---------- ----------
1200015 0 1200.015
In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.
I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.
The function is working but I wanted to take an expert opinion if it can be optimized.
CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL
(I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)
[Code]....
In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.
CURSOR C_AGREEMENT
IS
SELECT AGREEMENT_SERIAL
FROM VW_AGR_AGREEMENT
WHERE NUMBER0 = I_NUMBER0
AND TYPE_ID = I_TYPE_ID
-->and sub_number is null;
-->and sumb_number = s_sub_number
Recent events at work are forcing me to take a much closer look at hash joins in an attempt to understand them much deeper than just on the surface. But my question today is maybe simple. I have done lots of reading and can't for the life of me figure out how to get more memory to my HASH JOINS.
is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use?
1) my databases are all using workarea_size_policy=AUTO
2) I am not afraid to go back to =MANUAL and set my own work area sizes.
3) It seems I cannot set HASH_AREA_SIZE to more that about 2GB.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
18:40:31 SQL> alter session set hash_area_size = 6000000000 ;
alter session set hash_area_size = 6000000000
*
ERROR at line 1:
ORA-02017: integer value required
I know there is a limit of about 2GB on my box for HASH_AREA_SIZE and setting it to 2GB works fine. But it is still not enough.
18:50:22 SQL> alter session set hash_area_size = 2147483647;
Session altered.
Elapsed: 00:00:00.23
is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use? Using hash_area_size and 2GB, I get better performance than with my current PGA_AGGREGATE_TARGET doing the allocation for me.
I think I'd like to get as much as 20GB to specific sessions for hash joins but maybe I am pipe dreaming?
NAME TYPE VALUE
------------------------------------ ----------- -------------------
_pga_max_size big integer 1258280K
pga_aggregate_target big integer 6G
im creating sequences for an already created db and need to set MINVALUE to sequences.
create sequence tb_1
minvalue X
start with X;
so, X == select Max(id_tb1) from tb_1.
I need that select returns an integer.
How can I do this??
what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?
View 3 Replies View RelatedHow to get Time Difference between two DateTime Columns in Oracle 10g ?
View 10 Replies View RelatedI'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
Did I miss something when I created my network?
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?
View 15 Replies View Relatedhow to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?
View 4 Replies View RelatedHow 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 a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
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.
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
I have a database which shows the amount of labor done on a part and the columns I am retrieving from the database are as follows:
PART....... WORK_ORDER..... STEP.... OPERATION...... GOOD_PARTS
There are multiple parts in the database, each with multiple work orders, with multiple steps but each step only has 1 operation but each operation has multiple good_part entries.
What I need from this is to sum the sum of all good_parts for the last step of each work order but only if it is a certain operation for each part.
So say i have this data:
PART..... WORK_ORDER..... STEP..... OPERATION.... GOOD_PARTS
XYZ... .... .... 1.. ... .. ... ... 1.. .. .. .. GRIND.. .. . .. . . 50
XYZ... .... .... 1.. ... .. ... ... 1.. .. .. .. POLISH.. .. . .. .. 45
XYZ.. . ... ... .. 2 . . . .. .... .. .1.. .. ... ...GRIND. .. ... ... .. 40
XYZ. . .. .... ... 2 . .. . .. . ... . 2. . .. . ... POLISH .... .... ....45
XYZ... .. .... ... 2. ..... .... ... . 2... .... .... POLISH .. ... ... ... 5
XYZ . ..... ... .. 3.. ... .. .... ... .1. .. .... ... GRIND. .... ... .. . 40
XYZ. ... ... .. .. 3. .. .. .. .. ... . 2. .. ... ... POLISH. .. ... ... .. 45
XYZ. ... .... .. .. 3. .. .. ..... ... 3 . .. .. .. . SHIP. . .. .. .. .. .. 45
(This is the result of a bunch of joins and filters but it is essentially a SELECT * with the clause "ORDER BY PART, WORK_ORDER, STEP" to sort it correctly.)
And I want only the parts for "POLISH" i want to have the final sum of 95 because work order 1 has the last entry of "POLISH" with 45 parts; work order 2 has 2 entries of "POLISH" (which is the last step done) with a sum of 50; but work order 3 has the last operation "SHIP" so I don't include it at all.
I have previously pulled all this data into excel and ran a macro to calculate it but now I find that I need it to be calculated by the server. This database is Oracle, so I can use any functions it supports. I saw the function LAST_VALUE that might be useful.
We have Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit RAC in Linux and syslog server also in Linux.
syslog server is a centralize server to monitor all the system and database log.
is there any way to create a multiple alert log file. so that we can keep one alert log file in default location and another alert log file in centralize syslog server for monitoring purpose.
We are getting a consultant to upgrade an Oracle 9i installation to 11g R2. The current installation has 6 different databases installed on the same server. Each database is a different customer so for reasons of security we have requested that this be split into 6 virtual machines with one database per machine.
The consultant suggested that they could install the 11g database once and then just make copies (which would all have the same instance name. We are told that the TNS names can be configured so clients are directed to the right database.
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.
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),
[Code]...
Data Returned from Select is:
1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown
Ideally what is required back is:
1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown
I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)
The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F
I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F
write this sql ?
Video Rental Shop
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 ?
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10
I need to code a SQL statement (Not PL/SQL) to display following records:
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10
I have a table similar to the following,
USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...
which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.
select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user
However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:
user, latest_home_number, latest_work_number
I have a result of Query like this:
Col1 Col2 Col3
T1 15 20
T1 18 19
T1 5 20
T2 15 20
T2 18 19
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.
View 2 Replies View RelatedI have more 100 partition in a table, I would like to query 10 partitions alone in single statement, Hope it could be possible like query data for single partitions, provide the syntax for the same.
Because if I try to query for all the partition then the query is Hanging due to the large no of data, then I can query single partition by partition then it takes more than a day. so, I would like query data for 10 partition in a single select.