troubleshooting Question

Problem deleting records returned by a common table expression in db2

Avatar of DaveChoiceTech
DaveChoiceTech asked on
DB2
4 Comments1 Solution1531 ViewsLast Modified:
I apologize in advance for the involved query below but I couldn't find a way to make it clearer.

When I run this query it selects the MAP_TRANCODE_ID's to be removed

----------------
with CTE1 as
(
select MAP_TRANCODE_ID, MAP_ID, TRANCODE, MAP_TYPE, DELETE_FLAG, FILE_GROUP_CODE, CATEGORY, FILE_FORMAT_ID, DATE_FORMAT_OPTION, MATCHING_KEY_ID, MAKE_ACCOUNT_OPTION, row_number()
over (PARTITION BY MAP_ID, TRANCODE, MAP_TYPE, DELETE_FLAG, FILE_GROUP_CODE, CATEGORY, FILE_FORMAT_ID, DATE_FORMAT_OPTION, MATCHING_KEY_ID, MAKE_ACCOUNT_OPTION order by MAP_TRANCODE_ID) cte1_RN
from TLF_MAP_TRANCODE
where DELETE_FLAG = 'N'
)
,
CTE2 as
(
select cte1_2.MAP_TRANCODE_ID, cte1_2.MAP_ID, row_number() 
over (PARTITION BY cte1_2.MAP_ID) cte2_RN
FROM CTE1 cte1_1
Join CTE1 cte1_2
on cte1_2.MAP_ID = cte1_1.MAP_ID
where cte1_1.cte1_RN > 1
and cte1_2.MAP_TRANCODE_ID in
(
select MT.MAP_TRANCODE_ID AS "MT.MAP_TRANCODE_ID"
from FTC.TLF_MAP_TRANCODE MT LEFT JOIN FTC.TLF_FILE_ATTRIBUTE FA
on MT.MAP_TRANCODE_ID = FA.MAP_TRANCODE_ID
where FA.MAP_TRANCODE_ID is null
)
)

select MAP_TRANCODE_ID from TLF_MAP_TRANCODE
where MAP_TRANCODE_ID in 
(
select MAP_TRANCODE_ID FROM CTE2
where CTE2_RN = 1
)
----------------

When I replace the last part
select MAP_TRANCODE_ID from TLF_MAP_TRANCODE
where MAP_TRANCODE_ID in
(
select MAP_TRANCODE_ID FROM CTE2
where CTE2_RN = 1
)
with this:
 
delete from TLF_MAP_TRANCODE
where MAP_TRANCODE_ID in
(
select MAP_TRANCODE_ID FROM CTE2
where CTE2_RN = 1
)
I get the error
"An unexpected token "delete" was found following "ODE_ID is null  )  )".  Expected tokens may include:  "<from>".  SQLSTATE=42601"

I don't get it!
ASKER CERTIFIED SOLUTION
Gary Patterson, CISSP
Expert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros