?
Solved

db2 sp and "with"

Posted on 2013-02-06
6
Medium Priority
?
575 Views
Last Modified: 2013-02-07
Hi all,

I have an SP, and this part of it:

if exists (
      With a as ( select timestamp, row_number() over (order by timestamp desc) as rown,
      mjid,
      level from dbo.fm_fuel order by Timestamp fetch first 2 rows only)
      Select t1.timestamp, t1.level, t2.timestamp, t2.level
      from a t1 inner join a t2 on t1.rown=t2.rown+1 where t1.mjid=3199 and (t2.level - t1.level) > 9;
) then
INSERT INTO DBO.FM_REFUEL (TIMESTAMP, MJID, CHID, LEVEL, ODOMETER)
                                                                VALUES  
                                                                (CDTWHEN, IMJID, CCHID, iLEVEL, iODOMETER);
end if;

gives me this error:

ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=as;if
exists (
      With a;JOIN, DRIVER=3.57.82
Error Code: -104

The statament works by itself, but how do I make it work inside a stored procedure?

Any help is appreciated, thank you
0
Comment
Question by:darrgyas
[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
6 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 38861180
Hi  darrgyas ,

You can't use the *with* structure this way.  :(

Either drop the with construct (using just select ... from ...) or write the query as a MERGE ... WHEN NOT MATCHED INSERT ....



Kent
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 38861183
I wonder if it's throwing an error because TIMESTAMP is a reserved word.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38862280
I like both answers :-) but I guess fm_fuel.timestamp is a field (so I presume it was allowed) - however I would certainly always nominate the table or alias against that field!

the "with a as" is then self-joined so the intention is good (to re-use) but you might use a cursor instead.
0
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.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38862466
Hi darrgyas ,

There are subtle differences between client submitted SQL and the SQL used in Stored Procedures, as well as the major differences of objects allowed only in one or the other.  I've long been a supporter of good formatting, and have taken the liberty of modifying yours for easier (to me) readability.  It occurs to me that if the timestamp field is indexed AND it is guaranteed unique (not necessarily true if the table contains a transaction log) that a small rewrite gets you the answers a lot faster.

if exists
(
  With a as
  (
    select timestamp, row_number() over (order by timestamp desc) as rown, mjid, level
    from dbo.fm_fuel
    order by Timestamp
    fetch first 2 rows only
  )
  Select t1.timestamp, t1.level, t2.timestamp, t2.level
  from a t1
  inner join a t2
    on t1.rown=t2.rown+1
  where t1.mjid=3199
    and (t2.level - t1.level) > 9;
) then
  INSERT INTO DBO.FM_REFUEL (TIMESTAMP, MJID, CHID, LEVEL, ODOMETER)
  VALUES  (CDTWHEN, IMJID, CCHID, iLEVEL, iODOMETER);
end if;

Try this small change:

DECLARE row_count INTEGER;

With a as
(
  select timestamp, row_number() over (order by timestamp desc) as rown, mjid, level
  from dbo.fm_fuel
  order by Timestamp
  fetch first 2 rows only
)
SELECT count(*)  INTO row_count
FROM a t1
  INNER JOIN a t2
    ON t1.rown=t2.rown+1
  WHERE t1.mjid=3199
    AND (t2.level - t1.level) > 9;

IF (row_count > 0) THEN
  INSERT INTO DBO.FM_REFUEL (TIMESTAMP, MJID, CHID, LEVEL, ODOMETER)
  VALUES  (CDTWHEN, IMJID, CCHID, iLEVEL, iODOMETER);
end if;


I'm still not sure if that WITH syntax will work in the stored procedure.  With so many code bases and versions for DB2, it's dependent on what you have.



Kent
0
 

Author Comment

by:darrgyas
ID: 38863658
Kent,
You're right, it still doesn't work:

ERROR: A character, token, or clause is invalid or missing.
DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=With a
as
(
  select timestamp, ro;M.SYSDUMMY1;
0
 

Author Closing Comment

by:darrgyas
ID: 38863659
Thank you!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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