[Passing Parameters in Group by clause]

 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 

[Passing Parameters in Group by clause]

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