Home » Archived » BIRT » Can not send parameter to tablefunc with postgres 8.0.4 , Eclipse 3.2, BIRT 2.0 --> crosstab
| Can not send parameter to tablefunc with postgres 8.0.4 , Eclipse 3.2, BIRT 2.0 --> crosstab [message #147233] |
Wed, 22 March 2006 21:11  |
Eclipse User |
|
|
|
Originally posted by: czarft.gmail.com
Can not send parameter to tablefunc with postgres 8.0.4 , Eclipse 3.2, BIRT
2.0 --> crosstab
Hello, Every body.
I tried to use tablefunc for crosstab with BIRT by postgres 8.0.4, Eclipse
3.2, BIRT 2.0, it can work but no any parameters.
I want to send parameter to specify date between, but BIRT can not see
question mark in query of parameter to crosstab(text, text) function.
Who can tell me any way to use it.
____________________________________________________________ ________________
____________________________________
Part of query case 1
visitinfo.stime BETWEEN ? AND ? --> It can not work.
____________________________________________________________ ________________
____________________________________
Part of query case 2
visitinfo.stime BETWEEN ''2005-12-01 00:00:00'' AND ''2005-12-31
23:59:59'' --> It can work
____________________________________________________________ ________________
____________________________________
In query of Data Set...
SELECT * FROM crosstab(
'SELECT
visitinfo.coveragemain AS coveragemain,
coveragedetailtype.name AS coveragedetailtype_name,
CAST(EXTRACT(DAY FROM visitinfo.stime) AS NUMERIC) AS day_of_stime,
COUNT(visitinfo.id) AS visit_number
FROM
((visitinfo LEFT OUTER JOIN coveragedetail ON visitinfo.coveragemain =
coveragedetail.id)
LEFT OUTER JOIN coveragedetailtype ON coveragedetail.type =
coveragedetailtype.id)
WHERE
visitinfo.stime BETWEEN ? AND ?
GROUP BY
coveragemain,
coveragedetailtype_name,
day_of_stime
ORDER BY 1, 2, 3;',
'SELECT day FROM day_of_date ORDER BY 1;'
)
AS
ct(
coveragemain INTEGER,
coveragedetailtype_name TEXT,
day1 NUMERIC,
day2 NUMERIC,
day3 NUMERIC,
day4 NUMERIC,
day5 NUMERIC,
day6 NUMERIC,
day7 NUMERIC,
day8 NUMERIC,
day9 NUMERIC,
day10 NUMERIC,
day11 NUMERIC,
day12 NUMERIC,
day13 NUMERIC,
day14 NUMERIC,
day15 NUMERIC,
day16 NUMERIC,
day17 NUMERIC,
day18 NUMERIC,
day19 NUMERIC,
day20 NUMERIC,
day21 NUMERIC,
day22 NUMERIC,
day23 NUMERIC,
day24 NUMERIC,
day25 NUMERIC,
day26 NUMERIC,
day27 NUMERIC,
day28 NUMERIC,
day29 NUMERIC,
day30 NUMERIC,
day31 NUMERIC
);
____________________________________________________________ ________________
____________________________________
Yours sincerely
Report Developer
|
|
|
| Re: Can not send parameter to tablefunc with postgres 8.0.4 , Eclipse 3.2, BIRT 2.0 --> crosstab [message #150069 is a reply to message #147233] |
Fri, 31 March 2006 23:23  |
| Eclipse User |
|
|
|
"news.eclipse.org" <czarft@gmail.com> wrote in message
news:dvt037$g85$2@utils.eclipse.org...
> Can not send parameter to tablefunc with postgres 8.0.4 , Eclipse 3.2,
> BIRT
> 2.0 --> crosstab
>
> Hello, Every body.
> I tried to use tablefunc for crosstab with BIRT by postgres 8.0.4, Eclipse
> 3.2, BIRT 2.0, it can work but no any parameters.
> I want to send parameter to specify date between, but BIRT can not see
> question mark in query of parameter to crosstab(text, text) function.
> Who can tell me any way to use it.
>
> ____________________________________________________________ ________________
> ____________________________________
> Part of query case 1
> visitinfo.stime BETWEEN ? AND ? --> It can not work.
> ____________________________________________________________ ________________
> ____________________________________
> Part of query case 2
> visitinfo.stime BETWEEN ''2005-12-01 00:00:00'' AND ''2005-12-31
> 23:59:59'' --> It can work
> ____________________________________________________________ ________________
> ____________________________________
> In query of Data Set...
>
> SELECT * FROM crosstab(
> 'SELECT
> visitinfo.coveragemain AS coveragemain,
> coveragedetailtype.name AS coveragedetailtype_name,
> CAST(EXTRACT(DAY FROM visitinfo.stime) AS NUMERIC) AS day_of_stime,
> COUNT(visitinfo.id) AS visit_number
> FROM
> ((visitinfo LEFT OUTER JOIN coveragedetail ON visitinfo.coveragemain =
> coveragedetail.id)
> LEFT OUTER JOIN coveragedetailtype ON coveragedetail.type =
> coveragedetailtype.id)
> WHERE
> visitinfo.stime BETWEEN ? AND ?
> GROUP BY
> coveragemain,
> coveragedetailtype_name,
> day_of_stime
> ORDER BY 1, 2, 3;',
> 'SELECT day FROM day_of_date ORDER BY 1;'
> )
> AS
> ct(
> coveragemain INTEGER,
> coveragedetailtype_name TEXT,
> day1 NUMERIC,
> day2 NUMERIC,
> day3 NUMERIC,
> day4 NUMERIC,
> day5 NUMERIC,
> day6 NUMERIC,
> day7 NUMERIC,
> day8 NUMERIC,
> day9 NUMERIC,
> day10 NUMERIC,
> day11 NUMERIC,
> day12 NUMERIC,
> day13 NUMERIC,
> day14 NUMERIC,
> day15 NUMERIC,
> day16 NUMERIC,
> day17 NUMERIC,
> day18 NUMERIC,
> day19 NUMERIC,
> day20 NUMERIC,
> day21 NUMERIC,
> day22 NUMERIC,
> day23 NUMERIC,
> day24 NUMERIC,
> day25 NUMERIC,
> day26 NUMERIC,
> day27 NUMERIC,
> day28 NUMERIC,
> day29 NUMERIC,
> day30 NUMERIC,
> day31 NUMERIC
> );
> ____________________________________________________________ ________________
> ____________________________________
>
> Yours sincerely
> Report Developer
>
>
Hello, Every Body.
This problem, I can solve by usin Property Binding in Data Set By use this
Query and 2 parameters.
____________________________________________________________ ___________________
"SELECT * FROM crosstab( 'SELECT visitinfo.coveragemain AS coveragemain,
coveragedetailtype.name AS coveragedetailtype_name, CAST(EXTRACT(DAY FROM
visitinfo.stime) AS NUMERIC) AS day_of_stime, COUNT(visitinfo.id) AS
visit_number FROM ((visitinfo LEFT OUTER JOIN coveragedetail ON
visitinfo.coveragemain = coveragedetail.id) LEFT OUTER JOIN
coveragedetailtype ON coveragedetail.type = coveragedetailtype.id) WHERE
visitinfo.stime BETWEEN ''" + params["date_start"] + "'' AND ''" +
params["date_end"] + "'' GROUP BY coveragemain, coveragedetailtype_name,
day_of_stime ORDER BY 1, 2, 3;', 'SELECT day FROM day_of_date ORDER BY 1;' )
AS ct( coveragemain INTEGER, coveragedetailtype_name TEXT, day1 NUMERIC,
day2 NUMERIC, day3 NUMERIC, day4 NUMERIC, day5 NUMERIC, day6 NUMERIC, day7
NUMERIC, day8 NUMERIC, day9 NUMERIC, day10 NUMERIC, day11 NUMERIC, day12
NUMERIC, day13 NUMERIC, day14 NUMERIC, day15 NUMERIC, day16 NUMERIC, day17
NUMERIC, day18 NUMERIC, day19 NUMERIC, day20 NUMERIC, day21 NUMERIC, day22
NUMERIC, day23 NUMERIC, day24 NUMERIC, day25 NUMERIC, day26 NUMERIC, day27
NUMERIC, day28 NUMERIC, day29 NUMERIC, day30 NUMERIC, day31 NUMERIC );"
____________________________________________________________ ___________________
The hightlight is this below text.
"...BETWEEN ''" + params["date_start"] + "'' AND ''" + params["date_end"] +
"'' GROUP ..."
____________________________________________________________ ___________________
|
|
|
Goto Forum:
Current Time: Sat May 10 21:07:27 EDT 2025
Powered by FUDForum. Page generated in 0.03008 seconds |
Home