CODEWITHSUNDEEP
sqlplsql
vish1990
vish1990

Reputation: 384

sql query giving some error

I have a sql query and it is giving soem error saying "Group" is not used correctly. I am using one of the sql wraper creted to interact with db2 tables.

Can some one please guide me what is wrong.I can not use PIVOT so tried this.

with CTE as
(
select distinct(P.Hfan_id )as HFAN_ID,
Min(Case P.HFA_PARAM_NV When 'BUSI_WANT_CALL' Then P.HFAN_PARAM_TXT End) as BUSI_WANT_CALL,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN1_DT' Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN1_DT,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN2_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN2_DT,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN3_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN3_DT,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN4_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN4_DT,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN5_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN5_DT,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD1_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD1_RESPONS,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD2_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD2_RESPONS,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD3_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD3_RESPONS,

Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD4_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD4_RESPONS,
Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD5_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD5_RESPONS,

Min(Case P.HFA_PARAM_NV When 'CODEPT_SPOUSE_MK' Then P.HFAN_PARAM_TXT End) as CODEPT_SPOUSE_MK,
Min(Case P.HFA_PARAM_NV When 'HL_SUMMERHOUSE_MK' Then P.HFAN_PARAM_TXT End) as HL_SUMMERHOUSE_MK, 
Min(Case P.HFA_PARAM_NV When 'HL_STUDENT_LOAN' Then P.HFAN_PARAM_TXT End) as HL_STUDENT_LOAN ,
Min(Case P.HFA_PARAM_NV When 'HL_OTHER_LOAN' Then P.HFAN_PARAM_TXT End) as HL_OTHER_LOAN ,
Min(Case P.HFA_PARAM_NV When 'SHFAN_CPR_ID' Then P.HFAN_PARAM_TXT End) as SHFAN_CPR_ID ,
Min(Case P.HFA_PARAM_NV When 'SHL_STUDENT_LOAN' Then P.HFAN_PARAM_TXT End) as SHL_STUDENT_LOAN,
Min(Case P.HFA_PARAM_NV When 'SHL_OTHER_LOAN' Then P.HFAN_PARAM_TXT End) as SHL_OTHER_LOAN, 
Min(Case P.HFA_PARAM_NV When 'HL_LOANTYPE' Then P.HFAN_PARAM_TXT End) as HL_LOANTYPE,
Min(Case P.HFA_PARAM_NV When 'HL_LOAN_AMOUNT' Then P.HFAN_PARAM_TXT End) as HL_LOAN_AMOUNT,
Min(Case P.HFA_PARAM_NV When 'HL_PROPERTY_TYPE' Then P.HFAN_PARAM_TXT End) as HL_PROPERTY_TYPE


Group by P.hfan_id, P.HFAN_PARAM_TXT, P.HFA_PARAM_NV
having
P.hfan_id in
(14835145,
14697134,
14697430,
14694884,
14694999,
14693122,
14693074)
)

Select * from P1,

S.HFAS_EMAIL, 
S.HFAS_PRIV_MOB_NR,
Q.HFAS_ÆGTESTIL_TP, 
R.HFAN_BØRN_HJEM_AN, 
R.HFAN_TO_LÅNTAG_MK

from CTE P1 
inner join hf.HF_ANSØGER_S Q
on P1.hfan_id = Q.hfan_id 
inner join hf.HF_ANSØGNING_S R
on P1.hfan_id = R.hfan_id
inner join hf.HF_ANSØGER1_S S
on P1.hfan_id=S.hfan_id

Error- "Group" is not used correctly.

Upvotes: 0

Views: 67

Answers (5)

vish1990
vish1990

Reputation: 384

Thanks Everyone for your answer.The working solution (just in case): 

   with CTE as
    (
    select P.Hfan_id as HFAN_ID,
    Min(Case P.HFA_PARAM_NV When 'BUSI_WANT_CALL' Then P.HFAN_PARAM_TXT End) as BUSI_WANT_CALL,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN1_DT' Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN1_DT,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN2_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN2_DT,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN3_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN3_DT,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN4_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN4_DT,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILDREN5_DT'Then P.HFAN_PARAM_TXT End) as HFAN_CHILDREN5_DT,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD1_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD1_RESPONS,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD2_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD2_RESPONS,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD3_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD3_RESPONS,

    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD4_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD4_RESPONS,
    Min(Case P.HFA_PARAM_NV When 'HFAN_CHILD5_RESPONS' Then P.HFAN_PARAM_TXT End) as HFAN_CHILD5_RESPONS,

    Min(Case P.HFA_PARAM_NV When 'CODEPT_SPOUSE_MK' Then P.HFAN_PARAM_TXT End) as CODEPT_SPOUSE_MK,
    Min(Case P.HFA_PARAM_NV When 'HL_SUMMERHOUSE_MK' Then P.HFAN_PARAM_TXT End) as HL_SUMMERHOUSE_MK, 
    Min(Case P.HFA_PARAM_NV When 'HL_STUDENT_LOAN' Then P.HFAN_PARAM_TXT End) as HL_STUDENT_LOAN ,
    Min(Case P.HFA_PARAM_NV When 'HL_OTHER_LOAN' Then P.HFAN_PARAM_TXT End) as HL_OTHER_LOAN ,
    Min(Case P.HFA_PARAM_NV When 'SHFAN_CPR_ID' Then P.HFAN_PARAM_TXT End) as SHFAN_CPR_ID ,
    Min(Case P.HFA_PARAM_NV When 'SHL_STUDENT_LOAN' Then P.HFAN_PARAM_TXT End) as SHL_STUDENT_LOAN,
    Min(Case P.HFA_PARAM_NV When 'SHL_OTHER_LOAN' Then P.HFAN_PARAM_TXT End) as SHL_OTHER_LOAN, 
    Min(Case P.HFA_PARAM_NV When 'HL_LOANTYPE' Then P.HFAN_PARAM_TXT End) as HL_LOANTYPE,
    Min(Case P.HFA_PARAM_NV When 'HL_LOAN_AMOUNT' Then P.HFAN_PARAM_TXT End) as HL_LOAN_AMOUNT,
    Min(Case P.HFA_PARAM_NV When 'HL_PROPERTY_TYPE' Then P.HFAN_PARAM_TXT End) as HL_PROPERTY_TYPE
    from hf.hf_ANSØGNINGP_S P
    where 
    P.hfan_id in
    (14835145,
    14834519,
    14697430,
    14694884,
    14694999,
    14693122,
    14693074)
    Group by P.hfan_id

    )

    Select P1.*,

    S.HFAS_EMAIL, 
    S.HFAS_PRIV_MOB_NR,
    Q.HFAS_ÆGTESTIL_TP, 
    R.HFAN_BØRN_HJEM_AN, 
    R.HFAN_TO_LÅNTAG_MK

    from CTE P1 
    inner join hf.HF_ANSØGER_S Q
    on P1.hfan_id = Q.hfan_id 
    inner join hf.HF_ANSØGNING_S R
    on P1.hfan_id = R.hfan_id
    inner join hf.HF_ANSØGER1_S S
    on P1.hfan_id=S.hfan_id

Upvotes: 0

malc
malc

Reputation: 147

You have to include the columns you are "GROUPing BY" in the SELECT Statement. You need to add P.HFAN_PARAM_TXT to the Select statement as a separate column

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

As I mentioned in a comment, you are missing the from clause in the CTE. In addition, you should be filtering in a where clause not the having. And the group by doesn't look write (hint: select distinct is almost never needed in an aggregation query).

So, I think it should look like this:

with CTE as (
      select P.Hfan_id,
             . . .
      from p
      where P.hfan_id in (14835145, 14697134, 14697430, 14694884, 14694999, 14693122, 14693074)
      Group by P.hfan_id
     )

Upvotes: 2

NikNik
NikNik

Reputation: 2301

You can't use distinct with GROUP BY... and as others said, FROM is missing

Upvotes: 0

Collatrl
Collatrl

Reputation: 171

You have

select distinct(P.Hfan_id )as HFAN_ID,
...
(THERE SHOULD BE A FROM STATEMENT HERE)
group by P.hfan_id, P.HFAN_PARAM_TXT, P.HFA_PARAM_NV

You need to have a FROM between those 2. Try that and see if it helps.

Upvotes: 0

Related Questions