How To Name A Column From Data Stored In Certain Row
Oct 28, 2012
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have googled but I think the query is a little too complicated for me to explain it and get a result from google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
View 4 Replies
ADVERTISEMENT
Oct 28, 2012
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have Googled but I think the query is a little too complicated for me to explain it and get a result from Google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
I am trying to learn by myself how to use databases and am using the HR database that comes in oracle since I read in a forum that it is a great way to learn how to use queries.
I read about using Pivot to do it, but in pivot I need to know how many area codes there are. I will post a screen shot of the table that I am working on the other post
[URL].....
I can group by phone number with this:
SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT
FROM EMPLOYEES
GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);
But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.
This is kind of what I am trying to do, but instead of AREA appearing as column name I would like for each one of the area codes to appear as column name, here is the
SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ORDER BY DEPARTMENT_NAME;
View 5 Replies
View Related
Jul 12, 2012
I have a problem that I don't know how to solve it.here is it:
I have a table test1(intitule varchar,age number):
INTITULE AGE
------------------------------ ----------
nom1 1
nom2 2
nom3 3
nom4 4
nom5 5
nom6 6
nom7 7
nom8 8
nom9 9
nom10 10
I want to disorder(randomize) column INTITULE without touching to 'age'.
View 28 Replies
View Related
Dec 14, 2012
My ORACLE DB version is:
('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 Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');
I have this XML data stored in a CLOB column:
<Activity>
<Changes>
</Changes>
<Inserts>
</Inserts>
<Definition>
[code]....
I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.
View 3 Replies
View Related
Sep 23, 2010
Oracle database version is : 10G.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
get this issue resolved.
View 7 Replies
View Related
Jun 29, 2012
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
View 17 Replies
View Related
Jul 24, 2012
how to insert data in a table by deleting previous entered data and only inserting current data like:
CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);
[code]....
I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:
INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);
it must show aaa,8 and aaa,9 bt not the previous values.
NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.
View 4 Replies
View Related
Aug 17, 2012
I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.
Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?
Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
View 4 Replies
View Related
Oct 27, 2011
I have a table named PRODUCT (ID Number , ProductImage BLOB). I have inserted some records in. I want to create a form for user to view this table.
When I create Data Block, in Data Block Wizard, if I select option 'Table or View' to retrieve data, it's ok (Data type of item ProductImage is Image, when I run form and query, it display image normally).BUT, I want to retrieve data from stored procedure in a package. This is my package
PACKAGE my_pkg IS
TYPE my_rec IS RECORD
(
myID PRODUCT.ID%TYPE,
myImage PRODUCT.ProductImage%TYPE
);
[code].....
I select option 'Stored Procedure' in Data Block Wizard, then select my stored procedure (my_pkg.Do_Query). Next I choose 2 columns from my stored procedure (ID, ProductImage). The wizard still applys data type of ProductImage as Image. I finish Data Block Wizard and Layout Wizard... run form, query and an error message display:
FRM-40364: The data type of item 'ProductImage' does not match the corresponding column in the stored procedure.
I try to change 'myImage PRODUCT.ProductImage%TYPE' -> 'myImage BLOB' in my package but still get that error.
View 4 Replies
View Related
Feb 20, 2012
I am trying to store Chinese characters in certain fields in my database:
NLS_CHARACTERSET is UTF8
Version 11.2
Client NLS_LANG = UTF8
Client Region Settings = Simplified Chinese (PRC)
To Test - I have a string value I am trying to write to a field in Oracle. Looking at the source string with a hex editor, the multibyte value is E6 9C AC.
Looking the character up in UTF8 tables (U + 672C ) I can the symbol matches what I see when I look at the string with my editor.
I write the string to a field in Oracle. I can select from the field in SQLPlus and see the correct symbol.
However, when I dump the field I get the hex values B1,BE not E6 9C AC as I would expect.
explain why? And how is B1, BE getting translated back to the 'correct' symbol?
View 1 Replies
View Related
Jan 30, 2007
I work on a client-server application, where users need to be able to run rather complex queries.
We currently have most of the queries defined in views on the Oracle database server and the client application simply downloads the data (i.e. SELECT * from example_view). This is good for us as we can maintain these queries without releasing new versions of the client tool.
However we have some queries implemented by a colleague that have caused a lot of trouble (efficiency and quality) and these are stored client-side.
The issue I have is that these client side queries can return records in different units (i.e. in standard cubic metres, or barrels of oil etc), as the SQL is defined at runtime on the client, and I want to know the best way to replicate this with SQL stored server-side.
The client-side SQL has column definitions such as: SELECT oil_production * decode(&unit,'Nm3',.948,'Sm3',1,'MMBOE',6.0924,1) ... The &unit parameter is then replaced by the appropriate text (i.e. 'Sm3') before the query is sent to Oracle.
Is there anyway to pass variables to server-side SQL and get a recordset back? I don't think PL/SQL procedures can do this? and views can't contain bind variables.
View 1 Replies
View Related
Nov 8, 2010
I have a XML data that needs to be stored in oracle table and it is read from a java application. what datatype will suit for XML storage varchar2 or CLOB. The length of XML will be less than 4000.
View 4 Replies
View Related
Aug 1, 2011
CREATE OR replace PROCEDURE T_name_column
AS
file_handle utl_file.file_type;
BEGIN
[Code]....
View 6 Replies
View Related
Apr 30, 2010
How to create Data Block Using Stored Procedure in Forms10G ?
View 1 Replies
View Related
Sep 23, 2013
is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies
View Related
Oct 14, 2010
This is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
View 8 Replies
View Related
Nov 20, 2011
The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.
CREATE OR REPLACE PROCEDURE populate_product_dimension
AS
v_Count NUMBER := 0;
v_NumRecs NUMBER;
/*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/
[code]....
In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.
View 10 Replies
View Related
Oct 24, 2013
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies
View Related
Mar 11, 2013
I am using:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Microsoft Visual Studio 2010 : VB Express
Microsoft .NET Framework : Version 4.0.30319 RTMRel
When I populate dataset using select query and use OracleCommandBuilder and update dataadapter, data is saved in database.
But if I put same select in store procedure and use this to populate dataset then data is not updated in db.
I get this error
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
at da1.Update(ds1, "req") where I am going wrong.
Dim conn As New OracleConnection
Dim da1 As OracleDataAdapter = New OracleDataAdapter
Dim ds1 = New DataSet
With conn
If .State = ConnectionState.Open Then .Close()
[Code]....
View 2 Replies
View Related
Feb 6, 2012
This procedure is not working properly.
create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1
View 1 Replies
View Related
Jan 29, 2013
I have a database in which two table contains millions of data and the whole database size is getting more bigger. is there any option to archive the old data which has stored before 2012 and can be restored when the data required for 2011 or 2010 needed.
View 4 Replies
View Related
Mar 21, 2013
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description
78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
This column data can be varied from 1 to any number of words.
View 11 Replies
View Related
Jul 31, 2013
I have a INSERT query which is happening with a SELECT query.
===================================================
INSERT INTO tbl_fact_effort_lvl_data ( ............... )
SELECT ria.report_id,report_status: :,
((SELECT lov_num_val
FROM tbl_reference_data
WHERE lov_type = 'FREQUENCY' ) * (SELECT SUM(pph_task)
FROM tbl_ri_process
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id
FROM tbl_fact tf LEFT JOIN ......... ;
====================================================
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
View 8 Replies
View Related
Dec 28, 2010
I was wondering how can I do below statement in oracle
update table1 t1 set t1.column1 = t2.column2
from table2 t2 inner join table3 t3 on t3.column3=t2.column4
where t3.column5 is null
I tried read up merge and update, but not really understand how the syntax works in oracle.
View 2 Replies
View Related
Apr 20, 2012
I would like to have column data as column headers.
Tables:
skill_table
SKILL_ID | NAME
3431060 | Stomach
3431064 | Hand
3437806 | Finger
localnode_table (which actually has the order/alignment (like what is next and what is previous) of the name from skill table.
NODE_ID | PREVIOUS_ID | NEXT_ID
3431060 | | 3431064
3431064 | 3431060 | 3437806
3437806 | 3431064
How to make it appear like:
Stomach | Hand | Finger
3431060 | 3431064 | 3437806
View 3 Replies
View Related
May 26, 2013
I am trying to add a new column in a table and insert data from another column of same table.
alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit
As there are huge number of records in the POSITION table ...its taking for ever to execute this query.
View 1 Replies
View Related
Oct 7, 2011
I have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies
View Related
Apr 11, 2013
I downloaded a sample chart application from oracle website
[URL].......
it is working perfectly, but I want to know how it is build exactly. This is one of the queries,
"select null link,
task_name,
id,
parent_task,
start_date,
end_date,
decode(status,'Closed',100,'Open',60,'On-Hold',10,'Pending',0) status
from eba_demo_chart_tasks
order by project"
But I don't know where eba_demo_chart_tasks is stored, where can I find it!
View 2 Replies
View Related
Sep 26, 2011
how to get the column name for a particular column value.example:
create table test(id number,col2 varchar,col3 varchar);
insert into test values(1,'true','false');
insert into test values(2,'false','true');
commit;
i want to select column name for data 'true' having id=1;
i.e) the answer must be 'col2'.
View 2 Replies
View Related
Nov 5, 2012
I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.
Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?
View 1 Replies
View Related
