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.
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 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'
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.
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
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,
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;
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...)?
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?
I 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
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;
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 (
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30)); insert into email_tbl('1@y.com'); insert into email_tbl('2@y.com'); insert into email_tbl('3@y.com'); insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
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 have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.
Insert Into Test1 values(10,'Thomas','Manager','LA'); Insert Into Test1 values(11,'George','Chairman','LA');
declare MTest1Test1%RowType; str1VarChar2(1000):='Name'; MValue1Varchar2(100); begin Select * Into MTest1 from Test1 where ID=10; str1:='Select MTest1.'||str1||' from dual'; execute immediate str1 into mvalue1; dbms_output.put_line('mvalue1 '||mvalue1); end; /
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier ORA-06512: at line 9
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.
I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.
For that i have used a ref cursor to open and insert the table.
In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn
find below the sample
TYPE ref_csr IS REF CURSOR; insert_csr ref_csr; v_select VARCHAR2 (4000) := NULL;