oracle - max with case

select case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else max(cntrctr_lcns_seq_no)
                                   end as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id

I thi开发者_如何学运维nk you an see what i'm trying to do. Get the max seq_no for a specific id. But I get the error "not a single group group clause".

This select statement is part of a larger insert.

Thanks!

update: this is the whole statement

insert into nuwmsweb.CNTRCTR_LCNS_INFO
    (third_party_id,cntrctr_lcns_seq_no,cntrctr_lcns_no,
    lcns_st_cd,certfn_level_type_cd,cntrctr_type_cd,cut_tap_authy_ind,
    stat_type_nm)
    VALUES(thirdPartyId,(select max(case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else cntrctr_lcns_seq_no
                                   end) as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id
                                    ),
          licenseNumber,licenseState,licenseLevel,licenseType,cutTap,status);

---

The max aggregate function will ignore null values, so you don't need the case statement or the group by as you want the max over the entire returned set.

select
    coalesce(max(cntrctr_lcns_seq_no), 1) as cntrctr_lcns_seq_no
from nuwmsweb.cntrctr_lcns_info
where third_party_id = thirdPartyId

---

I think you want:

select coalesce (max(cntrctr_lcns_seq_no),1) as cntrctr_lcns_seq_no
  from nuwmsweb.cntrctr_lcns_info
 where third_party_id = thirdPartyId

(or you can use Oracle's nvl instead of ANSI's coalesce if you prefer).

---

Try this:

select  max(case when cntrctr_lcns_seq_no is null then 1
        else cntrctr_lcns_seq_no end) as cntrctr_lcns_seq_no
from nuwmsweb.cntrctr_lcns_info
where third_party_id = thirdPartyId                                     
group by third_party_id 

---

Edit :

The error you are getting is because you are not including the third_party_id in the group by clause.

Try...

select max(case when cntrctr_lcns_seq_no is null
                                   then 1
                                   else cntrctr_lcns_seq_no
                                   end) as cntrctr_lcns_seq_no
                                   from nuwmsweb.cntrctr_lcns_info
                                    where third_party_id = thirdPartyId
                                    group by third_party_id

If you really want to use this syntax for a different (more complicated query). Also, you should add the third_party_id in the group by clause.

But max already ignores nulls, so wouldn't a query like this be more descriptive of what you are trying to do?

select third_party_id, max(nvl(cntrctr_lcns_seq_no,1))
        from nuwmsweb.cntrctr_lcns_info
        where third_party_id = thirdPartyId
        group by third_party_id