Converting Negative Numbers To Signed EBCDIC?
Dec 15, 2010
I am working on a script that is reading from a numeric column converting it to a character string where the decimal is dropped (assumed) and the value is padded with leading zeros to fill the specified lengh. Using to_char(payment_amt,'FM0000000V00') is working fine for non-negative values. When the value of the field is a negative number the output includes a leading - (i.e., -000002458).
For the negative numbers I need to convert them to a 'Signed EBCDIC' value, where the last digit is converted to a specified character (in the above example it would be changing the 8 to a Q) and also replace the - with a zero, resulting in 00000245Q.
What I am doing is generating a fixed-length record with values in a specific location. What I'm getting now looks like this:
AAAA 000002358 BBBBBBB
CCCC -000002458 DDDDDDD
EEEE 000024555 FFFFFFF
And what I need it to look like is:
AAAA 000002358 BBBBBBB
CCCC 00000245Q DDDDDDD
EEEE 000024555 FFFFFFF
View 1 Replies
ADVERTISEMENT
Jul 13, 2011
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
View 7 Replies
View Related
Jan 9, 2012
I am doing a Gross Profit percentage. I want to display zero if the GP% is have negative value.
for example
If GP% shows -75 I want to display as 0.
trunc((sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))) / SUM(DECODE(TOTAL,0,1,TOTAL))*100,2) "GP%"
View 2 Replies
View Related
Apr 28, 2011
I have one column table with negative and positive values and want to display positive and negative values in different columns thru a single query.
Column
1
-4
3
-6
output should be like
A B
1 -4
3 -6
View 14 Replies
View Related
Apr 24, 2012
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
View 13 Replies
View Related
Jul 15, 2011
I found the data in DB with negative time as below
Quote:
1 SELECT
2 c.time
3 FROM partition c
4 WHERE
5* c.time < to_date('0001-01-01','YYYY-MM-DD')
SQL> /
DEACTIVATION_TIME
------------------------------------------------------------------------
17-JUN-08 04.06.22.893 PM
The data type for c.time is Timestamp.
When I use JDBC to retrieve the data and convert it to mill seconds, and it also shows as negative time. My question is the retrieved time doesn't show the negative date, but obvious it is a BC date and earlier than 0001-01-01.
1. How can we detect/show the date to show correctly for the BC date without checking c.time < to_date('0001-01-01','YYYY-MM-DD')? (I am wondering if there is format configuration to show the time like 17-JUN-08 04.06.22.893 PM BC, or something to show the time is negative )
2.how could we fix it negative time to positive ?
View 6 Replies
View Related
Nov 12, 2012
can you to display a field with negative value
S -- format mask display both sing + and -
MI -- format mask display negative sign at end
PR --Returns negative value in <angle brackets>. and Returns positive value with a leading and trailing blank.
I want the output should display only leading negative if the value is negative. I have used S99990.99 as format Mask. but need output should display only leading negative if the value is negative.
View 1 Replies
View Related
May 6, 2010
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
View 6 Replies
View Related
Jul 29, 2012
We are getting Negative values of Library cache hit ratio in AWR Report of 11g(11.2.0.3) with Solaris[tm] OE (64-bit). Why it shows negative value.
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.87 Redo NoWait %: 99.99
Buffer Hit %: 92.17 In-memory Sort %: 100.00
Library Hit %: -3,321.23 Soft Parse %: 81.95
Execute to Parse %: 92.88 Latch Hit %: 95.11
Parse CPU to Parse Elapsd %: 87.25 % Non-Parse CPU: 81.39
View 3 Replies
View Related
Aug 16, 2007
i have 2 tables
A B
id a.id
name
type
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name
1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
View 2 Replies
View Related
Aug 22, 2013
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Source row:
NOTE_IDCONTRACT_GRANT_IDPROSPECT_IDPROGRAM_CODE
1 1 1 786
2 2 2 786
Program:
SELECT
CASE
WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN
"PIVOT_SOURCE"."ID_NUMBER"
WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN
"PIVOT_SOURCE"."ID_NUMBER"
[code].........
View 3 Replies
View Related
Jul 14, 2012
I have two tables as follows:
TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12
TABLE_2
ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
View 1 Replies
View Related
Mar 24, 2011
I have simple program .It reads binary file and writes all , what reads into another file
WHILE my_lenght_help <= my_lenght LOOP
UTL_FILE.GET_RAW ( g_read_file , my_bufer_read ,200 );
UTL_FILE.PUT_RAW ( g_write_file , my_bufer_read );
my_lenght_help:= my_lenght_help + 200;
END LOOP;
my question. How I can change "my_bufer_read" between UTL_FILE.GET_RAW and UTL_FILE.PUT_RAW to make writable file ascii
View 3 Replies
View Related
Apr 27, 2012
I found this query in my sub version repository and really wondering how this working
select to_char(to_date(1000000,'J') ,'JSP') string_value from dual;
View 1 Replies
View Related
Sep 21, 2010
I am working on a POC. The goal is to do a
select * from function('input')
where the function dynamically returns different tables.
I have gotten to the point where I can return the cursor but I cannot format it as a table to use in the query. It return's it as XML. This is my function:
create or replace
FUNCTION CAMS_FUN_GEN_REPORTS(PARAM1 IN VARCHAR2) RETURN SYS_refCURSOR AS
BEGIN
DECLARE
p_recordset SYS_refCURSOR;
begin
OPEN p_recordset FOR 'SELECT * from STATS_FLAGGED_TOTALS_ME';
RETURN p_recordset;
END;
END CAMS_FUN_GEN_REPORTS;
This is my query:
select * from CAMS_FUN_GEN_REPORTS('')
So normally you would do this
select * from TABLE(CAMS_FUN_GEN_REPORTS('') as tabletype)
But I will never know the end result table as it will be dynamic so I have to find a way to cast the result of the function into a new table I can select on.
I have looked into pipe lining also but from what I can tell you still need to know the table definition. Which I can't know from the client side. The server will control the 'routing' and thus the end result of the select.
View 3 Replies
View Related
Sep 6, 2012
I have a row which contains 6 columns where I want that data to be shown in the form of columns as shown here:
From:
select item1, item2, item3, amt1, amt2, amt3 from item_table where sno=1; <----- returns 1 row as below
ITEM1 ITEM2 ITEM3 AMT1 AMT2 AMT3
---------- ------------ ------------- ----------- ------------ ----------
AAA BBB CCC 10.00 20.00 15.00
Data explanation: item1's (AAA) price is amt1 (10.00), item2's (BBB) price is amt2 (20.00) and item3's (CCC) price is amt3 (15.00). OK.
Now I want that data to convert into columns as shown here:
To:
ITEMS AMT
--------- ---------
AAA 10.00
BBB 20.00
CCC 30.00
I want a SQL to display this data.
I found one query which converts a row into columns, but this does not serve my requirement: [for your reference only]
SQL> select substr( the_string
, decode( level, 1, 1, instr(the_string,',',1,level-1)+1)
, decode( instr(the_string,',',1,level), 0, length(the_string), instr(the_string,',',1,level) - decode( level, 1, 0, instr(the_string,',',1,level-1))-1)
) the_value
from ( select (select item1||','||item2||','|| item3 from item_table where sno=1) ITEMS
from DUAL)
connect by level <= length(the_string)-length(replace(the_string,','))+1
View 11 Replies
View Related
Jun 15, 2012
We are able to insert CLOB into database, Using oracle Text I'm able to search inside clob. [two questions solved ]. The question arise when we need to send this data to application either as file or as text(varchar2).
I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
1)There is option to mount application partition inside db and export file over there but it is not viable option according to management.
2)I've Googled the solution to create JAVA API. Which will perform OS command like scp to send file from db to app(or any remote host). For security reason this option is also dropped.
3)I tried dbms_lob.substr but actual text inside clob is too long. File generated from CLOB sized around 5 MB.
So I guess it requires lot of effort if I wanted to convert into varchar2 as out parameter inside function.
So, to give CLOB data to developer as file or as varchar2. I'm not able to get any solution. Is there any other option using database to convert to string/varchar2 from CLOB ? Or do I need to drill down more into third option.
I'm having oracle 10.2.0.5.
View 2 Replies
View Related
Mar 4, 2012
I am converting from 10g to 11g app server and having problem with the exec_sql.execute command. In my 10g version, the code is:
Declare
lv_dbms_journal_cursor exec_sql.curstype;
lv_status INTEGER;
BEGIN
IF (exec_sql.IS_OPEN (lv_dbms_journal_cursor)) THEN
exec_sql.close_cursor(lv_dbms_journal_cursor);
[code].....
After this code caused the 11g forms session to crash, I modified the 11g version to the below, and it still crashes.
Declare
lv_dbms_journal_cursor exec_sql.curstype;
lv_status PLS_INTEGER;
connection_id EXEC_SQL.ConnType;
BEGIN
connection_id := EXEC_SQL.DEFAULT_CONNECTION;
[code]....
View 7 Replies
View Related
Oct 12, 2007
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
View 6 Replies
View Related
Jul 1, 2010
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
View 3 Replies
View Related
Jul 25, 2010
I have a column of datatype varchar2
this column may contain char or may contain number or may contain both
some values in this column are only number is there any function or any other way to get those column that are only number
View 3 Replies
View Related
Nov 1, 2010
Earlier, we had a discussion how to generate the permutations. generating the code for combinations
For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs
1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5
Total of 10 combinations
View 8 Replies
View Related
Apr 13, 2013
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
View 7 Replies
View Related
Jul 6, 2012
I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
View 7 Replies
View Related
Jul 6, 2013
I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.
CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');
-- The output to be is.
item_no , item_name
A TEST1
B TEST2
C TEST3
D TEST4
View 23 Replies
View Related
Jul 6, 2012
I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
SQL>
View 4 Replies
View Related
Sep 18, 2012
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but this is not right
i want to extract numbers without DOT also.
View 6 Replies
View Related
Apr 18, 2012
Is the 12c version of the Grid Control only for Oracle Cloud? Not completely sure of exactly what cloud computing is. Looks like a conglomeration of shared resources. Anyway we are converting all of our servers over to vmware virtual machines except the ones we manage in other states. I am upgrading each database to 11 r2 as we convert the servers. Anyway my Grid server is next to convert to a virtual machine. I am using Grid Control 10.2.0.5.0 now. I want to upgrade to the latest version of the Grid control. 12c seems to stress and boast about managing the "Oracle Cloud" to manage regular single node Oracle servers as well as vmware virtual machines?
View 3 Replies
View Related
Sep 14, 2008
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place]
UNION
SELECT [get data from another place]
UNION
SELECT [get data from another place]
this returns the following...
a_val
-----
100
200
300
query to return something like this...
a_val1 a_val2 a_val3
------ ------ ------
100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
View 1 Replies
View Related
Apr 8, 2010
We have a queue in which the message is coming from external system. The payload of the queue table is a PL/SQL record type. Once we get the message in the queue, we de-queue the message and read through the PL/SQL type collection and process the message.
From the below query, we are able to convert the PL/SQL collection message to XML message and see the data.
SELECT dbms_xmlgen.getxml
('SELECT USER_DATA
FROM <Queue_table> X
WHERE X.USER_DATA.SALE_ORDER.P_HEADER_REC.ORIG_SYS_DOCUMENT_REF=800501298')
FROM dual;
The new requirement is the message would come in a XML message in the queue. So my question is, is there any way through which the XML message can be converted to the PL/SQL record structure directly (it would be the opposite operation of the above query).
View 4 Replies
View Related
