Parameter in Sub Queries

birtseeker · 2011-04-28T23:47:18+00:00

Hi, I am using JDBC MS SQL connector to fetch data from MS SQL 2005 database. I need to pass report parameter to sub query. Following is my query.. select f1, f2, f3, ( select sum (f11), f12 from table1 as table 12 where table12.f12 >= ? -- here I need to pass report…

birtseeker

Hi,

I am using JDBC MS SQL connector to fetch data from MS SQL 2005 database. I need to pass report parameter to sub query. Following is my query..
select f1,
f2,
f3,
( select sum (f11),
f12
from table1 as table 12
where table12.f12 >= ? -- here I need to pass report parameter
)
from table1
where table1.f1 >= ?
and table1.f2 >= ?
-- Both are same tables
I want query same table in sub query and pass report parameter in sub query. how to do this?

Any help appreciated....

Regards,

VIkram

Find more posts tagged with

Comments

Hans_vd

Hi VIkram,

Querying the same table in the main query and the subquery is perfectly possible.
Passing a report parameter to a subquery is perfectly possible.

But your query is syntactically not correct.

Can you describe what you are trying to achieve?
If you place the subquery in the select clause, that column will show the same value for each row in your selection. Is that what you want?
Also, you cannot select the column f12 like this. It cannot be combined with an aggregation without grouping. And I'm also quite sure that you cannot select more than 1 column in a subquery in the select clause.

It could be helpfull if you show some data that is in your table and how you want the result of the query to be, so that I can help you constructing the query.

Regards
Hans

birtseeker

Thanks hans for your reply.

Actually I am developing one Cross Tab Report for Finance Module. I have report parameters as Year and Period. On report I want deprecition amount in a given range of Year and Period. This I achive by single normal query. Now I need one more field as "Depreciation Upto Period" which is sum of amount till from Year and Perid.

My report will looks like this.
Input Range : Year From 2010 To 2010
Period From 4 To 6
| Period 4|Period 5|Period 6|
|Asset Code |Depreciation Amount upto Period | [amount]|[Amount]|[Amount]|

For this Depreciation Amount upto Period, I am using sub query. When I hard code value to year and Period in subquery as Year <= 2010 and period < 3, It is giving me proper result. Now I want to give these Year and Period are report Paramete to the sub query as Report Parameter (Year From nad Period From).

Hope Now you have schenario clear.

Regards,

VIkram

Hans_vd

Hi VIkram,

You have to create as much parameters on your dataset as there are question marks in your query. So if you have two parameters Year and Period and you want to use both parameters twice in the query, you'll have to create 4 parameters and link your 2 report parameters twice to the dataset.
The order in which the question marks appear in the query has to match the order of the dataset parameter definitions.

Does this solve your problem?

Regards
Hans

birtseeker

select tffam100.t_anbr,
tffam100.t_aext,
tffam100.t_ctgy,
tffam100.t_desc,
tffam100.t_srvc,
tffam100.t_cost,
tffam110.t_book,
tffam110.t_life,
tffam800.t_amnt_1,
tffam800.t_year,
tffam800.t_prod,
( select sum (tffam8001.t_amnt_1)
from dbo.ttffam100225 as tffam1001
Inner Join dbo.ttffam110225 as tffam1101
on (tffam1101.t_anbr=tffam1001.t_anbr)
and (tffam1101.t_aext=tffam1001.t_aext)
Inner Join dbo.ttffam800225 as tffam8001
on (tffam8001.t_anbr=tffam1001.t_anbr)
and (tffam8001.t_aext=tffam100.t_aext)
where tffam1001.t_anbr = tffam100.t_anbr
and tffam1001.t_aext = tffam100.t_aext
and tffam1001.t_ctgy = tffam100.t_ctgy
and tffam1101.t_book = tffam1101.t_book
and tffam8001.t_year <= ? -- If I hard code 2010 and 6 here I get desired output
and tffam8001.t_prod < ?
and tffam800.t_atty = 3
group by tffam1001.t_anbr, tffam1001.t_aext
) as dpAmount
from dbo.ttffam100225 as tffam100 Inner Join dbo.ttffam110225 as tffam110
on (tffam110.t_anbr=tffam100.t_anbr)
and (tffam110.t_aext=tffam100.t_aext)
Inner Join dbo.ttffam800225 as tffam800
on (tffam800.t_anbr=tffam100.t_anbr)
and (tffam800.t_aext=tffam100.t_aext)
where tffam100.t_anbr >= ?
and tffam100.t_aext >= ?
and tffam100.t_ctgy >= ?
and tffam110.t_book >= ?
and tffam800.t_year >= ?
and tffam800.t_prod >= ?
order by tffam100.t_anbr, tffam100.t_aext, tffam800.t_prod

This is My query. I have created 8 parameters in following sequence:
yearfrom, periodfrom, anbrf, aextf, ctgyf, bookf, yearf, periodf
and creating filters as follows:
yearfrom <= r_yearf, periodfrom < r_periodf, anbrf between r_anbrf and r_anbrt ....
yearf between r_yearf and r_yeart, periodf between r_periodf and r_periordt

Now It is not displaying any result.

Regards,

VIkram

Hans_vd

VIkram,

What is the purpose of the filters? Aren't the parameters in the query enough to do your selection?
Are these filters placed on the dataset or on a table?
What happens if you remove the filters?

Regards
Hans

birtseeker

Thanks Hans for your promp reply.

It is working fine. I am new to this tool but now find very comfortable to this. I want to know when we have to use filter. I found there is no link betwen parameter and filter, then how they get connected at runtime. Is sequence is the only key for them?

I have one more query regarding formatting of cross tab. There are 3 field in to the group 1. I need to give header to them. This is my output layout.

I need Asset Code, Extension and DP Amount upto period as header in report.

|Asset Code | Extention|DP Amount upto Period |6 |7 |8 |
|t_anbr | t_aext | dpAmount | [Amount]|[Amount]|[Amount]|

Hope you get my requirement.

Regards,

VIkram

Hans_vd

Hi VIkram,

Filters can be used when for example you want to reuse a dataset in multiple tables.

About your crosstab formatting question, I don't know what you mean.
To put a label in the report, you can just select Label from the palette and drag it into the crosstab.

Regards
Hans

birtseeker

Hi Hans,

Please find attached Image file. In Image there are 8 fields without headers. I didn't find any way to add header to these fields. Also I need sum of Closing Balance and dpAmount field as Last Column as Grand Total. Is it possible to add these two field at the end?

Hope now you will get the requirement.

Regards,

VIkram

Hans_vd

Hi VIkram,

I don't think there is a way to automatically create a row to put in the labels. You can create a grid above the report items to put the labels in, but the sizing may be a awkward.

Regards
Hans