It seems with V7 of DB2 I can now use Fetch First 1 Rows Only in a
Select stmt, whic is great, because I don't have to use a cursor.
Unfortunately, it doesn't allow ORDER BY in the same Select and the
order is important because I need to pick the next supplier in a
sequence that can be changed by the user. Interestingly enough, I can
execute the Select with Order By and Fetch First 1 Rows Only in SPUFI,
with no problem.
Is there some way of handling the sort on Seqno without using Order By.
Here's the Query:
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO
INTO :Supplier
,:Gadget_QTY
,:SEQNO
FROM DS.Supplier D
INNER JOIN DS.Supplier_CPC TY C
ON D.Supplier = C.Supplier
WHERE C.PROD_NUM = :PROD_NUM
AND D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
FETCH FIRST 1 ROWS ONLY;
As you can see, I keep going to the next supplier in sequence until
there are non left - this Select is in a loop.
I'm using PL/I 1.1.1 on Z/OS 1.4
Thanks! 3 8300
How about this?
(I don't know about performance.)
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO
INTO :Supplier
, :Gadget_QTY
, :SEQNO
FROM DS.Supplier D
INNER JOIN
DS.Supplier_CPC TY C
ON D.Supplier = C.Supplier
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
INNER JOIN
DS.Supplier_CPC TY Cm
ON Dm.Supplier = Cm.Supplier
AND Cm.PROD_NUM = :PROD_NUM
WHERE Dm.SEQNO > :SEQNO
)
;
If only one matching DS.Supplier_CPC TY for each DS.Supplier is always
exists,
The query could be simplified a little.
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO
INTO :Supplier
, :Gadget_QTY
, :SEQNO
FROM DS.Supplier D
INNER JOIN
DS.Supplier_CPC TY C
ON D.Supplier = C.Supplier
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
WHERE Dm.SEQNO > :SEQNO
)
; Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<
In DB2 UDB for OS/390 and z/OS SQL Reference Version 7
Chapter 4. Queries -> select-statement
Both order-by-clause and fetch-first-clause can be specified.
Chapter 5. Statements -> SELECT INTO
Only FETCH FIRST [1] ROW ONLY can be specified.
Thanks, Tonkuma!
I wasn't able to use your suggestion quite as provided, I was able to
break it up into two queries. the first one just gets the next
sequence number and then I just plug it in the second query as an "="
predicate.
I read the DB2 manual chapters you refer to, but the Select with "INTO"
does not allow "ORDER BY" period. And I can't think of anyway to
receive the table values without an "INTO", so I can't use "ORDER BY"
or "FIRST ROW ONLY".
I believe I read that V8 may support this, which doesn't help me at the
moment.
Thanks again!
Tonkuma wrote: How about this? (I don't know about performance.) SELECT D.Supplier, C.Gadget_QTY, D.SEQNO INTO :Supplier , :Gadget_QTY , :SEQNO FROM DS.Supplier D INNER JOIN DS.Supplier_CPC TY C ON D.Supplier = C.Supplier AND C.PROD_NUM = :PROD_NUM WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */ AND D.SEQNO = (SELECT MIN(Dm.SEQNO) FROM DS.Supplier Dm INNER JOIN DS.Supplier_CPC TY Cm ON Dm.Supplier = Cm.Supplier AND Cm.PROD_NUM = :PROD_NUM WHERE Dm.SEQNO > :SEQNO ) ;
If only one matching DS.Supplier_CPC TY for each DS.Supplier is always exists, The query could be simplified a little.
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO INTO :Supplier , :Gadget_QTY , :SEQNO FROM DS.Supplier D INNER JOIN DS.Supplier_CPC TY C ON D.Supplier = C.Supplier AND C.PROD_NUM = :PROD_NUM WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */ AND D.SEQNO = (SELECT MIN(Dm.SEQNO) FROM DS.Supplier Dm WHERE Dm.SEQNO > :SEQNO ) ;
Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<
In DB2 UDB for OS/390 and z/OS SQL Reference Version 7 Chapter 4. Queries -> select-statement Both order-by-clause and fetch-first-clause can be specified. Chapter 5. Statements -> SELECT INTO Only FETCH FIRST [1] ROW ONLY can be specified. be*******@yahoo .com wrote: Thanks, Tonkuma! I wasn't able to use your suggestion quite as provided, I was able to break it up into two queries. the first one just gets the next sequence number and then I just plug it in the second query as an "=" predicate.
I read the DB2 manual chapters you refer to, but the Select with "INTO" does not allow "ORDER BY" period. And I can't think of anyway to receive the table values without an "INTO", so I can't use "ORDER BY" or "FIRST ROW ONLY".
I believe I read that V8 may support this, which doesn't help me at the moment. Thanks again!
Tonkuma wrote: How about this? (I don't know about performance.) SELECT D.Supplier, C.Gadget_QTY, D.SEQNO INTO :Supplier , :Gadget_QTY , :SEQNO FROM DS.Supplier D INNER JOIN DS.Supplier_CPC TY C ON D.Supplier = C.Supplier AND C.PROD_NUM = :PROD_NUM WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */ AND D.SEQNO = (SELECT MIN(Dm.SEQNO) FROM DS.Supplier Dm INNER JOIN DS.Supplier_CPC TY Cm ON Dm.Supplier = Cm.Supplier AND Cm.PROD_NUM = :PROD_NUM WHERE Dm.SEQNO > :SEQNO ) ;
If only one matching DS.Supplier_CPC TY for each DS.Supplier is always exists, The query could be simplified a little.
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO INTO :Supplier , :Gadget_QTY , :SEQNO FROM DS.Supplier D INNER JOIN DS.Supplier_CPC TY C ON D.Supplier = C.Supplier AND C.PROD_NUM = :PROD_NUM WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */ AND D.SEQNO = (SELECT MIN(Dm.SEQNO) FROM DS.Supplier Dm WHERE Dm.SEQNO > :SEQNO ) ;
> Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<
In DB2 UDB for OS/390 and z/OS SQL Reference Version 7 Chapter 4. Queries -> select-statement Both order-by-clause and fetch-first-clause can be specified. Chapter 5. Statements -> SELECT INTO Only FETCH FIRST [1] ROW ONLY can be specified.
I haven't used DB2 for quite a while now, but seem to remember using
GROUP BY and HAVING to fiddle sequencing when ORDER BY was not allowed.
Might be worth a try.
Robert This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan |
last post by:
I've encountered some strange behavior in a recursive procedure I'm
writing for a bill of materials. First let me ask directly if what I
think is happening is even possible:
It seems like the procedure is not following the recursion in serial
order, but in parallel. In other words, after one instance of the
procedure calls itself, it continues executing lines below the
recursion before the recursion is done. Is that possible? I...
|
by: Evan Smith |
last post by:
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.
The monitor captured:
SELECT * FROM EWM_CASE fetch first 1 rows only
It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
|
by: Acupuncture |
last post by:
Hi,
I am developing a JDBC application and I encountered this problem (DB2
for ISeries).
I want to do a select for update and also use the fetch first rows clause.
This is my sql statement:
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE
This runs fine (the cursor gets this name 'P00022'), but when I execute the
|
by: GreatAlterEgo |
last post by:
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
|
by: db2admin |
last post by:
hi,
For the following SQL and plan
==============================================================
SELECT Q1.PLCY_KY, Q1.PLCY_TRM_EFCTV_DT, Q1.ACNTG_BGN_DT,
CASE
WHEN ((Q1.PLCY_SRC_CD = '02') AND ((DAYS(Q1.PLCY_TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TRM_EFCTV_DT)) <= 180))
THEN 'Y'
| |
by: mandible |
last post by:
Hello I'm trying to have some control on how my data is ordered
depending on an input parameter
my question is
in a stored procedure how can I do something like this at the end of my
statement.
pOrder as input value where pOrder can be 1 or 0
|
by: bennett.matthew |
last post by:
Hello all,
I'm building an Ajax table which fetches its contents from mySQL. The
idea is that the table displays x results on each page, and when you
click 'next page' it downloads the next x results and displays them.
The table uses an arbitrary SQL string to fetch results, and I'm stuck
on how to access just a small result set. Since the SQL is arbitrary I
can't use WHERE myfield = somevalue, because I don't know what
'myfield' is.
|
by: Frank Swarbrick |
last post by:
Is there a way to do a multi-row fetch in to a COBOL table with DB2/LUW?
Apparently the following is supported in z/OS, but not LUW (or at least I
couldn't get it to work):
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
|
by: gecashish |
last post by:
If i use below query
Select * from ADJ_ADJSTMNT fetch first 21 rows only with ur;
works fine
But if i use the same query with order by clause like below
Select * from ADJ_ADJSTMNT
order by adj_ca_id
fetch first 21 rows only with ur;
It's very much time consumming.
Pls note that ADJ_ca_id has an primary index,It's must for me to select all records from ADJ rather then some specific fields from ADJ
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: 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: 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: 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...
| |