providing sql statement to strip commas from a number/numeric field in the BI repository logical column? I do not want to use cast function to convert to character because we need to keep that field as numeric. I could only find string expressions to remove commas.
View 12 RepliesI searched the forum but could not find an answer. I am creating an extract via SPOOL that will send the output to a CSV file. I am comfortable concatenating commas in between the fields, the problem lies in text fields such as last_name which may contain a comma. The requirements of my client state to put double quotes around text fields ONLY if they contain a comma (so as not to throw off the CSV file obviously).
The only thing I could think of was a nested DECODE checked every character of every text field for a , and if it finds one to put " and if not to put nothing. This would get very tedious to program it as there are many text fields and they can be very long. I may even run out of nested DECODE statements (I forget what the limit is now).
I want to convert the field value into numeric form using the formula:
∑[((radix)^ position * alphvalue) mod PR]
where alphvalue A= 10, B = 11, C = 12, D = 13,E = 14............. S = 28, ...........Z = 35, radix = 36, PR= 731
The value of position is marked from right to left starting with 0.
For Example:
ASIN
Let PR = 731 (large prime number)
∑[((36)^3 * 10)mod 731) + ((36)^2 * 28)mod 731)+
((36)^1 * 18)mod 731) + ((36)^0 * 22)mod 731) ]
= 182+ 469 + 648 + 22
= 1321
is it possible that it pick the field value and convert it into numeric form.
I have a column where the values are like
SQL> SELECT distinct a.legacy_product_code FROM im_polprdmap_pukka a where a.new_product_code like '%GIFT%';
LEGACY_PRODUCT_CODE
-------------------
GIFT25
GFTPF21NP
GIFT21/H
GIFT21//
[code]...
I need to calculate a field taking the numeric part of the above filed.How can i write this?
I am using APEX 4.1 and Oracle 10g XE.
I want to restrict 3 digits after decimal in tabular form. Only three or less than three digits should be allowed after decimal and should be stopped automatically.
e.g. 34.543
I want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies View RelatedI have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.
View 13 Replies View RelatedI have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;
--code written on button to update those values which are checked.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......
I am New to Apex. Im using Apex Version 4.2.
I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.
The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .
But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User
The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*
If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.
TestCase
DROP TABLE test ;
CREATE TABLE test (id NUMBER, cnt_str VARCHAR2(200));
INSERT INTO test (id, cnt_str) VALUES
(1,'AKRN000002,1451-1473,00000A,74,AKRN000002,1475-14791000000A,8010AKRN000002,1481-1492,00000A,9310AKRN000002,1494-1500')
[code]...
The requirement is in each of the string where there is comma after the number and the number is prefixed by "-" character,
the comma after the number should be replaced by '10'.
For example in the second record where ID = 2,
CNT_STR is '00000B,1-251000000D,26-32,ADTW000301,2858-2875'.
In this string -32, should become -3210 and resulting string should be '00000B,1-251000000D,26-3210ADTW000301,2858-2875'
Expected Result.
ID CNT_STR
-------- -------------------------------------
1 AKRN000002,1451-14731000000A,7410AKRN000002,1475-14791000000A,8010AKRN000002,1481-14921000000A,9310AKRN000002,1494-1500
2 00000B,1-251000000D,26-3210ADTW000301,2858-2875
3 AKRN000001,1126-12001000000B,501-525
When I try to print a decimal value , I get the dot replaced with a comma.
SQL> select 23.45 from dual;
23.45
----------
23,45
Is it because of any particular sqlplus set command or OS issue?
Create table a ( Objectid number, Value varchar2(2000);
/
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');
I should get the output as:
ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11
I am trying to insert data into a table which I created but I keep getting the error: missing comma.
INSERT INTO EMPLOYEE_DATA
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)
VALUES
('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')
I need to update a field with the 1st 9 characters of another field in the same record.
View 1 Replies View RelatedI have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0
[Code].....
I have a string value like -- a,,b,c,d,e,f
Using just sql, I want to put each value of the above string in a different row. So the output should be --
a
b
c
d
e
f
using procedures it would not be that great but I want to do it just using queries.
I have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:
<pnum> 1-10, 14, 17, 11, 21-24 </pnum>
I can muster a basic SQL statement to query for this by hand (more then one way to do this)-
SELECT *
FROM part_table
WHERE pnum BETWEEN '1' AND '10'
OR pnum BETWEEN '21' AND '24'
OR pnum IN (14, 17, 11);
is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?
SELECT XMLELEMENT (DECODE ('RF', 'RF', 'Credit', 'Sale'), xmlattributes ('01234567' AS "id") ) AS Order FROM DUAL
when I run the above SQL I'm getting "ORA-00917-missing comma" error.
We have a existing view which pulls list of orders against a deal. Straight forward result of the view is below.
Deal idSALES_ORDER_NUMBER
280760548460578
280760548789518
280760548794798
280760548794799
280760548873291
280760548887725
280760548900581
Each deal may have different number of orders.
We need to modify the view logic to result the list as comma seperated:
48460578,48789518,48794798,48794799,48873291,48887725,48900581
how to proceed.
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
View 4 Replies View RelatedI have to convert string 1234567 as 123,456,7 .
note 1234567 is a string.
This I want TO separate TO different COLUMNS based ON comma.
THE RULE IS LIKE out OF total five fields FIRST 3 comma will be FIRST 3 addresses AND rest will be address4 AND LAST NUMBER should appear IN pincode field IF found.
The trouble is for reading reverse to get the number.
WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
SELECT 'The Icon, 2nd 3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT addr1 FROM address;
In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '
Col_A
______
123
124
select '|| col_A ||'||','
from
Table_A;
The output should be
'123',
'124'
I can concatenate a comma but not single quotes.
I would like get rows into comma separated values expected output
rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:
create table test1 (name1 varchar2(10));
insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;
SQL> select * from test1;
NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4