I have the following issue i have two table
PRODUCT (id, product_name)
1, prod1
1, prod11
2, prod2
3, prod3
OSS(id, oss_name)
1, oss1
2, oss2
what i want to return is the product_name and the oss_name for each id.
1, prod1, oss1
1, prod11,
2, prod2, oss2
3, prod3,
I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.
I know simple method Like this :-
column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.
Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.
I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are
where owner NOT IN ('SYS','SYSTEM') and data_type IN ('CHAR','VARCHAR2','NUMBER')
My query as follows
select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
I was looking for application column name corresponding to "Draft Invoice Number" ra_interface_lines_all table.
I tried the below.
/* Get descriptive_flexfield_name for the application table name*/
SELECT *
FROM FND_DESCRIPTIVE_FLEXS_VL
WHERE application_id = 222
AND APPLICATION_TABLE_NAME=upper('ra_interface_lines_all');
/* Get the application column name and end user column name*/
SELECT *
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE application_id = 222
AND descriptive_flexfield_name = 'RA_INTERFACE_LINES';
There are many DESCRIPTIVE_FLEX_CONTEXT_CODEs obtained. I could finally trace out that draft invoice number corresponds to INTERFACE_LINE_ATTRIBUTE2. How can I know what DESCRIPTIVE_FLEX_CONTEXT_CODE should I look for?
I want to build a single query to fetch the application column name and flex field name for a specific table .
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
query for below requiremnet :-
I have table A having column name Varchar2(10), Seq Number ,Address varchar2(20)
Data in table as
name SeqAddress
A1bangalore
A2karnataka
A3India
B1Mumbai
B2Maharastra
B3India
I need to write query to get below Output
Abangalore,karnataka,India
BMumbai,Maharastra,India
I can not use any inbulit function of oracle like "SYS_CONNECT_BY_PATH", "LIST_AGGR" or any other function , even can not use any user defined function.Need to write only SQL to get this result.
how can we get above result
I need to query a table to read the value, specifically a date, in one column and characters in another, i.e. and ID number, and populate a new column with new data. For example:
If Column A = 1/1/2009 and Column B = 0123 in table 'Persons' I need a query statement to populate Column Z with a 'Yes'
If Column A = 2/1/2009 and Column B = 9876 in table 'Persons' I need a query statement to also populate Column Z with a 'Yes'
How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)
CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),
[Code]...
After the Data is loaded, we see data look like the above.
(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.
If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?
Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL
[Code]....
i have a database in which some tables. Now i want to alter column by using this query.
ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))
this column properties is QTY NUMBER(5)
now i want to convert it into QTY NUMBER (5,2)
How to add column 'col_new' in below table after the column 'col1'?
Table name: ofq1
table structure.
columnname datatype
------- -------
COL1VARCHAR2(20 BYTE)
COL2NUMBER
COL3DATE
COL4DATE
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
Query : select * from scott.dept where deptno=10;
Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK
But i want the above resultset as below,
COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from
2 (select deptno,job,sal
3 --,comm
4 from emp)
5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER'))
6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from
2 (select deptno,job,sal,comm
3 from emp)
4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER'))
5 order by 1;
[code]....
I want result like below.
DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
10 SAL1300 2450
20 SAL1900 2975
30 SAL 950 5600 2850
30 COMM 2200
is it possible to have multiple column pivot query.
I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
i.e. I want "column4" to read "CountofCalId"
If a session runs a query, the status will be inactive after completing the database call. I know this concept. But I just want to know what will be session status, if it running a procedure contains many select queries, DML quries and loop? Whether it will be active until procedure completes or it will be switching active and inactive?
View 3 Replies View RelatedI am running a query in Oracle that uses multiple joins. I want to return as a part of the select statement a column that contains the row number of the result.
Here is an example of my query;
SELECT *
FROM A_TABLE
INNER JOIN THEADM.A_TABLE_EXTENSION
ON THEADM.A_TABLE_EXTENSION.OBJID = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_ENV
ON THEADM.A_TABLE_ENV.A_TABLE_ENV2A_TABLE = A_TABLE.OBJID
INNER JOIN THEADM.A_TABLE_LOG
ON THEADM.A_TABLE_LOG.A_TABLE_LOG2A_TABLE = A_TABLE.OBJID
WHERE CREATION_TIME > '01AUG10'
I want the resulting query (after the final inner join) to contain a column named 'Column' (or similar) that contains the number of the row.
I cannot re-sort the result set - there is no criteria by which to do this. (which is why I want the row number so badly... so that I can sort the result set however I want later and return to the original using this row number) The way that it is returned from the system is the way that I need it so using OVER ORDER BY won't work for me here. I also can only read the database, I cannot INSERT or add any tables.
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij
SELECT OM.ORG_NAME||' Unit - ':p_unit name
FROM org_mst om
In the above query I want to concatenate Unit number passed by a parameter.
I have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:
select column1 from tablename where column2 = 'abc'?
Is there a way in a query/update/insert to refer to a column by column number rather than column name?
declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;
[code]....
I am trying to run the below query to extract the outline data from v$sql_plan(querying other_xml column), but i get the below error...this works perfectly fine on 10.2.0.4 + version...but on 10.2.0.2 and and 10.2.0.1 i get the below error... how to fix it ?
SQL> @sql_hint
Enter value for sql_id: d15cdr0zt3vtp
Enter value for child_no: 0
extractvalue(value(d), '/hint') as outline_hints
*
ERROR at line 2:
ORA-00904: "D": invalid identifier
the query is taken from below...[URL]....
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
[code]...