Construct of select and subselect

Toad for IBM DB2
1

I would like some help with the following select statement.

JDBC/ODBC Bridge
DB2/400 SQL

I get the error [Code: -199, SQL State: 37000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword IN not expected. Valid tokens: + -.

select acctno, ratcod, ssno
from (select acctno, ratcod, ssno from resdat19.lnmast lnmast
group by ssno
having sum(case ratcod in (6, 7 ,8) then 1 else 0 end) > 0
and sum(case ratcod in (6, 7, 8) then 0 else 1 end) > 0
)
as ASum
JOIN
ON lnmast.ssno = ASum.ssno

2

try “case when ratcod in (6, 7, 8)…”

3
  • Error still -

[Code: -104, SQL State: 37000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token . was not valid. Valid tokens: ON FULL LEFT CROSS INNER RIGHT USING EXCEPTION.

select acctno, ratcod, ssno
from (select acctno, ratcod, ssno from resdat19.lnmast lnmast
group by ssno
having sum(case when ratcod in (6, 7 ,8) then 1 else 0 end) > 0
and sum(case when ratcod in (6, 7, 8) then 0 else 1 end) > 0
)
as ASum
JOIN
ON lnmast.ssno = ASum.ssno