?
Solved

Help with query

Posted on 2005-04-05
13
Medium Priority
?
1,234 Views
Last Modified: 2012-05-05
Basically I need to make this MS SQL Server query work with DB2:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
      (
            SELECT TOP 100 fld2
            FROM tbl1
            WHERE fld3 = ''
      )

I have no DB2 experience so I though this would work:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
      (
            SELECT fld2
            FROM tbl1
            WHERE fld3 = ''
            FETCH FIRST 100 ROWS ONLY
      )

However it doesn't. I get "SQL0199: Keyword FETCH not expected. Valid tokens: ) UNION. Cause . . . . . : The keyword FETCH was not expected here. A syntax error was detected at keyword FETCH...."

How can I accomplish this in DB2 for iSeries 5.2?

Thanks!
0
Comment
Question by:astankovic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
13 Comments
 
LVL 8

Expert Comment

by:nagki
ID: 13710102
Hi astankovic,

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
     (
          SELECT fld2
          FROM (select fld2,row_number() over() as rn from tbl1 WHERE fld3 = '' ) as x
          where rn < 100)
         


Cheers!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13711163

Your original SQL isn't far off, though version 5.2 may be too old to support the FETCH FIRST clause.


UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
(
  SELECT fld2
  FROM tbl1
  ORDER BY fld2 DESC
  FETCH FIRST 100 ROWS ONLY
)


Good Luck,
Kent
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13712002
My 5.1 system supports FETCH FIRST n ROWS, so 5.2 should definitely do it.

HTH,
DaveSlash
0
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13712208

Cool.  Then to correct my typo:

UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
(
  SELECT fld3
  FROM tbl1
  WHERE fld4 = ''
  ORDER BY fld3 DESC
  FETCH FIRST 100 ROWS ONLY
)

Should work fine.  I use that technique a lot.

Kent
0
 
LVL 4

Author Comment

by:astankovic
ID: 13718323
Kdo,

That's basically the same as I had other than the addition of ORDER BY clause.
But that still doesn't work, I get "Keyword ORDER not expected..." error.

FETCH FIRST n ROWS ONLY works in a simple query, but it doesn't seem to work in the subquery. The same thing about ORDER BY,

Any connection string options that I need to set to enable that? I'm connecting with Microsoft .NET OleDb provider.

Thanks,
Alex
0
 
LVL 4

Author Comment

by:astankovic
ID: 13718341
nagki ,

That errors out too.
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13718431
Just for kicks, try removing the ORDER BY and the FETCH FIRST clauses.  Does it then work?

Alternately, you could use a WITH clause.  Try something like this:

With tempTable as (
  SELECT fld3
  FROM tbl1
  WHERE fld4 = ''
  ORDER BY fld3 DESC
  FETCH FIRST 100 ROWS ONLY
)
UPDATE tbl1 SET fld1 = 'value'
WHERE fld2 IN
    (select fld3 from tempTable)

HTH,
DaveSlash
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 13718449
OK. I just tried it.

The only way I could get it to work was to remove the ORDER BY and the FETCH FIRST clauses.

Hmmm, I'm sure there's an explanantion for that, but it does seem weird to me.

DaveSlash
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 13720938

Strange....

I have no problem with queries that use that syntax.

I wonder if they require OLAP or other extensions?

0
 
LVL 8

Expert Comment

by:nagki
ID: 13731544
Hi astankovic,
can u post what error my query is giving as it worked fine for me

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question