SQL & PL/SQL :: Order By Alphanumeric?

Aug 1, 2012

create table x(
sno varchar2(5)
);

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');

SQL>select * from x;

SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3

How can I select it ike this

1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: Check For Alphanumeric Values

Sep 8, 2011

i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.

Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Sort Alphanumeric Data

Nov 20, 2008

I have an urgent request which is pending with the following problem.

Problem :

I have a table which contains data of various datatypes like alphanumeric,varchar and number.

Now my query is " how to sort the data of the table using alphanumeric field"

How to select the data in a required(MyRequirement) sort order.

Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1

[Code]....

afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11

[Code]...

MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10

[Code]..

Means it has to sort the data order by lefthand side of PI and also righthand side of PI.

Pls check the attachment if you are not getting the above data in correct order.

View 22 Replies View Related

SQL & PL/SQL :: Alphanumeric Sequence Generator

Apr 12, 2010

Is there a function or a process to generate and maintain an alphanumeric sequence?

I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.

so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.

Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.

EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).

I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.

However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)

How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.

I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.

My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.

View 5 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: Alphanumeric Sorting Column Base

Jun 18, 2010

I am trying to sort columns base on cluster and alpha numeric field.

Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1

Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this

Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1

View 9 Replies View Related

SQL & PL/SQL :: Select Only Rows Which Has Number And Not Alphanumeric Value?

Oct 16, 2012

I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since

View 11 Replies View Related

SQL & PL/SQL :: Last Numeric Character In Alphanumeric String

Feb 8, 2011

How can I find the last numeric value in the alphanumeric string?

Example:

LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2

View 9 Replies View Related

SQL & PL/SQL :: How To Validate Given String For Alphanumeric Character

Feb 14, 2011

To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.

I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

Here is the DML and DDL.

[code]

create table abc ( classid varchar(10));

insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");

[code]

Output:

Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F

View 6 Replies View Related

SQL & PL/SQL :: Exception Query - Alphanumeric Input?

Dec 27, 2012

I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.

I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.

I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?

DECLARE
v_sal NUMBER (12) := '&Enter_salary';
v_last_name VARCHAR2(10);
BEGIN
SELECT last_name

[code]...

View 3 Replies View Related

Forms :: How To Validate Alphanumeric And Symbol In Textbox

Nov 12, 2012

I have created a form that have a button to check the character in the textbox is alphanumeric and symbol. Here attached the code, that i tried from other post.

View 5 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

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.

View 5 Replies View Related

SQL & PL/SQL :: Order By With IN

May 24, 2010

Its a very small query

SELECT * from EMPLOY
WHERE fk_dept_id IN ( select id
from DEPARTMENT
ORDER BY END_DT DESC)

I cant use IN.

NOTE: Select * should be done from Employ only no joins and all.

View 10 Replies View Related

SQL & PL/SQL :: ORDER BY In A Particular Order

Jul 17, 2012

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have data like:-

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2011081CC
171350 2012041WA
171350 2012031WW
171350 2011081WW
171350 2011081CR
171350 2011081CA

The possible last two characters of the iss_nbr can be:-

WW, WR,WA,CR,CA,CC

And I want it to be ORDER BY as follows.

WR
WA
CR
CA
WW
CC

So for example, in above case, it should be

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2012041WA
171350 2012031WW
171350 2011081CR
171350 2011081CA
171350 2011081WW
171350 2011081CC

How can I do it while loading the table?

View 11 Replies View Related

SQL & PL/SQL :: Order By Row ID

Mar 20, 2012

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 ?

View 2 Replies View Related

Select Vs Order By

Nov 9, 2006

In a query which gets executed first:

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;

View 1 Replies View Related

Tab Order On A Form

Feb 25, 2010

I am having trouble with tab order on a Form.

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.

View 1 Replies View Related

SQL & PL/SQL :: Use Of Order By In Varchar2

Feb 21, 2011

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

1,10,11,2,....like that.

View 14 Replies View Related

SQL & PL/SQL :: Order By Clause?

Mar 12, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Order By A Column

Jun 17, 2013

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

View 13 Replies View Related

PL/SQL :: Order By Month Name

Jun 20, 2012

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?

View 7 Replies View Related

PL/SQL :: Order By With Union?

Aug 24, 2012

I have the following sql

select htf.escape_sc(su.sukey) a, htf.escape_sc(su.sukey) b
from udm_su su, udm_lde lde
where su.ldeid = lde.ldeid
and su.sukey in (select su_generic
 
[code]...

View 3 Replies View Related

PL/SQL :: Order By On Datafile Name

Sep 4, 2013

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

output. /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data23/sans01.dbf 

should be something

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  

View 16 Replies View Related

SQL & PL/SQL :: Order By For VARCHAR2

Sep 28, 2010

create table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/

[Code]....

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105

[Code]...

Function created.

SQL> select * from members
2 order by safe_to_number(roomID);

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4

[Code]....

How can I sort the output to get the result like below

2007209 1 1
2006253 1 1-A
2006253 1 1-A

[Code]...

and so on...

View 13 Replies View Related

SQL & PL/SQL :: Order By Month

Jan 31, 2013

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');

View 8 Replies View Related

PL/SQL :: How To Order By The Column

May 28, 2013

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?

View 11 Replies View Related

PL/SQL :: First Clause With Order By

Apr 29, 2013

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.

procedure
display_list(manager_id in varchar2)

Output is:
emp_id
manager_id
performance_rating

View 3 Replies View Related

Order By In Oracle With Priority?

Nov 23, 2012

I have a table (emp) with three columns in ORACLE:

COMPANY OFFICE EMP
------------------------
9999 00001 emp1
9999 00001 emp2
9999 00002 emp3
9999 00002 emp4

[code]...

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 don't know how to do.

View 2 Replies View Related

Tuning With Order By Condition

Sep 9, 2010

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.

View 4 Replies View Related

Group By With Order By For Dates?

Mar 9, 2013

ii have written a query like thi:

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

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved