SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table
Jul 18, 2012
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......
View 6 Replies
ADVERTISEMENT
Feb 3, 2012
What I am trying to do is print out page that displays all of the column titles and the data under them for a query given by the user. It is then going to be put into an excel spreadsheet.
I've done this before with Java, simply by using the getMetaData function, but I can't seem to find an alternative for PL/SQL. It seems at the very least I need to know the number of columns in a query, but that would defeat the purpose of this.
Is what I am trying to do even possible or is knowing the column names an absolute necessity when printing table data?
View 9 Replies
View Related
Sep 12, 2012
I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.
I used dba_constraints view. Please find below details.
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
2 where TABLE_NAME='DSET_PRODUCT_S';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------------------
SCOTT SYS_C10202456 C EMPLOYEE
SCOTT SYS_C234576 C DEPT
View 6 Replies
View Related
May 9, 2012
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attachment for table creation and table data.
The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:
select completed, count(completed) as theCount from tbl_completed group by completed;
COMPLETED THECOUNT
Y 772
N 720
I need a result set to look like this:
COMPLETED,Y,N
THECOUNT,772,720
The best solution that I have discovered is this, but I figuring their might be a better way.
select
(select 'THECOUNT' from dual) as COMPLETED,
(select count(completed) from tbl_completed where completed = 'Y') as Y,
(select count(completed) from tbl_completed where completed = 'N') as N
from dual;
COMPLETED,Y,N
THECOUNT,772,720
View 3 Replies
View Related
Apr 28, 2013
I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.
View 1 Replies
View Related
Mar 6, 2013
I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,
Note: Here org_id is the column name .
Provide me sql code or procedure to achieve this.
View 2 Replies
View Related
Apr 9, 2012
Why Oracle allowing the Built-in function names as column name in data base table
Ex :
create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))
View 3 Replies
View Related
Feb 1, 2013
I have a schema DEF and I have a column_name CREATE_DATE.
I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.
View 5 Replies
View Related
Aug 11, 2011
base on performance it is better to retrieve data from view or mention the table names directly?
I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view
View 9 Replies
View Related
Mar 1, 2010
I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.
Table1:
col1col2category
A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1
Table2:
colAcolBActive
A10Y
A20N
B30Y
B40N
Z35Y
Table3:
STUDENTSCORE
A20
B40
C10
D20
J30
Z35
query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.
View 8 Replies
View Related
Jul 2, 2012
join two oracle tables for fast fetch of data from table.
View 1 Replies
View Related
Jul 30, 2012
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
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 IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
View 10 Replies
View Related
Oct 19, 2010
Just wanted to know What is the name of the table where all table names are stored?
View 2 Replies
View Related
Nov 14, 2010
it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac 2.111.7.20.
this is my .NET code (which works fine on every environment it was installed in the last 2 years):
using (OracleConnection sqlConn = new OracleConnection())
{
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";
[code].......
this is the oracle procedure:
PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;
The problem that we saw was that on one of the client machines dr["Name"] threw exception :
Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)
We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])
How can it be? from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?
View 4 Replies
View Related
Jul 7, 2010
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
View 1 Replies
View Related
Sep 19, 2011
I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.
Here is what I have:
selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id
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
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
Jan 1, 2012
How to Insert all user names into a table from sql for example when i use select * from all_users command it displays all user names i want that how we transfer these user names into a table.
View 11 Replies
View Related
Oct 5, 2010
Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns
1.Dept
2.sales_amt
3.status
There are 3 different flag for status A,B & C.I want display data in the following format in five columns.
Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30
total 40 110 50
But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.
For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is
select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept
Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like
Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30
So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.
View 2 Replies
View Related
Feb 7, 2011
Is there a query that will return all the table names that are not populated with any data within a Userid/Schema?
View 2 Replies
View Related
Jul 3, 2011
how to sort the names in table without using group by command?.
View 3 Replies
View Related
Jul 4, 2012
In details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)
output should be it should take only first value from ().
View 2 Replies
View Related
Jul 28, 2010
I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.
UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'
I am getting the error "ORA-00933: SQL command not properly ended"
View 9 Replies
View Related
Apr 17, 2013
We had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
Edit/Delete Message
View 6 Replies
View Related
Mar 28, 2013
I have to move the data from a Varray column to a table.
Lets say I have created a Varray as described below,
create or replace TYPE "BT_TYPE" AS OBJECT (
R_ID NUMBER,
P_EVENT VARCHAR2(100))
/
create or replace TYPE "BT_VR" AS varray(20) of BT_TYPE
/
And I have a used this Varray as the column datatype in table
Create table BT_MASTER(
BT_ID_SEQNUMBER(5),
BT_DETAILBT_VR);
And this table contains data.
I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.
Create Table BT_STG(
BT_STG_ID NUMBER(5),
R_ID NUMBER(5),
P_EVENT VARCHAR2(100)
);
One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.
Is there any other simpler way by which I could create a table similar to a VARRAY/Object?
Something similar to,
create table test as select * from BT_VR
View 4 Replies
View Related
Jul 19, 2012
I created a test table with 4 columns(id, first_name,last_name, salary-number ) and then alter table to encrypted salary column . everything is OK.
I insert values into test table. However, I still can see salary value by select SQL.
What is wrong?
my db is oracle 11.2.01 in 2008 SP window
newdba
View 7 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
Apr 7, 2011
I have question on the following, that gets defined for the bad file and the log file
BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'
What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..
l_badfile := file_nm || '.bad' ;
View 3 Replies
View Related
Jun 28, 2012
i'm working on sql developer in that i'm able to access tables if particular table name is given..
i don't understand is there any grant role problem .
View 11 Replies
View Related
