0

I have an AS400 Linked Server, and I'm trying to run some queries and get some sample sets, but I can't figure out how to limit the number of rows returned.

I tried:

SELECT * FROM OPENQUERY(LINKED, 'SELECT * FROM LINKED.APLUS.CUS WHERE CMSUSP != ''S'' LIMIT 100')

and

SELECT * FROM OPENQUERY(LINKED, 'SELECT TOP(100) * FROM LINKED.APLUS.CUS WHERE CMSUSP != ''S'' ')

I know I can add the TOP to the SELECT FROM OPENQUERY, but I'm trying to not have the AS400 return 100k rows when I only need 100.

Dizzy49
  • 1,360
  • 24
  • 35
  • 1
    Using openquery, you pass a statement to the other database engine to execute. So any functionality you desire must be implemented in the sql dialect used by that engine. So - does DB2 (presumably - might help if you provide specific information) implement that in some fashion? – SMor Feb 06 '20 at 22:28
  • 1
    And seems to me that LIMIT should do what you want. Did it? – SMor Feb 06 '20 at 22:29

1 Answers1

0

Based on my experience with an AS400 system, I believe you're looking for FETCH FIRST N ROWS ONLY

SELECT * FROM ... WHERE ... ORDER BY ... FETCH FIRST 100 ROWS ONLY

In your example that would be:

SELECT * FROM OPENQUERY(LINKED, 'SELECT * FROM LINKED.APLUS.CUS WHERE CMSUSP != ''S'' FETCH FIRST 100 ROWS ONLY')

It is recommended to include an ORDER BY to ensure that the results are consistent.

See this question and answer for additional details: How to do SQL select top N ... in AS400

TeamworkGuy2
  • 159
  • 6