Insert All Client Numbers
May 12, 2010
I am trying to run this script:
Update ACT.CUSTOMER_CLIENT_REFERENCE
Set ORIGINAL_SOURCE_FG = 'N',
CANADIAN_ULTIMATE_REFERENCE_FG = 'N',
LUDT = SYSDATE,
CLT_NO = (Select clt_no from client.client where clt_no between 701885 and 705287)
Where Exists (Select 1 from client.client where clt_ofc_no = 19 and clt_no between 701885 and 705287)
But the CLT_NO = .... line is giving me this error: ORA-01427: single-row subquery returns more than one row
what i am trying to do is insert all the client numbers between 701885 and 705287 into ACT.CUSTOMER_CLIENT_REFERENCE
View 6 Replies
ADVERTISEMENT
Aug 23, 2012
I am having the emp table :
empno enmae job
1111 ramu S.E
7658 VENU S.S.E
8280 GETA TRAINEE
alter table emp add sno number
for that sno i have to generate 1,2,3,...
View 4 Replies
View Related
Oct 13, 2010
I wanted to load some data(selected rows) from one system to other.
from source(production) system Toad: selected * from employee table where registration date is not today.
from data grid I exported the rows using save as (in insert statement format) to employee.sql.
the sql file(employee.sql) has the below values for emaplyee 2,60716197237375E17 in the source(production) system this value is 260716197237375278, Later I loaded the sql file to dev system at linux prompt with sql file
@emplaoyee.sql
I retrieved the data after loading using to_char
select toc_char(emp_code) from employee_t;
the value I got is :260716197237375000
where as my expectation is : 260716197237375000
Is there any configuration required on linux system to convert the data correctly while loading to database using @employee.sql?
To conclude:
Exported value : 260716197237375278 (but exported as 2,60716197237375E17)
After import the value is : 260716197237375000 instead 260716197237375000
View 3 Replies
View Related
Sep 13, 2010
Here is one way to create EXCEL file from oracle sql query and prevent excel displaying large numbers in scientific notation(exponential notation)
set feedback off
set verify off
set heading off
spool c:excel_test.xls
select 'PO_NUMBER'||chr(9)||'VENDOR_NUMBER' from dual
union
select '=PROPER('||po_number||')'||chr(9)||'=PROPER('||vendor_number||')'||chr(9)
from invoices
where rownum < 12
order by 1 desc
Note that PO_NUMBER is 16 characters, VENDOR_NUMBER is 15 characters in invoices table.
View 1 Replies
View Related
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
Oct 28, 2013
I wish to make this simple statement with Toad GUI
INSERT INTO EXCLUDE_xxx
VALUES ('xxx',
'xxx',
'xxx',
'xxx',
SYSDATE);
Insert record is greyed out. How to insert new rows with Toad (click click)?
View 7 Replies
View Related
Mar 29, 2012
I'm trying to insert an XML into an XMLType with the following value:
insert into Table1(
code, xmlColumn) VALUES
(1, '<script> > </script>');
the question is I need to have > in the text, but Oracle insists in putting it like <script> > </script>.
What do I have to do to solve this?
View 15 Replies
View Related
Jun 22, 2012
I want to insert data into three table
my first table is
firstemp
having columns
1.EMPID
2.NAME
3.STATUS
second table is
secimp
have same columns as of first table
third table is
thirdimp
columns are same as of first andsecond
I want to insert in Firsttable if EMPid=EMP_1h and insert in second table if empid=EMP_2u and insert into third table if empid=emp_3p
View 1 Replies
View Related
Dec 13, 2012
I have problem: I have 1 sql server already setup SQL Server 2012 Express and 1 Oracle Database server 10g. Now i want to insert data from SQL server to Oracle database through link server.
Some step i already make:
1. Setup oracle database 10g and configure listener (Finished)
2. Setup Sql server 2012 express on Windows 7 (Finished)
3. Setup ODTwithODAC1020221 on PC already setup SQL server (Finished)
4. Make Linkserver from SQL server to Oracle database (Finished), and can select data from Oracle Database on SQL server through Linkserver.
However when i insert data from SQl server to Oracle Server not success.
select * from OPENQUERY (QVHKTEST, 'SELECT * FROM QVSYSTEM')
After i run above script, result is OK
With: "QVHKTEST" is alias of Link server from SQL to Oracle server
: "QVSYSTEM" is a table on Oracle database, that table we want to get through Linkserver on SQL server
Both Server Database contain same name table is "QVSYSTEM"
-----------
INSERT OPENQUERY (QVHKTEST, 'SELECT BODY_NO,
MERCHANDISE,
MODEL_NAME,
LINE_NAME,
DATE_ENTRY
FROM QVSYSTEM')
values('VNF4619829','3227B002CA','L1068','01','2012/09/26 03:18:11');
If i run script above directly in SQL Window query can insert OK. This is code in trigger at table on SQL server:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:<Description: After data insert into table qvsystem on SQL server
-- This trigger will fire and insert that data into table qvsystem
[code].......
But when i make trigger after insert on table in SQL Server to insert data From SQL server to Oracle server, however not success and SQL server raise error below:
OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure Insert_data, Line 16
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.
I don't know how to configure them.
View 8 Replies
View Related
Jan 4, 2011
How can i insert 50 million records at a time
View 1 Replies
View Related
Aug 21, 2008
Is theree any way to generate the insert script (with the data) for an existing table.
In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.
View 19 Replies
View Related
Aug 23, 2011
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
View 13 Replies
View Related
Mar 29, 2011
I am using oracle 9.2.0.6.0 on HP-UX.
I am unable to insert and even display euro symbol from server as well as windows client.
Following are the details of my database server
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
[code]....
1) When I try to insert € from Db server (using putty) using Alt+0128 it does not print anything (nothing gets typed on the screen). Not even junk characters Also following query does not print anything
SQL> select chr(128) from dual;
C
-
2) while I set NLS_LANG on client and try to insert €, Alt+0128 produces a question mark symbol And following query displays junk character
SQL> select chr(128) from dual;
C
-
Ç
3) Regardless when I inserted couple of rows and tried UNISTR following was the result
SQL> select unistr(v) from t;
Error 45 initializing SQL*Plus
Internal error
$
View 19 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
Mar 1, 2012
How to print even numbers 1 to 10.without using the MOD Function
View 17 Replies
View Related
May 11, 2012
I have a table with three columns: terminal, place and batch. How can I check for missing batches?
select * from transactions;
terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825
View 5 Replies
View Related
Mar 14, 2002
I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :
select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
I'm afraid but i don't undaunted how pass my number to this function (&num ???)
View 12 Replies
View Related
Feb 16, 2010
create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;
I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.
For example:
Number_fromNumber_to missing
13
594 from previous record, this record is supposed to start from 4
View 24 Replies
View Related
Feb 5, 2011
I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example
5.24 Plus 5.24 it will give the result 10.48
I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12
View 7 Replies
View Related
Sep 21, 2011
I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as
ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900
I need to select all records from table A when it is between any one of the three rows from table B.
View 13 Replies
View Related
Apr 10, 2012
acheive the below result.
Select * from result;
res
===
3
3
3
3
3
0
3
3
3
0
3
0
output:
I would like to get res1 using SQL query.
resres1
31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....
View 9 Replies
View Related
Aug 5, 2011
how can show this query in this picture
select round(15.555,2) from dual appear final result 15.55 only
and this query don't be this result 15.56
because i have filed and always Decimal numbers be (15.555,2)
i want only result appear after two numbers and no rounded the numbers
View 2 Replies
View Related
