Help with a query

[sweller29]

Hey, can someone tell me why the query below does not work correctly. It should
select all records where the "gemsid" does not exist in the subquery?



Select gemsid,
max(decode(tng_sys_nr,0299,emp_tng_stt_dt)) as class1,
max(decode(tng_sys_nr,0146,emp_tng_stt_dt)) as class2,
max(decode(tng_sys_nr,0288,emp_tng_stt_dt)) as class3,
max(decode(tng_sys_nr,0457,emp_tng_stt_dt)) as class4
from comp_trng
where gemsid not in
(
select gemsid
from ll_records
where moduleid = 15
)
group by gemsid

[paross1]

What is it doing instead, returning some of the expected records, returning unexpected records, returning no records, giving you an error?

Phil

[sweller29]

It's returning all records that fit the first query, whether or not gemsid is in the second query.

[EndoplasmicReticulum]

Try this: Select gemsid, max(decode(tng_sys_nr,0299,emp_tng_stt_dt)) as
class1, max(decode(tng_sys_nr,0146,emp_tng_stt_dt)) as class2,
max(decode(tng_sys_nr,0288,emp_tng_stt_dt)) as class3,
max(decode(tng_sys_nr,0457,emp_tng_stt_dt)) as class4 from comp_trng as t1
where gemsid not in ( select gemsid from ll_records where t1.gemsid =
ll_records.gemsid and moduleid = 15 ) group by gemsid

[sweller29]

endoplasmic,
I tried that, but received a sql command not ended properly error. I changed "from comp_trng as t1" to "from comp_trng t1" and got the same result set. Any other ideas?

[sweller29]

Someone in the office has helped me out. Added a rtrim() to the query and it
works fine. Seems there was extra space in one of the tables.

Select gemsid,
max(decode(tng_sys_nr,0299,emp_tng_stt_dt)) as class1,
max(decode(tng_sys_nr,0146,emp_tng_stt_dt)) as class2,
max(decode(tng_sys_nr,0288,emp_tng_stt_dt)) as class3,
max(decode(tng_sys_nr,0457,emp_tng_stt_dt)) as class4
from comp_trng
where rtrim(gemsid) not in
(
select gemsid
from ll_records
where moduleid = 15
)
group by gemsid