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.
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?
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
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;
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.
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
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.
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.
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?
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.
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
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?
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.
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.
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
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.
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
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.
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..
