Hi,
This is my query which is embedded in a COBOL program.
EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC
When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.
I now doubt whether DB2 supports SELECT-INTO with ORDER BY.
Does it or does it not? 26 17212
Put "FETCH FIRST 1 ROW ONLY" before "FOR READ ONLY" and "OPTIMIZE FOR 1
ROW".
"FETCH FIRST 1 ROW ONLY" is part of Subselect.
"FOR READ ONLY" and "OPTIMIZE FOR 1 ROW" are part of Select-sttement.
What DB2 version and platform are you using?
GreatAlterEgo wrote: Hi, This is my query which is embedded in a COBOL program.
EXEC SQL SELECT DATE, AGE, DURATION, AMT INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT FROM TAB1 WHERE CODE = :KEY.CODE AND SET = :KEY.SET AND DATE <= :KEY.DATE AND AGE >= :KEY.AGE AND DURATION >= :KEY.DURATION ORDER BY DATE DESC, AGE ASC, DURATION ASC FOR READ ONLY FETCH FIRST 1 ROW ONLY OPTIMIZE FOR 1 ROW END-EXEC
When I compile it, the module errors out, giving the reason as :FOR FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.
Do you get a SQL0811? If yes, which level of DB2 are you using? (output of
"db2level" command) This should be working since level 031219 or later.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
"GreatAlter Ego" <gr***********@ gmail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . Even this does not work.
I don't know for sure, but a fetch into assumes there is only one row, and
order by implies multiple rows. If you open a cursor and then do only one
fetch into from the cursor, the cursor definition can have an order by.
"Knut Stolze" <st****@de.ibm. com> wrote in message
news:dq******** **@lc03.rz.uni-jena.de... GreatAlterEgo wrote:
Hi, This is my query which is embedded in a COBOL program.
EXEC SQL SELECT DATE, AGE, DURATION, AMT INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT FROM TAB1 WHERE CODE = :KEY.CODE AND SET = :KEY.SET AND DATE <= :KEY.DATE AND AGE >= :KEY.AGE AND DURATION >= :KEY.DURATION ORDER BY DATE DESC, AGE ASC, DURATION ASC FOR READ ONLY FETCH FIRST 1 ROW ONLY OPTIMIZE FOR 1 ROW END-EXEC
When I compile it, the module errors out, giving the reason as :FOR FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.
Do you get a SQL0811? If yes, which level of DB2 are you using? (output of "db2level" command) This should be working since level 031219 or later.
-- Knut Stolze DB2 Information Integration Development IBM Germany
If it is COBOL, then it may be DB2 for z/OS.
I think so.
DB2 for OS/390 and z/OS supports "FETCH FIRST n ROW ONLY" from Version
7.
Anyway, if DB2 version and platform are unknown, it would be difficult
to know the reason of error.
Since tables have no order by definition, what are you tryng to do?
Apparently, you want to grab a random row for display. Is that right?
--CELKO-- wrote: Since tables have no order by definition, what are you tryng to do? Apparently, you want to grab a random row for display. Is that right?
Please stop lecturing. Start reading instead. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Guy Hocking |
last post by:
Hi there,
I have a problem in my ASP/SQL Server application i am developing, i hope
you guys can help.
I have a ASP form with list boxes populated by SQL tables. When a user
selects a value in a list box and submits the form the value is put into a
session variable and the relevant page is displayed (in accordance to one of
the list boxes).
The page is then displayed with the relevant SQL data. So far i have got the
|
by: mlke |
last post by:
I would like to select the top 10 record from a table? How can I do it?
In MS SQL, it's easy using select top 10 from table1. But in oracle database,
I can't use top, anyone have any suggestions? Thanks!
|
by: Jay K |
last post by:
Hi,
I have multiple queries like this:
SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 1
ORDER BY col3 desc LIMIT 5
and
|
by: malcolm |
last post by:
Example, suppose you have these 2 tables
(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)
CarsSold {
CarsSoldID int (primary key)
MonthID int
DealershipID int
NumberCarsSold int
|
by: war_wheelan |
last post by:
I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs
and want to ORDER BY 'IOI's'. I have been combing through the BOL, but
I don't even know what topic/heading this would fall under.
USE INDII
SELECT FIXID, COUNT(*) AS IOIs
FROM .
GROUP BY FIXID
| |
by: TP |
last post by:
Here is my problem.
I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.
But my table has 1 million rows and if I do a select * then I do get
1 million rows.
I want to be able to provide page navigation as google does, page
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
|
by: rcamarda |
last post by:
I was looking through our vendors views, searching for something I
needed for our Datawarehouse and I came across something I do not
understand: I found a view that lists data when I use it in t-sql,
however when I try to use the statement when I modified the view (via
MS SQL Server Management Studio) I can not execute the statement. I get
The column prefix 'dbo.tbl_5001_NumericAudit' does not match with a
table name or alias name used...
|
by: FishVal |
last post by:
Hereby I'm proposing a way of convinient work with properties containing SQL Select statements, particulary RowSource property of ComboBox and ListBox.
The usual way is the following.
Private Sub Combo1_AfterUpdate()
Me.Combo2.RowSource = "SELECT ... FROM ... WHERE Table2.keyID=" & Me.Combo1 & ";"
Combo2.Requery
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |