Re: Exception in this sql

First, I would read more than the first sentence of any reply. If you
have ADD it's your job to struggle through it, not ours to have each reply
be no longer than a tweet. The following was part of the post.
<snip>
LIMIT is not Standard, while FETCH FIRST x ROWS ONLY is.
</snip>

Our immediate past CEO had ADD.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 05/22/2015 08:33 AM
Subject: Re: Exception in this sql
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Right so how would you use such' Limit in DB2 how would you code this
situation? which is
we do want all 001 rows
but if the order has no 001 row then we only want the one row for it the
earliest time wise?

On Fri, May 22, 2015 at 1:39 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

If you only want to have a single row the syntax is : FETCH FIRST ROW

ONLY

(not LIMIT).

Just an advice:
1. If you search for solutions in the internet make sure you find an DB2
example (and not an example from MYSQL, SQLSERVER etc.). Even though

there

is an SQL standard there may be different dialects and some of the

database

manufacturer say, but we are already covering the functionality (with a
different syntax) why we should reinvent the wheel? (LIMIT is not

Standard,

while FETCH FIRST x ROWS ONLY is.
2. Double check the syntax in the SQL Reference:

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzpdf.pdf
?

lang=en


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Friday, 22.5 2015 00:58
An: Midrange Systems Technical Discussion
Betreff: Exception in this sql

I have to make one change to what was done previously. If there are no
'001'
rows for the order then we only want the earliest code.

But this union here is giving an error: SQL0104 - Token LIMIT was not
valid.
Valid tokens: ) FETCH ORDER UNION EXCEPT.

CREATE VIEW rklib.clspaytp AS
Select *
from rklib.clspaytpp
where ottrnc='001'
union

select *
from
(
select *
from rklib.clspaytpp
where ottrnc != '001'
limit 1
) x
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.