Hi,
when a SELECT in an INSERT INTO statement returns no rows, NO_DATA_FOUND
exception is raised. How do I write a "nice" SELECT INTO when I expect that
0 or 1 row gets returned, and the variable should be NULL if no rows are
returned.
By "nice" I mean that my intention is clear from the source code and no
exception-catching is involved, since that would suggest that I'm handling
such a condition that shouldn't occur.
Therefore the following two techniques are not satisfying:
SELECT MIN(thefield) INTO myvar ... -- (not clear what is intended)
BEGIN --exception handling where no real exceptional condition occurs
SELECT thefield INTO myvar...
EXCEPTION WHEN NO_DATA_FOUND THEN
myvar := NULL
END;
3  12388 
On 27.10.2004 15:17 Agoston Bejo wrote: Hi,
when a SELECT in an INSERT INTO statement returns no rows, NO_DATA_FOUND
exception is raised. How do I write a "nice" SELECT INTO when I expect that
0 or 1 row gets returned, and the variable should be NULL if no rows are
returned.
By "nice" I mean that my intention is clear from the source code and no
exception-catching is involved, since that would suggest that I'm handling
such a condition that shouldn't occur.
Therefore the following two techniques are not satisfying:
SELECT MIN(thefield) INTO myvar ... -- (not clear what is intended)
BEGIN --exception handling where no real exceptional condition occurs
SELECT thefield INTO myvar...
EXCEPTION WHEN NO_DATA_FOUND THEN
myvar := NULL
END;
I can't see a problem with the second approach. Simply add a comment to explain
that this is an expected exception. From my point of view this is perfectly
acceptable
Thomas
Does Oracle support this?
myvar := (select MIN(thefield) ....)
"Scalar subqueries" should have "null on empty" semnatics.
Cheers
Serge
Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2u******* ******@uni-berlin.de>... Does Oracle support this?
myvar := (select MIN(thefield) ....)
"Scalar subqueries" should have "null on empty" semnatics.
Cheers
Serge
I'll interpret the above as two questions.
#1 Does Oracle support:
myvar := (select MIN(thefield) ....)
?
Well, AFAIK not in PL/SQL or embedded SQL. The above looks like some
form of PASCAL. I don't think any language supports a SQL construct
like that, but I could be wrong. Correct syntax in ORACLE looks like
SELECT MIN(thefield) into :myvar ...
#2 Does Oracle support:
"Scalar subqueries" should have "null on empty" semnatics.
?
YES.
If the value of the column named "thefield" is null for ALL rows in
the result set, then MIN() returns NULL. Same is also true for MAX()
and several other group functions. (COUNT() of course never return
NULL.)
HTH,
Ed- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: johnnyboy10017 |
last post by:
I am having a hell of a time with what I think is a very simple query:
It won't actually insert a new record into the specified table, but
returns no error, in fact it returns "1" (or true) that the query was
successful.
Things to know:
1. id field is auto-incrementing primary key
2. Other queries SELECT, UPDATE, and DELETE all work no problem
3. INSERT works via phpMyAdmin
4. Query generated by phpMyAdmin and pasted into my PHP...
|
by: Sans Spam |
last post by:
Greetings! I have a table that contains all of the function
permissions within a given application. These functions are different
sections of a site and each has its own permissions (READ, WRITE,
UPDATE, DELETE) which are controlled by a web frontend and the table
records are manipulated to control the permissions.
Example:
The Press Release section record would look like this:
Username: John Doe
Function Name: Press Release
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Stéphane Cazeaux |
last post by:
Hi
I currently use pgsql 7.2.4 (but the following has also been seen on
pgsql 7.3.3) with a transaction level set to "read committed".
It do a lot of little tests to understand how concurrency control works.
Let see this scenario:
We have a table named "test_count" and a field named "count"
The table contains 1 entry with count=1
|
by: Josué Maldonado |
last post by:
Hello List,
I'm importing some data from Foxpro to Postgres, there is atable wich
contains aprox 4.8 million rows and it size about 830MB. I uploaded it
to Postgres using dbf2pg and worked fine, it tooks about 10-15 minutes.
Now I'm inserting some data from that table to a brand new table in
Postgresql, for that I'm doing insert into ... select from. The point is
inserting this data from one table to another table in Postgresql took...
| | |
by: Mark Harrison |
last post by:
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.
I've been doing something like
delete from foo where name = 'xx';
insert into foo values('xx',1,2,...);
|
by: Bob Bedford |
last post by:
hello
I'm looking for some functions or objects allowing to
select-insert-update-delete from any table in a mysql database without the
need to create a new query every time. Example:
selectdatas(array('field1','field2','fieldn'),array('table1','tablen'),array('left
join,idy','inner join, idx'))
then the function build the query, execute it and then return an object with
|
by: Michel Esber |
last post by:
Hello,
Environment: db2 V8 FP 13 LUW
Our application currently uses:
insert into table values ('A'),('B'),...('Z')
We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
|
by: Rabothe |
last post by:
I have created an excel report and i have visual basic macros running. Now i want to insert borders using VB macros. How do i do that? If the report returns 5 row then the border should be around those 5 rows, and If the report returns 10 row then the border should be around those 10 rows. INSERT BORDERS BASED ON THE NUMBER OF RETURNED ROWS.
|
by: shashi shekhar singh |
last post by:
Respected Sir,
i want to check it out, when insert returns zero rows affected ?
insert into Question_Copy(,column_name1,column_name2,column_name3)
SELECT top 1 column_name1,column_name2,column_name3
FROM tablename where column_name1 not in (select column_name1 from Question_Copy)
--and column_name2 not in (select column_name2 from Question_Copy)
and UNIT_CODE not in (select UNIT_CODE from Question_Copy)
--and column_name3...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
