It is like always:
If you need your data in a specific sequence you have to add an ORDER BY
(also in an Sub-Select and also in composition with LIMIT or FETCH FIRST
ROWS ONLY).
NULL Values are always added at the end
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Craig
Pelkie
Sent: Monday, 1 April 2024 10:19
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL subselect LIMIT question
Given the following SELECT statement, I get three rows returned, with values
for EMAIL."USER" and EMAIL.EMAIL:
SELECT
CUSTMAST.D1010 AS CUSTOMER_NUMBER ,
CUSTMAST.D1015 AS COMPANY_NAME ,
EMAIL."USER",
EMAIL.EMAIL
FROM CUSTMAST
LEFT JOIN
(
SELECT CUSTNO, "USER", EMAIL
FROM DLREMAIL
-- LIMIT 1
) AS EMAIL ON CUSTMAST.D1010 = EMAIL.CUSTNO
WHERE CUSTMAST.D1010 = 782805
If I uncomment the LIMIT clause in the subselect, so that LIMIT 1 is in
effect, I get 1 row returned, but the EMAIL."USER" and EMAIL.EMAIL values in
the result set are NULL. I expected that the values would contain data from
the first row selected in the subquery.
Any insight as to what I need to do to get just 1 row of data from DLREMAIL,
with the values? There are 3 rows in the DLREMAIL table for customer number
782805, and there are values (non-null) for the columns in those rows.
(table/column names are in a legacy table).
Thanks,
Craig Pelkie
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
