I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.
View 2 RepliesI know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.
I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.
I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.
Emp Name
--------
Sam
John
Noel
Alen
Saaem
output would be
---------------
Alen
John
Noel
Saaem
Sam
Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.
select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END
Note: DESC or ASC is dynamic coming from java-code.
How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.
View 10 Replies View RelatedI'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
);
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
I have a master detail form that presents a list of 22 items that can be checked or unchecked depending on whether the test is needed.
The issue I have is trying to order or sequence the list in a specific order without a database column.
Here is the code I am trying to use in a post query trigger:
CASE :SAMPLE_TESTS.TESTCODE
when 'L001'
then :SAMPLE_TESTS.SEQ := '02' || :SAMPLE_TESTS.TESTCODE;
when 'L002'
then :SAMPLE_TESTS.SEQ := '03' || :SAMPLE_TESTS.TESTCODE;
when 'L003'
then :SAMPLE_TESTS.SEQ := '04' || :SAMPLE_TESTS.TESTCODE;
[Code]..
The non-database field is character with a length of 6.
When I try to use this field in the order by property of the data block it is unable to perform query.
Is there another way?
I need to sort values inside a column. Data in column is concat with '|' (pipe). There could be 1 to many values in one column.
The order of the rows is not relevant here.
Test case:
-------------
create table t1 (col1 varchar2(100));
insert into t1 values ('H302|H411|H317|H314|H312');
insert into t1 values ('H315|H410|H400|H318');
insert into t1 values ('H226|H400|H331|H318|H317|H315|H310|H301');
insert into t1 values ('H301');
commit;
select col1 from t1;
COL1
----------------------------------------
H302|H411|H317|H314|H312
H315|H410|H400|H318
H315|H318
H301
H226|H400|H331|H318|H317|H315|H310|H301
Need is this:
COL1
----------------------------------------
H302|H312|H314|H317|H411
H315|H318|H400|H410
H315|H318
H301
H226|H301|H310|H315|H317|H318|H331|H400
am using a scalar subquery in order to sort by department_name column. My sql statement is like this:
select employee_id,last_name
from employees e
order by
(select department_name
from departments d
where d.department_id= e.department_id)
i want to display also the department_name in my output.
select employee_id,last_name,
(select department_name
from departments d
where d.department_id= e.department_id) department_name,department_id
[Code]...
the result is sorted and name is also displaying.but am not getting the output properly.
result is like this:
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME DEPARTMENT_ID
--------------------------------------------------------------------------------------------------------
205 Higgins Accounting 110
206 Gietz Accounting 110
200 Whalen Administration 10
107 Lorentz IT 60
105 Austin IT 60
103 Hunold IT 60
104 Ernst IT 60
106 Pataballa IT 60
am getting the first three line.but as 5 worker are in IT department so in that case how result is sorted..
I have below tables,
describe rpthead
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.
How can I Sort Ascending or Descending of Last Total Column of Matrix Report R6i i.e. F_SumsalPerempno, summing total salary of each empno at end of each row.
I need the employee paid highest amount of total salary during the year to appear on first row, while months to display as per original order.
I am trying to ultimately as the title says separate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the
SELECT 'First Entry' Asset FROM Dual Union
SELECT 'Second Entry' Asset FROM Dual Union
SELECT 'Third Entry' Asset FROM Dual
and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.
Today I’m trying to make a “table” from a column. The information I’m using looks like:
AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ - K
I need my table have these columns:
Column1 Column2 Column3
AAA B CCC
DDD E FFF
GGG H III
JJJ K
So I began to divide the string into smaller pieces:
SELECT LEVEL reg,
REGEXP_SUBSTR('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K','[^|]+', 1, LEVEL) ferroc
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K') - LENGTH(REPLACE('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ–K','|')) + 1; What I get is:
>
REG ferroc
1 AAA-B-CCC
2 DDD-E-FFF
3 GGG-H-III
4 JJJ - K
>
After this step I’m lost, I try to do almost the same thing than in the first query but mi information get mixed.
I want to create a column which will be having unique values but while selecting data from table i am not willing to display same column in output.
SQL> create table temp
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 );
Table created.
SQL> insert into temp values (1,2,3);
1 row created.
SQL> select * from temp;
C1 C2 C3
---------- ---------- ----------
1 2
I want C3 as a hidden column and while selecting like
select * from temp;
expected output is...
C1 C2
---------- ----------
1 2
My main target is, C3 column should not display in select * ... stm but column with data should be there in table.
Is it possible to create a table without any single column in it?
View 1 Replies View Relatedcreate trigger on certain column for table structure.
SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....
I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .
Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)
Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column
It shows me error when i create a table with column name as UID.
Is UID a inbuilt function or anything else?
I am trying to write a script to create a table with the author column default value as "Administrator" I use this execute immediate statement1;
Why does it create the table successfully with ',AUTHOR VARCHAR2(30) DEFAULT 1' and NOT with ',AUTHOR VARCHAR2(30) DEFAULT Administrator' ?
I want to set the COLUMN DEFAULT have to "Administrator" when I create the table.
When I try character I get this error "ORA-00984: column not allowed here"
statement1:='CREATE table TEST_ID2'
||'(TEST_ID NUMBER(6)'
||',test_name VARCHAR2(40)'
||notnull||
',date_created DATE DEFAULT SYSDATE '
[code]....
how to sort the names in table without using group by command?.
View 3 Replies View Relatedthe reason behind the below statements:
1) We cant create TABLE PARTITIONED on CLUSTER or INDEX on CLUSTER TABLE.
2) We cant create a partitioned table with the column of LONG or LONGRAW? (But how it could be possible with BLOB, CLOB?
I am one table create tt1(A varchar2(6),B number(5));
insert data in this column
tt1
A B
---------------
F1 1
F365 2
F10 3
F65 4
F55 5
Q. how to get order by a column. i wan't to
tt1
A
--------------------
F1
F10
F35
F65
F365
Q. how to get order by a column , i wan't to
tt1
A
--------------------
F0001
F0010
F0035
F0065
F0365
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 know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
But how do I DELETE these same rows?
In sql server we can able to change the order of a column but some says in oracle also we can do it. But as i think it is not possible.
View 3 Replies View RelatedI am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.
select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments
where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC
Above SQL doesn't gives the output in proper order ,
Oracle version : 11.2.0.2 Linux EL6 server
I have a query like below:
select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union
[Code]...
Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.
But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).
I want every output in dd/mm/yyyy format so I need to use to_char function.
But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.
10g- 10.2.0
This is my query
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
I have to set a matrix report as follow:
- in the rows the machine_id.
- in the column the material_lib.
Table material ( material_id,material_lib,ordre)
- in the cells quantity.
I get successfully my matrix through the wizard of report, but i should display the columns sorted with the field ordre.
how to get the columns of the matrix sorted.
I am trying to change the column order in an Interactive Report. Run the report as a developer then use the action menu.
However, I am not sure how one can run the the report as a developer. Do you just login and run the page? Then if I do that, I don't see the action menu there. If I go to page attributes "Save Report" tab, I do see the action menu but "Select Columns" only gives me "Report Type", "Report Name" etc as columns. Or we don't have enough privileges to run the report as a developer?