Multiple values inside IN clause in a select statement?
13 December, 2011
Hi,
I was trying to get multiple values inside IN clause in a select statement.
( the line mentioned on page 283 of guide_user_v5.2.pdf
If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)
)
Using the same example i didn't get multiple values.
When it came to 'Report filter' page by default it selected "equal to" filter, and there is no choice for user to select different type of filter.
Basically I wanted to get a multiple value filter so user can select multiple values.
Pls guide me on this.
Regards,
Anand
I was trying to get multiple values inside IN clause in a select statement.
( the line mentioned on page 283 of guide_user_v5.2.pdf
If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)
)
Using the same example i didn't get multiple values.
When it came to 'Report filter' page by default it selected "equal to" filter, and there is no choice for user to select different type of filter.
Basically I wanted to get a multiple value filter so user can select multiple values.
Pls guide me on this.
Regards,
Anand
Hi Anand,
To be able to enter multiple values you will need to create a query with multiple parameters. e.g. select * from person where region in ({?},{?},{?})
Let me know if that achieves what you were after.
Regards,
Steven
To be able to enter multiple values you will need to create a query with multiple parameters. e.g. select * from person where region in ({?},{?},{?})
Let me know if that achieves what you were after.
Regards,
Steven
1) I am not sure that how many values can go inside the IN Clause, basically it should by itself refer from the underlying query or table and self populate all the values. For e.g if there are 190 countries in the underlying table or views it should populate those by itself. The user should not go and populate ({?},{?},{?},{?},{?},{?}........) till 190 times.
2) On the report filter page it by default it selects "equal to" filter and user does not option to select any other type of filter like in range, between etc.
user should be able to define other type of filter.
Regards,
Anand
2) On the report filter page it by default it selects "equal to" filter and user does not option to select any other type of filter like in range, between etc.
user should be able to define other type of filter.
Regards,
Anand
Hi Anand,
1) When using freehand SQL reports, you cannot have a filter that dynamically increases while still allowing it to be user prompt.
However if you have all the values for country already in a column then you can just create a drag-and-drop report and use that field as a filter. This allows you to cache that column and use it as a inlist filter , please see the following on the wiki for more information on filters. filters 1 filters 2
2) The filter operator is specified as part of the SQL , so if you would like it to be equal to, between etc.. you must specify this in the sql .
You cannot modify the filter outside of the freehand sql.
If you would like to modify the filter you would need either write it in your query or use a drag and drop report.
Regards,
Steven
1) When using freehand SQL reports, you cannot have a filter that dynamically increases while still allowing it to be user prompt.
However if you have all the values for country already in a column then you can just create a drag-and-drop report and use that field as a filter. This allows you to cache that column and use it as a inlist filter , please see the following on the wiki for more information on filters. filters 1 filters 2
2) The filter operator is specified as part of the SQL , so if you would like it to be equal to, between etc.. you must specify this in the sql .
You cannot modify the filter outside of the freehand sql.
If you would like to modify the filter you would need either write it in your query or use a drag and drop report.
Regards,
Steven
Hi Steven,
Thanks for your replies. But it will not solve my problem. I have to create a report using Accumulating Sum and for this I have to use freehand sql. I cannot use drag and drop option. Moreover I have to give an IN List kind of filter to user where he can select multiple values. For e.g he should be able to select any combination of two values (TV, Radio) including All option as it comes when we define a filter from Drag and Drop option.
Second thing I need is linking of filter in freehand sql. Like there are two date range but both will have same value. I want the user to pass only one filter value and let the second filter be linked to first filter.
The sample query with example is attached.
select act_date, total_ratecard total_ratecard, channel_type
from Table_tv
where act_date between '2011-01-01' and '2011-01-30' and channel_type = 'TV'
union all
select act_date, total_ratecard total_ratecard,'Radio' channel_type,
from table_radio
where act_date between '2011-01-01' and '2011-01-30' and channel_type ='Radio'
I want user can select one date range and let the other be linked to first.
Since both the date range are same and I don't want user to enter again.
For channel_type he can select either TV or Radio or both from an IN List kind of filter.
Pls suggest how it can be made with Freehand Sql.
Regards,
Anand
Thanks for your replies. But it will not solve my problem. I have to create a report using Accumulating Sum and for this I have to use freehand sql. I cannot use drag and drop option. Moreover I have to give an IN List kind of filter to user where he can select multiple values. For e.g he should be able to select any combination of two values (TV, Radio) including All option as it comes when we define a filter from Drag and Drop option.
Second thing I need is linking of filter in freehand sql. Like there are two date range but both will have same value. I want the user to pass only one filter value and let the second filter be linked to first filter.
The sample query with example is attached.
select act_date, total_ratecard total_ratecard, channel_type
from Table_tv
where act_date between '2011-01-01' and '2011-01-30' and channel_type = 'TV'
union all
select act_date, total_ratecard total_ratecard,'Radio' channel_type,
from table_radio
where act_date between '2011-01-01' and '2011-01-30' and channel_type ='Radio'
I want user can select one date range and let the other be linked to first.
Since both the date range are same and I don't want user to enter again.
For channel_type he can select either TV or Radio or both from an IN List kind of filter.
Pls suggest how it can be made with Freehand Sql.
Regards,
Anand
Hi Anand,
You can use advanced metrics when using drag and drop to apply accumulating total.
This looks like it is not possible in a freehand sql report and could only be done using the drag and drop interface where you could link the filters, set them to be displayed in a list and cache them.
Apologies for the inconvenience.
Regards,
Steven
You can use advanced metrics when using drag and drop to apply accumulating total.
This looks like it is not possible in a freehand sql report and could only be done using the drag and drop interface where you could link the filters, set them to be displayed in a list and cache them.
Apologies for the inconvenience.
Regards,
Steven
Hi Steven,
Can you pls look into the future enhancements, to support the above requirements. This will provide more flexibility for report developer.
Regards,
Anand
Can you pls look into the future enhancements, to support the above requirements. This will provide more flexibility for report developer.
Regards,
Anand