PL/SQL :: Simple Character Sorting Of Results

Jun 8, 2012

I have three SQLS which I am running using "Union"

e.g

select col1,count(*) from table where type ='parts'
group by col1
union
select col1,count(*) from table where type ='consumables'
group by col1
union
select 'Total',count(*) from table
group by 'Total'

I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!

View 5 Replies


ADVERTISEMENT

PL/SQL :: Sorting Of Character?

Feb 7, 2013

I got the following data for which I will like to sort in ascending order.

B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2
B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/10
B51-01-008/31012013/CRE1005/2

and expected result will be

B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/2
B51-01-008/31012013/CRE1005/10
B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2

I will like to sort it based on the order on second segment for which is date, the earlier appear first then only the rest.

View 11 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

how do we know database character set is either single character set or multi character set?

While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".

Below are steps taken to resolve the issue -

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

i got this error: ORA-12712: new character set must be a superset of old character set

below are the commands executed by me:

SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;

And its working...

I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?

View 11 Replies View Related

SQL & PL/SQL :: Rollup And Sorting

Jul 2, 2012

I have the following query

SQL>
SQL> SELECT DECODE(act.statement_flag, NULL, 'Total',act.statement_flag) "Monthly Statement?",
2 COUNT(account_id) "Count",
3 ROUND(((COUNT(account_id)/
(SELECT COUNT(account_id) FROM accounts
WHERE account_status_id = consts$.acct_sts_active))* 100),2) || '%' "Percent of Active Accounts"
4 FROM accounts act
5 WHERE act.account_status_id = consts$.ACCT_STS_active
6 GROUP BY ROLLUP(act.statement_flag)
SQL> /

Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
N 1892 98.08%
Y 37 1.92%
Total 1929 100%

I want the output to be in the following fashion:

Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
Y 37 1.92%
N 1892 98.08%
Total 1929

And I would also like to know is there another way to find the Percent of Active accounts in the query.

View 3 Replies View Related

Sorting Strings As Integers

Jun 10, 2007

The db field is a string-type field that hold strings such as:

'1234'
'753'
'textstring'
'345'

Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'

My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'

Is there a quick and dirty SQL-only way to doing this in Oracle?

View 3 Replies View Related

SQL & PL/SQL :: Sorting Hierarchy Query

Jun 10, 2013

Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)

Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5

Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3

Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3

Please check attached file for an example.

View 8 Replies View Related

SQL & PL/SQL :: Sorting VARCHAR2 Field

Mar 23, 2010

sort data set A,B,1,2,A1,A2,B1,B2,2B,1000 as A,B,1,2,1000,A1,A2,B1,B2,2B.

I tried with LPAD and EXPREG_REPLACE funtion. But it did not work.

View 13 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 :: Sorting Collection In Nested Table

Sep 11, 2012

I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.

-- PLS-00642: local collection not allowed in SQL statements

-- PL/SQl: ORA-00902: invalid datatype

The error message are also noted below in the code on the line that fails.

A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.

IMPORTANT: The code is running in an enviroment that does not have permission to create.

Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Developer: 8.0.0.1480

DECLARE
-- Declare the variable MyTID to be used by select statements and
-- set its data type and max character count
MyTID varchar2(10);
[code]....

View 13 Replies View Related

SQL & PL/SQL :: Sorting Nested Table / Collection

Nov 10, 2011

In test.pks file I declared the following type

TYPE tab_tests is table of NUMBER(15);

In test.pkb, I have the following procedure

PROCEDURE report (
i_cid IN NUMBER
)
IS
test1 tab_tests := tab_tests();
test2 tab_tests;
BEGIN

-- I populate test1 with the data and it works fine. But when I tried to make a sorted nested table with the following command.

I got this error 'ORA-00902: invalid datatype',

a nested table than the following, like a built in Oracle collection method?

execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;

View 16 Replies View Related

Reports & Discoverer :: Sorting On Different Columns

Sep 10, 2012

I have report where there are multiple columns and requirement is user may choose his own sorting order from form, any sample .rdf.

CREATE TABLE EMP (EMP_CODE VARCHAR2(12),EMP_NAME VARCHAR2(20),SAL NUMBER);
INSERT INTO EMP VALUES ('1','A',10);
INSERT INTO EMP VALUES ('2','C',3);
INSERT INTO EMP VALUES ('3','B',20);

[Code]..

--note order by is changing, how its doable in report 6i.

View 2 Replies View Related

Reports & Discoverer :: Sorting Order

May 18, 2011

1) eno list values ASC , DESC
priority Column : 2

2) DNO List Values ASC , DESC
priority column : 1

3) DTNO List Values ASC , DESC
priority column : 3

So i need the sorting order output like this
LIST VALUES ASC (OR) DESC USER SELECT AT RUN TIME ANY VALUE.

ORDER BY DNO DESC , ENO ASC , DTNO ASC

* The Priority is user will enter at run time based on the number the column should be sorting.

View 6 Replies View Related

SQL & PL/SQL :: How To Insert Special Character / ORA-00911 / Invalid Character

May 25, 2010

We have Oracle 10g and user trying to Insert following in one of our table field and getting error:

'REVOLUCIÃ"N Historical Corruptions Agenda'

Getting follwoing error:

ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');

View 5 Replies View Related

Forms :: Sorting Values In List Item

Dec 15, 2010

I have two list items and from left list item values are populated to right list item through Add and Remove buttons and vice versa.My requirement is

1.) I need to sort the values of list item whenever a new element is added to the list item.

View 1 Replies View Related

Reports & Discoverer :: Selective Sorting On Report Group?

Dec 11, 2010

I have a Group report based on one group.For eg..there is machine (Group) and it has detail records .The problem is there are certain specific machine on which i want sorting to be done the rest will be not sorted.

machine WX
Details not be sorted
machine sh4
Details to be sorted
machine sh5
Details to be sorted
machine AN1
Details not to be sorted

View 12 Replies View Related

Reports & Discoverer :: Sorting Data And Hidden Frames

Dec 15, 2011

Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.

View 3 Replies View Related

Application Express :: Combined Date Sorting With Anychart

Apr 25, 2013

I have a table in which I extract the year and the month and both in combination (from a date type). This is needed to summarize for example the year in a chart.

I need the combination of month and year because people could do a query which switches through years. For expample: From 02.2012 to 03.2013.My problem is I dont know how to tell anychart to sort the dates. I have already tried:

to_number(to_char(testdate,'YYYY')) as Year,
to_number(to_char(testdate,'YYYY'))||to_number(to_char(testdate,'MM')) as MonthyearApex now sorts:

20121,201212,20122

I would like that apex realizes to order:

1.2012,2.2012,3.2012 .........01.2013,02.2013.03.2013

The report itself has now four date relating collums:

1) Date (Type Date)
2) Year (Type Number)
3) Month (Type Number)
4) Monthyear (Type varchar)

View 3 Replies View Related

Inserting Character From Extended ASCII Character Set?

Mar 8, 2007

I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like

update table1 set company_name='blah�" where company='blah'

It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?

View 3 Replies View Related

PL/SQL :: How To Select String Between Nth Specific Character To Nth+1 Character

Aug 9, 2012

I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'

I want to get the string between second underscore and third underscore i.e. 'MEGAMART'

How to achieve this in sql statement?

View 4 Replies View Related

Globalization :: Character Encoding - LATIN-1 Character In UTF-8 DB?

Feb 9, 2013

I am using C++ OCI LIB, to insert some report data from remote OCI client to oracle 11 server. This data is read by another process to create the report.The DB CHARSET is UTF-8. But the report tool expects the data to be ISO08859-1 encoded. So while inserting the data into the database i specify the following LANG and CHARSET for my table colulmn in client:

The TARGET DB CHARSET is UTF-8
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
size_t csid = 871; // UTF-8
OCIAtrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND,
*(void *)&csid*,
(ub4) 0,
(ub4)OCI_ATTR_CHARSET_ID, errhp);

This solution works for almost every case of ASCII and Extended ASCII Charest but we are facing issues if we have few specific characters to be inserted.f we are trying to insert single beta character [β] through client, the data goes empty to the column.

Beta Character details:
DEC     OCT     HEX     BIN     Symbol           Description
223     337     DF     11011111     ß     Latin small letter sharp s - ess-zed

DB Output after insert single β:
select rawtohex(NAME) from PERSONS where EID=333;

RAWTOHEX(NAME)
---------------------------

But if the string is *"ββ"* everything work fine:
DB Output for "ββ":
select rawtohex(NAME) from PERSONS where EID=333;

RAWTOHEX(NAME)
---------------------------
DFDF

View 6 Replies View Related

XML DB :: Simple Node With Attribute?

Jan 4, 2013

1. i register scheme like this
DECLARE
  schemaurl VARCHAR2(256);
  xmlschema xmltype;
BEGIN

[code]...

/2. i try to insert XML
insert into TMP_SPM_ACTLOGLINE values (xmltype(
  '<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     
[code]...

The question is how should the scheme look like to insert this XML?

View 5 Replies View Related

Reports & Discoverer :: Oracle Report 10g - Sorting In Break Order

Nov 30, 2010

Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.

There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,

it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.

View 6 Replies View Related

Simple Update Query Not Returning More Than One Row?

Sep 9, 2009

I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:

update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)

It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.

View 2 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

SQL & PL/SQL :: Compilation Error With Simple ELSIF

Mar 12, 2010

what I don't get is what is wrong with my ELSIF statement? Majic program that will guess how much you are worth

ACCEPT s_first PROMPT 'Enter your random letter'
DECLARE
random_letter VARCHAR(20);
personal_worth VARCHAR(20);
BEGIN
[code]....

View 6 Replies View Related

Forms :: How To Design A Simple Graph

May 23, 2011

How to design a Simple Graph?

View 1 Replies View Related

SQL & PL/SQL :: Oracle SQL Simple Table Verification?

Feb 27, 2012

I'm taking my first SQL class and trying to replicate the code in this picture. I'm wondering if the code is space-sensitive in certain areas

For example, in the very last line, does it matter if I have:

CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_T(PRODUCT_ID));

vs.

CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_T (PRODUCT_ID));
(where the second one has a space between PRODUCT_T and (PRODUCT_ID));

Also, I don't have Oracle SQL on my computer.

CREATE TABLE CUSTOMER_T
(CUSTOMER_IDNUMBER(11,0)NOT NULL,
CUSTOMER_NAMEVARCHAR2(25)NOT NULL,
CUSTOMER_ADDRESSVARCHAR2(30),
CITYVARCHAR2(20),
STATEVARCHAR2(2),

[code]....

View 7 Replies View Related

ODP.NET :: Ora-00936 With Simple Select Statement?

Dec 19, 2012

I get "ORA-00936: missing expression" when I issue the following SQL:

select 4 - .2 from dual

from the following VB.NET code (3.5 framework):

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))

I have the following installed when connecting to 10.2.0.3 database:
11.2.0.1 client installed
11.2.0.3.20 ODP installed

This is really strange behaviour as the following SQL variants work:
select 4 - 0.2 from dual
select 4 - '.2' from dual
select -.2 + 4 from dual

This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11

View 11 Replies View Related

SQL & PL/SQL :: Using UTL_SMTP To Send Simple Emails

Aug 2, 2006

I tried using UTL_SMTP to send simple emails. My code is as follows:-

PROCEDURE prc_send
IS
- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;
BEGIN
-- establish the connection to the smtp server
[code].........

When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated. I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.

View 9 Replies View Related

Write Simple Sql Which Would Delete Data From Last N Months?

Apr 20, 2009

am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate

e.g

Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May

View 3 Replies View Related







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