darrgyas
asked on
db2 sp and "with"
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Member_2_2484401
I wonder if it's throwing an error because TIMESTAMP is a reserved word.
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.
the "with a as" is then self-joined so the intention is good (to re-use) but you might use a cursor instead.
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
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
ASKER
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;
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;
ASKER
Thank you!