Passing Parameters in Group by clause

[ashishus1]

Hi , 

 

 I am using SQL query for my report design and the query is as shown below
 

select

case

when upper($P{Date_Type})= 'Y'

then to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'Q'

then to_char(enqh.enqr_date_time,'Q')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'M'

then to_char(enqh.enqr_date_time,'MON')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'W'

then to_char(enqh.enqr_date_time,'WW')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'D'

then to_char(enqh.enqr_date_time, 'DD-MON-YYYY')

end

as G

from enqr_search_header enqh

(select min(quote_amount) value, FK_ENQR_SEARCH_HDR_ID from enqr_search_results group by FK_ENQR_SEARCH_HDR_ID ) enqrs,

gm_services_types prd,

bkng_header bh,

gm_customer_profile cust ,

otp_company_comm corp,

gm_cust_address gca,

gm_city gc,

gm_ith_employees emp

where enqh.pk_enqr_search_hdr_id = enqrs.fk_enqr_search_hdr_id

and enqh.fk_service_type_id = prd.pk_service_type_id

and enqh.fk_edocket_id = bh.pk_edocket_id

and bh.FK_CUST_PROFILE_ID = cust.PK_GM_CUST_PROFILE_ID

and cust.corp_dtl_id = corp.client_code

and cust.pk_gm_cust_profile_id = gca.fk_gm_cust_profile_id

and gca.fk_addresstype_domtypval_id = 10136 -- office address and 10135 for home address

and gc.gm_city_id=gca.fk_cust_city_id

and emp.userid=enqh.fk_inserted_user_id

group by

case

when upper($P{Date_Type})= 'Y'

then to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'Q'

then to_char(enqh.enqr_date_time,'Q')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'M'

then to_char(enqh.enqr_date_time,'MON')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'W'

then to_char(enqh.enqr_date_time,'WW')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P{Date_Type})= 'D'

then to_char(enqh.enqr_date_time, 'DD-MON-YYYY')

end

 

I have case in my select as well as group by as shown in Blue colour . 

My concern is it is giving me "not a group by expression error " in the group by clause . how to overcome this ?

Pls someone kindly let me know ...... It will be very helpful 

[cmatthews]

 All your parameters in the query expression section need to contain a bang " ! ". You don't use the bang much elsewhere but this is what tells the report engine to do what you want in the report query.

You need it throughout your query but ... here is a small sample of what needs updating.

group by

 

 

case

when upper($P!{Date_Type})= 'Y'

then to_char(enqh.enqr_date_time,'YYYY')

when upper($P!{Date_Type})= 'Q'

then to_char(enqh.enqr_date_time,'Q')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P!{Date_Type})= 'M'

then to_char(enqh.enqr_date_time,'MON')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P!{Date_Type})= 'W'

then to_char(enqh.enqr_date_time,'WW')||to_char(enqh.enqr_date_time,'YYYY')

when upper($P!{Date_Type})= 'D'

then to_char(enqh.enqr_date_time, 'DD-MON-YYYY')

end

 

Good luck,

Clark

[ashishus1]

 Hi Clark , 

 

 Thanks for reply . Now It is asking me to pass a default value expression for the parameter "Date Type" and when I pass a string 'y' as default parameter , its throwing me "Invalid column type" error . Kinddly help in this