I am having an issue with clob_agg...ordered by a field.
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
[Code] .......
Here I need clob concatenation based on ordered assignment name...
op should be based on title in alphabetical order(1st character)..
For 102 it should be
Grad Assist, Secretary, Teaching
G,S,T -- Alphabetical order
Code I tried : (didn't keep the data in the order)
with test_asg as (
select 100 ASSIGNMENT_ID, 'Comp info' TITLE, 'AP' EMP_CAT from dual union all
select 100,'Technician','AP' from dual union all
select 101,'Faculty','AP' from dual union all
select 102,'Teaching','CS' from dual union all
[Code] ......
Clobagg function :
create or replace
type clobagg_type as object(
text clob,
static function ODCIAggregateInitialize(
sctx in out clobagg_type
I have a question regarding a Date field on one of my form.
How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
I am having table without any primary key. In this table, only inserts and deletes are performed , no update operation.
Is it safe to use order by rowid on such a table ? Does by applying order rowid, is it possible to check order in which rows were inserted in this table ?
Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?
What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?
select empno,ename,rownum from emp order by rownum desc;
I rearranged the fields on a form, so I want the tab order to be different. I rearranged the fields in the object navigator to be in the order I want them to tab. I made sure the Previous Navigation Item and Next Navigation Item were set to null for all the fields.
Still it keeps the old tab order.
I also tried explicitly specifying the Previous Navigation Item and Next Navigation Item. Same thing, it kept the old tab order before I rearranged the fields. Previous Navigation Item and Next Navigation Item are both null.
There are no triggers on these fields. All fields are in the same block. I am using Oracle Form Builder version 9.
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.
insert into x values('A-1'); insert into x values('B-1'); insert into x values('B-2'); insert into x values('B-3'); insert into x values('1'); insert into x values('A-2'); insert into x values('2'); insert into x values('3'); insert into x values('A-4'); insert into x values('B-4'); insert into x values('C-4'); insert into x values('D-4');
I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:
SELECT DISTINCT CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date) [code]....
The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause
ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));
But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?
I am using 11GR2 and looking to find out a way , where in I able to extract the data files name in such a manner , where it lists data file in order of mount points. say data1 first and then so on.. Snippet from my data files
like /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data23/sans01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf
How I can see the order by employees,year wise and monthwise (month should start from january and so on..)
SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total FROM EMP GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');
I have a varchar2 column which can hold numeric values, and also varchar2 values.
The following select returns only the records which have a number value in that column
select * from my_table order by decode( replace(translate(valoare,'1234567890.','00000000000'),'0',''), NULL, valoare, 0 ) != 0;
But how can i order that column, so that first are the numbers (also ordered), and then the varchar2 columns (so all the rows of that table are selected, both varchar2 values and numbers in that column)? Is it possible?
I have a requirement where I need to display a list of employees, performance rating and manager id. I want to display the employees first whose manager is as manager_id as input parameter. So lets say a manager logs in, he should see his reporters first and then the rest. How can I implement this in SQL? I am trying first clause with order by but not sure how to do that here.
I want to get value in EMP column with following statement: ------------- SELECT EMP FROM emp ORDER BY COMPANY (But 9999 is first), OFFICE (But 99999 is first). -------------
I have a query with order by clause, which takes 30 sec to execute with order by clause. And if i remove the order by clause it executes within 1 sec.
The column in the order by condition has index. but when i see Explain plan output. it doesn't show this index is being used. I tried to execute query with INDEX hint but still explain plan is not showing this index.
select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags" from p_in where order_date between '1-apr-11 and '31-mar-12' group by to_char(order_date,'mon-yyyy')
the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE
