Why does is the code blow reuturning a null for a simple subtraction and division?I am trying to do 4-13/9 for one and 4-22/9 for 22
select unit,
term_1201,term_1202,r_slope,r_intercept,
(r_intercept - term_1201) / decode((r_slope),0,1) as ONE,
(r_intercept - term_1202) / decode((r_slope),0,1) as TWO
[code]...
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.
Linux 2.4.21-37.ELsmp #1 SMP Wed Sep 7 13:28:55 EDT 2005 i686 i686 i386 GNU/Linux...As you'll see I do not understand anything about PRO*C. I need to compile this PRO*C code to use it to retrieve DBMS PIPES being sent.
I'm trying to compile it using the following command:
gcc -o IAPIPE IAPIPE.c
The code - file IAPIPE.c
#include <stdio.h>
#include <string.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
int status;
int msg_length;
char retval[2000];
EXEC SQL END DECLARE SECTION;
[code]....
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
Attached query is running fine if inline view B (Marked in Comments) returning value. If inline view B returns NULL then it fails to return result. I want if Inline view B is returning NULL then it should pass ZERO to main query.
View 2 Replies View Relatedhow to catch a null value with C# code?
My Asp.Net/C# application is connected to an Oracle database where it retrieves database values and also values calculated/returned from Oracle functions. My problem is that sometimes an Oracle function I calling returns a null value because of not correct input parameters which will cause Oracle function to (I think) return an null value. My problem and question is how to catch/obtain if he Oracle function returns a null value? Oracle function should return a decimal value if correct input parameters. Below is my row of code that crashes when Oracle function returns a null value in case of incorrect input parameters.
decGrossFreightRevenue = (decimal)(OracleNumber)cmdCalculateRevenueCall.ExecuteOracleScalar();
how to change my code to catch a null value and avoid a crash. I have tried several checks (if statements) without success.
how to get correct result for the time subtraction of minutes for the following query
select (to_date('14-06-2012 11:10:00','dd-mm-yyyy hh24:mi:ss') - to_date('14-06-2012 11:00:00','dd-mm-yyyy hh24:mi:ss'))*60*60 as
result from dual
RESULT
----------
25
i want the output as 10 minutes.
Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.
Is it possible or not.
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies View RelatedI have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
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?
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.
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]....
How to design a Simple Graph?
View 1 Replies View RelatedI'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]....
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!
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.
create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
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
I am trying to use OCIColl datatype and thus wanted to check if a simple program using OCI functions and data types gets compile or not. sO I wrote the follwoing function which I want to compile as a DLL.
extern "C" CLUSTERINGDLL_API OCIColl *fnClusteringDLL(OCIColl *dxm[])
{
OCIError *errhp;
OCINumber num_1, num_2, sum;
OCINumberAdd(errhp, &num_1, &num_2, &sum);
std::cout<<"Members in the array are "<<dxm[1]<<std::endl;
std::cout<<"2 Member in the array are "<<dxm[0]<<std::endl;
printf("Is this printed %f", dxm[0]);
return dxm[1];
}
But I am getting the following error:-
Error6error LNK2019: unresolved external symbol _OCINumberAdd referenced in function _fnClusteringDLLclusteringdll.obj...
I have included the library folder containing oci.lib in the project directories.
I am using Oracle 11g Release 11.2.0.1.0
OS: windows
I am taking an example of EMP table in a scott schema.
EMP_1 is also a same structure of EMP.
Here is the Insert statement used.(i might get where clauses etc in the real scenario i am taking a simple example here)
INSERT INTO SCOTT.EMP_1(JOB,ENAME,EMPNO) SELECT JOB,ENAME,EMPNO FROM SCOTT.EMP
table EMP is having around 25 million records.
i would like to do a commit after every 1000 records. as my redo log is getting full if i use a single commit at the end.
Quote:pls note: Increasing of redo log is not possible as of now
is there anyway i can acheive this without cursor bulk collect.
I have developed simple matrix report using wizard. But now I want to do it manually.
View 3 Replies View RelatedI have created simple data block by wizard and taken tabular layout 10 rows and 6 columns
i want to generate serial number on first column .....
what need to use..?
Yesterday i got wait event when executed simple select from table.This select was like:
SELECT emp_number from employer where subs_id = 111
I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:
1) SELECT emp_number from employer where subs_id = 111 then
2) Package with this function
Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?
BANNER
1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
2PL/SQL Release 10.2.0.5.0 - Production
3CORE 10.2.0.5.0 Production
4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
5NLSRTL Version 10.2.0.5.0 - Production
[code]...
Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.