Can I use parameter in SQL Select clause

[uouser]

 I am trying to run a query in which my parameter is used in the select clause of the query. The query i am using is similar to the one i have pasted in code area. Anybody having a clue how to use parameter in select clause? When i read fields, it puts null instead of the parameter value.

The query is parsed successfully and when i run the report i got error while filling the report which is "Unknown column name : if(date_of_invoice >=  null, '1', '0')"

Please help. Thanks.

 

 

Code:

SELECT if(date_of_invoice >=  $P{para}, '1', '0')FROM supplier_payment_invoicesWHEREBlock_ID = 10

[uouser]

Can anybody reply on it please ... it is the second post I have made and no reply :( ... seems like a DEAD forum :(.

[gglulu]

hi

can you say which data base ? 

I think you can use the other sql syntaxe example :

 

 

Code:

SELECT CASE WHEN (<column_value>= <value>) THEN            WHEN (<column_value> = <value>) THEN            ELSE <value>FROM <table_name>;

[Gaby38]

 I think that your problem is not a problem of sql syntax. Your parameter must be set before. As it is used in the parameter form, I think that none of the reports parameters are set at this time. so your parameter must come from a previous input control.

This kind of controls are named "cascading controls" and have been released for the first time in the 3.7 release.

Where do you set your "para" parameter in your report? In a previous input control ??

[uouser]

Thanks for your reply gglulu .... I am using MySQL .... I tried this but no success .... it again puts null when processed .....I tried to put default value as welll ... it works with default value but then it never takes the value i entered at run time ..... it only produced report every time with default value ..... Anything else you may think of ... or is it not allowed to use parameter in select clause ???

[uouser]

Thanks for your reply Gaby38 ... I am taking the value of 'para' at run time ... i tried with default value but then it always produce the report with the default value whatever the input i gave at run time

[caprihan]

 Hi,

I need to use a Parameter in the Select part of my SQL as showin in the code window

Unfortunately when I run the report AND provide values for the parameter, I get an exception such as

 

Error filling print... Unknown column name : if(tvcr.MEMBER_STAY_FROMDATE BETWEEN DATE_SUB(null, INTERVAL '4' DAY) AND null, DATE_FORMAT(tvcr.MEMBER_STAY_FROMDATE, '%m/%d'), CONCAT('< ', DATE_FORMAT(DATE_SUB(null, INTERVAL '4' DAY), '%m/%d')))
net.sf.jasperreports.engine.JRException: Unknown column name : if(tvcr.MEMBER_STAY_FROMDATE BETWEEN DATE_SUB(null, INTERVAL '4' DAY) AND null, DATE_FORMAT(tvcr.MEMBER_STAY_FROMDATE, '%m/%d'), CONCAT('< ', DATE_FORMAT(DATE_SUB(null, INTERVAL '4' DAY), '%m/%d'))) 
    at net.sf.jasperreports.engine.JRResultSetDataSource.getColumnIndex(JRResultSetDataSource.java:355) 
....

 

Can anyone please provide me a workaround if it exists?

 

Thanks in advance,

Gaurav

Code:

select ci.INSTANCE_SUFFIX,if(tvcr.MEMBER_STAY_FROMDATE BETWEEN DATE_SUB($P{To_Date}, INTERVAL '4' DAY) AND $P{To_Date},DATE_FORMAT(tvcr.MEMBER_STAY_FROMDATE, '%m/%d'),CONCAT('< ', DATE_FORMAT(DATE_SUB($P{To_Date}, INTERVAL '4' DAY), '%m/%d'))), IFNULL(tvcr.MEMBER_CHILDREN_COUNT,'0') + 1from sms_activities.tvc_record tvcr, sms.community_instance ciwhere event_id = $P{Event_ID}and ci.instance_id = tvcr.member_region_idand tvcr.member_stay_fromdate >= $P{From_Date}and tvcr.member_stay_fromdate <= $P{To_Date}

[uouser]

Gaurav,

 

Unfortunately nothing, above, worked for me. I think i used sub-report to resolve this issue. May be that is of some help.

 

Regards,

Sumair

[skandasamy23]

You can use like below.

 

select col1,col2... $P!{parameter} from table1