Brigitta, thanks for your reply.
The problem was not with ordering, but with my misunderstanding of where to place the LIMIT clause.
When I placed the LIMIT inside the subselect, null values were returned.
Placing the LIMIT on the outer SELECT does what I need (values are returned):
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 -- << NOT HERE
) AS EMAIL ON CUSTMAST.D1010 = EMAIL.CUSTNO
WHERE CUSTMAST.D1010 = 782805
LIMIT 1 -- << HERE
Not sure why that didn't occur to me before posting, but also not sure why the LIMIT inside the subselect returns NULL. In any case, problem solved.
Craig Pelkie
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Monday, April 1, 2024 05:49
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL subselect LIMIT question
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.
--
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
