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!
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?
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.
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?
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)
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
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.
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
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.
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:
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?
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;
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?
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.
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.
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));
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
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.
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
