Parameters in Select Block in SQL

[yskripch]

Hello,

 

I would like to add parameters to the select block of an SQL statement. Something along the lines of :

Select Name

,job

,address

,$P{Group}

,$P{Group2}

from table

where date between $P{Start} and $P{End}

group by Name

,job

,address

,$P{Group}

,$P{Group2}

so the two parameters that are passed in will be used as a grouping method plus they need to fill in fields in the report. I have tried playing with the ! in the SQL statement but it doesnt help. The WHERE block works fine, the problem is in the GROUP BY and SELECT blocks of the statement.   IF anybody has any idea how to make this work, I  would appreciate the help. Let me know if you need more information.

[peter.galeingres.com]

Hi Yuri,

I am not 100% clear on what you are trying to acheive but here are some pointers.

If the parameters are not quoted in the query then the values passed in will be treated as column names. For example

SELECT job, $P!{Group}
FROM .......

If parameter is given the value 'foo' then the query will run as

SELECT job, foo
FROM .......

That is OK providing there is a column called foo in the table. However you also have to have a field ($F{...}) in the report with the same column name. What you dont want is lots of fields to cater for every possible value of Group. So you need an AS clause.

SELECT job, $P!{Group} AS group
FROM .......

Now you can have a field called $F{group} in the report to receive the value. But that field has to have a data type that is compatible with the column that is being returned. Strings will work for a lot of datatypes but that could get messy and I dont see an easy solution.
The parameter names can be included in the GROUP BY clause providing the values pssed in are valid column names.

If your intention was to use the values passed in as string literals then all you need to do is quote the paremeters

SELECT job, '$P!{Group}'
FROM .......

In this case your query will return the value of $P{Group} for each row

job1        foo
job2        foo
job3        foo

You are not allowed to include quoted literals in the GROUP BY clause.

Note that $P! is used throughout.

HTH

Peter Gale
Ingres Corporation

 

[yskripch]

The first case you described is the one that I needed. Thanks for the quick reply. I will try it out now.

[yskripch]

Thanks for the way to make it work..I dont know why I didnt think of it. lol..Thanks again.